3 Software Versions on 1 row

[robert5551212]

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.

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_Sismey]

This should get you started, you just need to rename the Software Names, etc

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]

Thanks.....

Someone else posted this....

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