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

Multiple software on one report

JTZRFB
Engaged Sweeper
I did a search, but just not pulling up what I'm looking for. I'm wanting to to have one report with multiple columns. One for Firefox and one for Chrome. Right now Assets show up on two lines. One will have Chrome listed and the other line Firefox.

Ideally the columns should be
Asset Name, Software Name Firefox, Firefox Version, Software Name Chrome, Chrome Version

If you can point to posts that has accomplished it would be greatly appreciated. Thanks


Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblSoftwareUni.softwareName Like '%Chrome%' Or
tblSoftwareUni.softwareName Like '%Firefox%') And tblAssetCustom.State = 1
Order By tblAssets.AssetID


1 REPLY 1

RCorbeil
Honored Sweeper II
Try this. It's based on the standard base query.
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
Chrome.OneOrAll AS ChromeWhoFor,
Chrome.softwareName AS ChromeSoftware,
Chrome.softwareVersion AS ChromeVersion,
Firefox.OneOrAll As FirefoxWhoFor,
Firefox.softwareName As FirefoxSoftware,
Firefox.softwareVersion As FirefoxVersion
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select
tblSoftware.AssetID,
Case When tblSoftware.CurrentUser = 1 Then 'User' Else 'Global' End As OneOrAll,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From
tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%Google Chrome%'
And tblSoftwareUni.softwareName Not Like '%Google Chrome Frame%') As Chrome On Chrome.AssetID = tblAssets.AssetID
Left Join (Select
tblSoftware.AssetID,
Case When tblSoftware.CurrentUser = 1 Then 'User' Else 'Global' End As OneOrAll,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From
tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
tblSoftwareUni.softwareName Like '%Firefox%'
And tblSoftwareUni.SoftwarePublisher Like '%Mozilla%') As Firefox On Firefox.AssetID = tblAssets.AssetID
Where
tblAssetCustom.State = 1