‎01-06-2016 09:18 AM
Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Count(tblSoftware.SoftwareID) As Total
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
Where tblAssetCustom.State = 1 And tblSoftwareUni.softwareName Not Like '%365%'
And tblSoftwareUni.softwareName Like 'Microsoft Office%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%runtime%' And
tblSoftwareUni.softwareName Not Like '%visio%' And
tblSoftwareUni.softwareName Not Like '%proofing%' And
tblSoftwareUni.softwareName Not Like '%analyzer%' And
tblSoftwareUni.softwareName Not Like '%resource%'
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
Order By Total Desc,
Version Desc
Solved! Go to Solution.
‎01-07-2016 04:51 PM
Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Count(tblSoftware.SoftwareID) As Total,
tblAssets.Domain
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
Where tblAssetCustom.State = 1 And tblSoftwareUni.softwareName Not Like '%365%'
And tblSoftwareUni.softwareName Like 'Microsoft Office%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%runtime%' And
tblSoftwareUni.softwareName Not Like '%visio%' And
tblSoftwareUni.softwareName Not Like '%proofing%' And
tblSoftwareUni.softwareName Not Like '%analyzer%' And
tblSoftwareUni.softwareName Not Like '%resource%' And
tblAssets.Domain Like '%domainName %'
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Domain
Order By Total Desc,
Version Desc
‎01-07-2016 04:51 PM
Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Count(tblSoftware.SoftwareID) As Total,
tblAssets.Domain
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
Where tblAssetCustom.State = 1 And tblSoftwareUni.softwareName Not Like '%365%'
And tblSoftwareUni.softwareName Like 'Microsoft Office%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%runtime%' And
tblSoftwareUni.softwareName Not Like '%visio%' And
tblSoftwareUni.softwareName Not Like '%proofing%' And
tblSoftwareUni.softwareName Not Like '%analyzer%' And
tblSoftwareUni.softwareName Not Like '%resource%' And
tblAssets.Domain Like '%domainName %'
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Domain
Order By Total Desc,
Version Desc
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now