Notification

Icon
Error

3 Software Versions on 1 row

Posted: Wednesday, May 4, 2022 1:14:22 AM(UTC)
Robert5551212

Robert5551212

Member Original PosterPosts: 6
0
Like
Hello,

I would like a report that lists a few software version numbers on one row. I did this at my last job... but I lost my notes. Brick wall

I will put the software name as the Column header... So the report would simply look like this.... on a single row..

Expected Output
Computer Name | Chrome Version | CarbonBlack Version | CrowdStrike Version

I remember the syntax would allow me to filter out items...
(Name like %Chrome%, but not Like %Chrome Frame%)

Also the report only listed machines joined to my domain... Any help would be greatly appreciated.

I tried this thread but it did not work...

Thanks
Andy.S
#1Andy.S Member Posts: 160  
posted: 5/5/2022 11:35:17 AM(UTC)
This should get you started, you just need to rename the Software Names, etc

Code:
Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblassets.Lasttried,
  Case
    When Firefox.AssetID Is Not Null Then Firefox.softwareVersion
    Else ''
  End As 'Firefox Version',
  Case
    When Chrome.AssetID Is Not Null Then Chrome.softwareVersion
    Else ''
  End As 'Chrome Version',
  Case
    When CarbonBlack.AssetID Is Not Null Then CarbonBlack.softwareVersion
    Else ''
  End As 'CarbonBlack Version'
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Left Join (Select tblSoftware.softwareVersion,
      tblSoftwareUni.softwareName,
      tblSoftware.AssetID
    From lansweeperdb.dbo.tblSoftware
      Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Chrome%') Chrome On
      Chrome.AssetID = tblassets.AssetID
  Left Join (Select tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion,
      tblSoftware.AssetID
    From lansweeperdb.dbo.tblSoftware
      Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Firefox%') Firefox On
      Firefox.AssetID = tblassets.AssetID
  Left Join (Select tblSoftware.AssetID,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion
    From lansweeperdb.dbo.tblSoftware
      Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%CarbonBlack Version%') CarbonBlack
    On CarbonBlack.AssetID = tblassets.AssetID
Where tsysassettypes.AssetTypename Like 'Windows' And tblassetcustom.State = 1
Robert5551212
#2Robert5551212 Member Original PosterPosts: 6  
posted: 5/11/2022 11:39:51 PM(UTC)
Thanks.....

Someone else posted this....

Quote:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.IPAddress,
tsysOS.Image As icon,
tblComputersystem1.Model,
tblAssets.NrProcessors,
tblAssets.Processor,
tblAssets.Memory,
SOFTWARE1.SoftwareName As SOFTWARE1Software,
SOFTWARE1.SoftwareVersion As SOFTWARE1Version,
SOFTWARE2.SoftwareName As SOFTWARE2Software,
SOFTWARE2.SoftwareVersion As SOFTWARE2Version,
SOFTWARE3.SoftwareName As SOFTWARE3Software,
SOFTWARE3.SoftwareVersion As SOFTWARE3Version,
SOFTWARE4.SoftwareName As SOFTWARE4Software,
SOFTWARE4.SoftwareVersion As SOFTWARE4Version,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblComputersystem tblComputersystem1 On tblAssets.AssetID =
tblComputersystem1.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SOFTWARE1 Description%') As
SOFTWARE1 On SOFTWARE1.AssetID = tblAssets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SOFTWARE2 Description%') As
SOFTWARE2 On SOFTWARE2.AssetID = tblAssets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SOFTWARE3 Description%') As
SOFTWARE3 On SOFTWARE3.AssetID = tblAssets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%SOFTWARE4 Description') As
SOFTWARE4 On SOFTWARE4.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.IPAddress,
tsysOS.Image,
tblComputersystem1.Model,
tblAssets.NrProcessors,
tblAssets.Processor,
tblAssets.Memory,
tblAssets.Lastseen,
SOFTWARE1.SoftwareName,
SOFTWARE1.SoftwareVersion,
SOFTWARE2.SoftwareName,
SOFTWARE2.SoftwareVersion,
SOFTWARE3.SoftwareName,
SOFTWARE3.SoftwareVersion,
SOFTWARE4.SoftwareName,
SOFTWARE4.SoftwareVersion

Active Discussions

Lansweeper Version 10.2.0.0
by  _Shawn_   Go to last post Go to first unread
Last post: 7/1/2022 10:33:33 PM(UTC)
Lansweeper Certificates
by  Orion Poplawski  
Go to last post Go to first unread
Last post: 7/1/2022 10:11:12 PM(UTC)
Lansweeper Dell warranty lookup not working
by  LANGuy  
Go to last post Go to first unread
Last post: 7/1/2022 1:30:06 PM(UTC)
Lansweeper RedHat 8.5 & SELinux
by  QuelleAcht   Go to last post Go to first unread
Last post: 7/1/2022 1:16:19 PM(UTC)
Lansweeper Suddenly seeing Access Denied scanning errors?
by  Erik.T  
Go to last post Go to first unread
Last post: 7/1/2022 9:38:17 AM(UTC)
Lansweeper Single Line report with H/D
by  Ioannis   Go to last post Go to first unread
Last post: 7/1/2022 7:53:23 AM(UTC)
Lansweeper Lansweeper Dark Theme
by  mrobbins  
Go to last post Go to first unread
Last post: 6/30/2022 5:38:01 PM(UTC)