Notification

Icon
Error

Run a report against a specific static group only

Posted: Tuesday, May 14, 2019 11:45:06 PM(UTC)
marandtay

marandtay

Member Original PosterPosts: 3
0
Like
This issue has been solved! Click here to view the solution
Hi,

We have a report as below and a static group already set up "Critical Systems". What we need to do is only show results for that static group. Can you help? Apologies in advance but we have no SQL skills in-house d'oh! .

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
Count(tblSoftware.AssetID) 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
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
Order By Total Desc
endyk
#1endyk Member Posts: 24  
posted: 5/15/2019 11:21:38 AM(UTC)
Try this:

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
Count(tblSoftware.AssetID) 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
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
where tblAssetGroups.AssetGroup = 'Critical Systems'
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
Order By Total Desc
marandtay
#2marandtay Member Original PosterPosts: 3  
posted: 5/16/2019 8:45:04 AM(UTC)
Thank you very much Angel , I will give that a go and let you know.

All the best,

Mark
marandtay
#3marandtay Member Original PosterPosts: 3  
posted: 5/17/2019 6:54:27 AM(UTC)
Originally Posted by: endyk Go to Quoted Post
Try this:

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
Count(tblSoftware.AssetID) 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
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
where tblAssetGroups.AssetGroup = 'Critical Systems'
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
Order By Total Desc


SO that worked perfectly! You are a star and this has saved us so much time. Thank you so much for responding and your help on this.
endyk
#4endyk Member Posts: 24  
posted: 5/17/2019 9:06:00 AM(UTC)
Anytime. You are welcome!

Active Discussions

Lansweeper Windows 7 EOL
by  Esben.D   Go to last post Go to first unread
Last post: Today at 12:22:43 PM(UTC)
Lansweeper Change History or Restore of Custom Reports?
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 12:16:21 PM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  Esben.D   Go to last post Go to first unread
Last post: Today at 10:55:07 AM(UTC)
Action Change Windows domain PC Name
by  max204  
Go to last post Go to first unread
Last post: Today at 10:28:29 AM(UTC)
Lansweeper Lansweeper Reporting Old Assets as New
by  Esben.D   Go to last post Go to first unread
Last post: Today at 8:45:09 AM(UTC)
Lansweeper Thunderbird 68 Vulnerability
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 8:41:29 AM(UTC)
Lansweeper Assets disappearing
by  Leeroy_J   Go to last post Go to first unread
Last post: Today at 12:44:56 AM(UTC)
Lansweeper Old Windows 7 Assets
by  cycleheat  
Go to last post Go to first unread
Last post: Yesterday at 9:45:35 PM(UTC)