Walid Hegazy

How To Do :) IT

SCCM2012 R2 collect local group users custom Report

2 Comments

last week I had case with one of the customers , he wanted to generate Report in all users in all local groups using Configuration Manager 2012 then I came across very helpful post by Sherry Kissinger , so here I am just explaining the sherry’s steps I followed.

The SCCM doesn’t collect Local groups members by itself, so in order to do that download the attached WMI Framework for Local Groups with Logging  .cab file

on Assets and Compliance, Compliance Settings, right-click on “Configuration Baseline” and Import Configuration Data… the .cab file.

image

After Importing the baseline , deploy it to the collection you want to collect local Group members from “all workstations” for example.

Now, you’ll need to create the MOF file to extend the ConfigMgr inventory classes.

Save the below as “localgroupmembers.mof

#pragma deleteclass (“LocalGroupMembers”,NOFAIL)
[ SMS_Report     (TRUE),
SMS_Group_Name (“LocalGroupMembers”),
SMS_Class_ID   (“LocalGroupMembers”) ]
class cm_LocalGroupMembers : SMS_Class_Template
{
[SMS_Report (TRUE), key ] string Account;
[SMS_Report (TRUE)      ] string Category;
[SMS_Report (TRUE)      ] string Domain;
[SMS_Report (TRUE), key ] string Name;
[SMS_Report (TRUE)      ] string Type;
};

Then from the console go to administration / Client settings /hardware inventory Click on Set Classes then click import.

image

Browse to ‘localgroupmembers.mof’ file You created,ensure you see the success on the import like this:

5

after importing is done  you can jus search to make sure classes are imported successfully.

6

Now just wait for it to work , for me it took around 3+ hours to for any thing to appear when I was checking the “v_gs_localgroupmembers0” View in the SCCCM2012 Database

image

If you now check the hardware inventory  you can see the users there Open-mouthed smile

But how to create the  report !! .

image

now go to the monitoring workspace ,  right click reports select create new report /fill the report name the SQL Report builder will open /select to create new data set / click edit as text and paste the following query

declare @olddcm datetime
declare @oldhinv datetime
set @oldDCM=DATEADD(DAY,-3, getdate())
set @oldHinv=DATEADD(DAY,-3, getdate())
select sys1.netbios_name0
,lgm.name0 [Name of the local Group]
,lgm.account0 as [Account Contained within the Group]
, lgm.category0 [Account Type]
, lgm.domain0 [Domain for Account]
, lgm.type0 [Type of Account]
, case when ws.lasthwscan < @oldhinv then ‘Last Hinv might be out of date’
when cs.lastcompliancemessagetime < @olddcm then ‘CI evaluation might be out of date’
when ws.lasthwscan < cs.lastcompliancemessagetime then ‘CI evaluated since hinv, not necessarily unreliable’
else ‘Recent CI Eval, Hinv since CI Eval = Fairly Reliable’
end as [Reliability of Information]
from
v_gs_localgroupmembers0 lgm
join v_gs_workstation_status ws on ws.resourceid=lgm.resourceid
join v_r_system_valid sys1 on sys1.resourceid=lgm.resourceid
left join v_CICurrentComplianceStatus cs on cs.resourceid=lgm.resourceid
left join v_LocalizedCIProperties_SiteLoc loc on loc.ci_id=cs.ci_id
where loc.displayname = ‘local group members into WMI With logging’
order by sys1.netbios_name0, lgm.name0, lgm.account0

Then continue the wizard and save the report.

This is the report row Grouped by name of local group

image

image

Now we have our report Nerd smile

Advertisements

2 thoughts on “SCCM2012 R2 collect local group users custom Report

  1. Fantastic post, this was a big help. Thanks.
    One request: Can you post the .rdl for the report? I have not problem with the SQL in Management Studio. But I am having quite a hard time in Report Builder.
    Thanks again…

  2. Thanks!!!
    It’s better than extending the class of inventory.

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