cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

3 Software Versions on 1 row

robert5551212
Engaged Sweeper
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
2 REPLIES 2

Andy_Sismey
Champion Sweeper III
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

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