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 10 OS version not updating in Lansweeper
by  ewhite   Go to last post Go to first unread
Last post: Today at 5:50:15 PM(UTC)
Lansweeper Lansweeper Not Saving New Asset
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 3:54:17 PM(UTC)
Lansweeper Office365: Sharepoint library permissions
by  n.klug   Go to last post Go to first unread
Last post: Today at 8:47:24 AM(UTC)
Lansweeper Update relation: Owned by from Active Directory
by  davdyv  
Go to last post Go to first unread
Last post: Today at 8:03:06 AM(UTC)
Lansweeper Uptime incorrect
by  CyberCitizen   Go to last post Go to first unread
Last post: Today at 1:29:08 AM(UTC)
Lansweeper Email configuration
by  Ian.Prentice   Go to last post Go to first unread
Last post: 7/19/2019 3:14:04 PM(UTC)
Lansweeper InTune Scanning not showing all devices
by  TBRIT  
Go to last post Go to first unread
Last post: 7/19/2019 3:12:03 PM(UTC)