SQL SCCM Query: howto limit collection

Hi,

Got this SQL query on SCCM, how do I limit the query to a specific collection?

SELECT
v_R_System.Name0
as
'Computer Name',

v_GS_OPERATING_SYSTEM.Caption0
as
'Operating System',

v_GS_AntimalwareHealthStatus.LastQuickScanDateTimeStart,
v_GS_AntimalwareHealthStatus.LastQuickScanDateTimeEnd,
v_GS_AntimalwareHealthStatus.LastFullScanDateTimeStart,
v_GS_AntimalwareHealthStatus.LastFullScanDateTimeEnd,

v_GS_AntimalwareHealthStatus.LastQuickScanAge
as 'Days since last quick scan',

v_GS_AntimalwareHealthStatus.LastFullScanAge
as 'Days since last full scan',

v_GS_AntimalwareHealthStatus.EngineVersion
as 'EngineVersion'
     
FROM

v_GS_AntimalwareHealthStatus
INNER JOIN

v_R_System ON v_GS_AntimalwareHealthStatus.ResourceID =
v_R_System.ResourceID INNER JOIN
v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID

J.
janhoedtAsked:
Who is Participating?
 
NConnect With a Mentor Enterprise Systems EngineerCommented:
In that case, try the following:

SELECT
v_R_System.Name0
as
'Computer Name',

v_GS_OPERATING_SYSTEM.Caption0
as
'Operating System',

v_GS_AntimalwareHealthStatus.LastQuickScanDateTimeStart,
v_GS_AntimalwareHealthStatus.LastQuickScanDateTimeEnd,
v_GS_AntimalwareHealthStatus.LastFullScanDateTimeStart,
v_GS_AntimalwareHealthStatus.LastFullScanDateTimeEnd,

v_GS_AntimalwareHealthStatus.LastQuickScanAge
as 'Days since last quick scan',

v_GS_AntimalwareHealthStatus.LastFullScanAge
as 'Days since last full scan',

v_GS_AntimalwareHealthStatus.EngineVersion
as 'EngineVersion'
     
FROM

v_GS_AntimalwareHealthStatus
INNER JOIN

v_R_System ON v_GS_AntimalwareHealthStatus.ResourceID =
v_R_System.ResourceID INNER JOIN
v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID JOIN v_fullCollectionMembership on v_R_System.REsourceID = V_FullCollectionMembership.ResourceID

WHERE

v_fullcollectionmembership.collectionID = '123456'
0
 
Pawan KumarDatabase ExpertCommented:
Which database are you using ? How many rows do you want to limit?
0
 
NEnterprise Systems EngineerCommented:
Hello.

If you join v_FullCollectionMembership on v_r_system,id = v_fullcollectionmembership.resourceID

you could add

'where v_fullcollectionmembership.CollectionID=@CollID'

That should prompt you to input a collection ID to run the query on.
1
 
janhoedtAuthor Commented:
Thanks! However, I do not want to prompt for a collection ID, I would like to set it.
F.e. collectionID = '123456'

I'm pretty new to queries, how/where do I add the values you mention to my query?
0
 
janhoedtAuthor Commented:
Great, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.