Satyen, On the 2nd to the last line from the bottom is the beginning of the where clause. Change that line to the below.
where sys1.netbios_name0 = 'Administrators' and loc.displayname = 'Local Group Members into WMI with Logging' Thanks, ________________________________ Mark Mears [email protected]<mailto:[email protected]%0d> Phone: (757) 945-2651 [cid:[email protected]]<http://www.cireson.com/> [cid:[email protected]]<http://twitter.com/teamcireson> Check out our System Center App Store: www.cireson.com/app-store ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Hotmail_Satyen Sent: Thursday, September 26, 2013 3:09 AM To: [email protected] Subject: RE: [mssms] SCCM 2012 - Query Local Admin Users for win 7 In this blog "http://myitforum.com/cs2/blogs/skissinger/archive/2010/04/25/report-on-all-members-of-all-local-groups.aspx"; Sherry has mentioned Just want the members of the local Administrators group? put in a where name0 = 'Administrators' Where exactly in the below query will where name0 = 'Administrators' fit in? 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 From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Hotmail_Satyen Sent: Thursday, September 26, 2013 9:25 AM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] SCCM 2012 - Query Local Admin Users for win 7 Thanks a lot for your report Sherry. It works perfectly fine. We have SCCM 2012 in our environment. I am not good at SQL. It would be great help if you can help edit / tweak the SQL queries of the reports to generate the below results. * List the Administrator Group only. (The current report lists all the groups of a computer and its members) * Option to choose collection. * Can we have options to select Domain (accounts) only * Can we have options to select user accounts only (currently it provides information for user accounts as well as user groups) * In the individual computer report for Admins, can we include all local groups, power users, etc. Regards, Satyen From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Linkey, Mike Sent: Tuesday, December 04, 2012 9:16 PM To: [email protected]<mailto:[email protected]> Subject: RE: [mssms] SCCM 2012 - Query Local Admin Users for win 7 I have Sherry's method setup and working great with the baseline and CI. I was even able to customize my reports more to exclude users that are approved for aDmin access. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Pat Johnson Sent: Monday, December 03, 2012 4:08 PM To: [email protected]<mailto:[email protected]> Subject: Re: [mssms] SCCM 2012 - Query Local Admin Users for win 7 Sherry your amazing i ran the vbscript by itself on the test box and it works. so i need to find out that the CI and baseline and not doing. i just might make a re-occurring basis. On Mon, Dec 3, 2012 at 12:01 PM, Sherry Kissinger <[email protected]<mailto:[email protected]>> wrote: if you go to wbemtest, connect, root\cimv2, connect. click enum classes... click recursive in there, OK. wait a bit... then scroll and look for cm_localgroupmembers If you don't see it on that client, then there is "something" not working right with that baseline and ci. Go through your CI... what exactly are the settings on the CI--pane by pane. What exactly is in the script section? If you are on that client, control panel, on the compliance tab you can run a local report. What's in there? The summary of "compliant" could mean that the baseline resolved as "not applicable"--i.e., it didn't bother to run the CI because based on the CI logic a windows 7 x86 didn't deserve to run that CI. ________________________________ From: Pat Johnson <[email protected]<mailto:[email protected]>> To: [email protected]<mailto:[email protected]> Sent: Mon, December 3, 2012 1:48:27 PM Subject: Re: [mssms] SCCM 2012 - Query Local Admin Users for win 7 Sherry you have been so helpful, and I can't thank you enough. The text box I have is just a workstation with windows 7 x86 and it's on the domain with local users. The CI is set to script, and a part of its own baseline that is deployed to the collection that my test box is in. The client on the test box says the is it compliant with the baseline. The local logs still have the error Collection: Class "cm_LocalGroupMembers" does not exist out". I appreciate all your help. Thanks again. On Fri, Nov 30, 2012 at 5:10 PM, Sherry Kissinger <[email protected]<mailto:[email protected]>> wrote: Oh, one other thing... your test box isn't a domain controller is it? There simply aren't "local groups" on a domain controller. If your test box is a DC... there's your problem. Pick on a member server or a workstation. Sherry Kissinger Microsoft MVP - ConfigMgr [email protected]<mailto:[email protected]> ________________________________ From: Pat Johnson <[email protected]<mailto:[email protected]>> To: [email protected]<mailto:[email protected]> Sent: Fri, November 30, 2012 6:14:08 PM Subject: Re: [mssms] SCCM 2012 - Query Local Admin Users for win 7 I used the DCM Baseline and configuration item. On Fri, Nov 30, 2012 at 3:56 PM, Sherry Kissinger <[email protected]<mailto:[email protected]>> wrote: DCM Baseline and configuration item
<<inline: image001.png>>
<<inline: image002.jpg>>
