cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
servicedesk
Champion Sweeper II
Hello !

I created a new "Static asset group" with about 40 machines but I don't know how to use it...

Firstly I would like to know if they have a specific software "Microsoft Silverlight" and version.

This SQL report works but is not based on my new group...

Select Top 1000000 tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon,
tblADComputers.OU
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
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblSoftwareUni.softwareName Like '%Microsoft Silverlight%' And
tblAssetCustom.State = 1
Order By tblAssets.AssetName,
software,
version

Thanks
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
You need to include both tblAssetGroupLink and tblAssetGroups, like in this example:

Select Top 1000000 tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon,
tblADComputers.OU,
tblAssetGroups.AssetGroup
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
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblSoftwareUni.softwareName Like '%Microsoft Silverlight%' And
tblAssetGroups.AssetGroup Like 'YourAssetGroup%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
software,
version

View solution in original post

2 REPLIES 2
servicedesk
Champion Sweeper II
Brilliant, thank you
Daniel_B
Lansweeper Alumni
You need to include both tblAssetGroupLink and tblAssetGroups, like in this example:

Select Top 1000000 tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon,
tblADComputers.OU,
tblAssetGroups.AssetGroup
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
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblSoftwareUni.softwareName Like '%Microsoft Silverlight%' And
tblAssetGroups.AssetGroup Like 'YourAssetGroup%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
software,
version