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 How do you scan your assets on your network?
by  CyberCitizen   Go to last post Go to first unread
Last post: Today at 1:14:18 AM(UTC)
Lansweeper How EXACTLY Does LS Count Assets?
by  vqT4cDoP9iXyMZwoDUWU  
Go to last post Go to first unread
Last post: Yesterday at 10:14:48 PM(UTC)
Lansweeper URL for Custom Reports
by  Joe Conger   Go to last post Go to first unread
Last post: Yesterday at 6:52:06 PM(UTC)
Lansweeper Add Document as File Link
by  mowo  
Go to last post Go to first unread
Last post: Yesterday at 2:00:25 PM(UTC)
Lansweeper Table View Filter
by  tgt   Go to last post Go to first unread
Last post: Yesterday at 8:30:44 AM(UTC)
Lansweeper How to scan assets on a different network
by  twpseaton  
Go to last post Go to first unread
Last post: 6/25/2019 10:04:36 PM(UTC)
Lansweeper Lansweeper warnings sent to an email
by  MilicaM   Go to last post Go to first unread
Last post: 6/25/2019 2:23:29 PM(UTC)
Lansweeper ShellRunAs action in Lansweeper
by  DavidL  
Go to last post Go to first unread
Last post: 6/25/2019 11:58:07 AM(UTC)