SCCM queries made easy – query to get all complaint computers

Hi Readers,

Not getting enough time to blog as I am daily geeting new challenges now a days & I am loving it.

Although SCCM is not my domain but I came across a method that I have used for building SCCM queries easily.

I have used easy query builder http://easyquerybuilder.com/ (Thanks to its creator , its free)

Just connect it to SQL & see the magic.

You can just drag & drop to make queries (I have made below query that tell me what patch I am missing from my servers)

All my servers are in Messaging OU in Active Directory.

———————————————————————————————————————————————————–

SELECT v_R_System.Netbios_Name0 AS “v_R_System Netbios_Name0”, v_GS_WORKSTATION_STATUS.LastHWScan AS “v_GS_WORKSTATION_STATUS LastHWScan”, v_UpdateInfo.UpdateType AS “v_UpdateInfo UpdateType”, v_UpdateInfo.ArticleID AS “v_UpdateInfo ArticleID”, v_UpdateInfo.BulletinID AS “v_UpdateInfo BulletinID”, v_UpdateComplianceStatus.Status AS “v_UpdateComplianceStatus Status”

FROM ((((v_R_System AS v_R_System

     INNER JOIN v_GS_WORKSTATION_STATUS AS v_GS_WORKSTATION_STATUS ON (v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID ))

     INNER JOIN v_UpdateComplianceStatus AS v_UpdateComplianceStatus ON (v_UpdateComplianceStatus.ResourceID = v_R_System.ResourceID ))

     INNER JOIN v_UpdateInfo AS v_UpdateInfo ON (v_UpdateInfo.CI_ID = v_UpdateComplianceStatus.CI_ID ))

     INNER JOIN v_RA_System_SystemOUName AS v_RA_System_SystemOUName ON (v_R_System.ResourceID = v_RA_System_SystemOUName.ResourceID ))

WHERE

(

  v_RA_System_SystemOUName.System_OU_Name0 LIKE ‘%Messaging%’ )

 

—————————————————————————————————————————————————————–

Below are the meaning of values for the results you obtain.

0 = Detection state unknown

1 = Update is not required

2 = Update is required

3 = Update is installed

Hope this will help some of us…like it helped me…

Regards

Sukhija Vikas

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s