Using SCCM to get Local User Group Membership and Local User Password Expiration

Recently the server team asked me if SCCM could gather Local Group Membership and Local User Password Expiration.

After a bit of Googling, I found an article by Sherry Kissenger (isn’t she just the best!).  The article describes how to get Local Group Membership.  Using the knowledge that she provided, I was able to extend her script to gather Local User Password Expiration.

Here is Sherry’s original post.  You will want to follow all of her directions since I won’t be reposting them.  I can state that her instructions were very easy to follow and everything worked great!  http://myitforum.com/cs2/blogs/skissinger/archive/2010/04/25/report-on-all-members-of-all-local-groups.aspx

Here is the modified VBScript that I used to gather both Local Group Membership and Local User Password Expiration

on error resume next
'Steps
'enumerate from win32_group where localaccount=1
'Read in the members of each local group returned
'Add the returned information to a custom WMI namespace
'sms-def.mof to pull that back.
Set fso = CreateObject("Scripting.FileSystemObject") 
Set nwo = CreateObject("Wscript.Network") 
Set sho = CreateObject("Wscript.Shell") 
TempFolder = sho.ExpandEnvironmentStrings("%temp%")
strWindir = sho.ExpandEnvironmentStrings("%windir%")
strComputer = nwo.ComputerName
Dim wbemCimtypeSint16 
Dim wbemCimtypeSint32 
Dim wbemCimtypeReal32 
Dim wbemCimtypeReal64 
Dim wbemCimtypeString 
Dim wbemCimtypeBoolean 
Dim wbemCimtypeObject 
Dim wbemCimtypeSint8 
Dim wbemCimtypeUint8 
Dim wbemCimtypeUint16 
Dim wbemCimtypeUint32 
Dim wbemCimtypeSint64 
Dim wbemCimtypeUint64 
Dim wbemCimtypeDateTime 
Dim wbemCimtypeReference 
Dim wbemCimtypeChar16 

wbemCimtypeSint16 = 2 
wbemCimtypeSint32 = 3 
wbemCimtypeReal32 = 4 
wbemCimtypeReal64 = 5 
wbemCimtypeString = 8 
wbemCimtypeBoolean = 11 
wbemCimtypeObject = 13 
wbemCimtypeSint8 = 16 
wbemCimtypeUint8 = 17 
wbemCimtypeUint16 = 18 
wbemCimtypeUint32 = 19 
wbemCimtypeSint64 = 20 
wbemCimtypeUint64 = 21 
wbemCimtypeDateTime = 101 
wbemCimtypeReference = 102 
wbemCimtypeChar16 = 103 
' Remove classes 
Set oLocation = CreateObject("WbemScripting.SWbemLocator") 
'===================
'If this is a Domain Controller, bail!
'===================
Set oWMI = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\.\root\cimv2")
Set colComputer = oWMI.ExecQuery _
("Select DomainRole from Win32_ComputerSystem")
For Each oComputer in colComputer
 if (oComputer.DomainRole = 4 or oComputer.DomainRole = 5) then
   wscript.echo "DomainController, So I'm quitting!"
   'wscript.quit
 Else
'==================
'If it is NOT a domain controller, then continue gathering info 
'and stuff it into WMI for later easy retrieval
'==================

Set oServices = oLocation.ConnectServer(,"root\cimv2") 
set oNewObject = oServices.Get("CM_LocalGroupMembers") 
oNewObject.Delete_ 
'==================
'Get the local Group Names
'==================
Dim iGroups(300)
i=0
Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\.\root\cimv2")
Set colGroup = objWMIService.ExecQuery("select * from win32_group where localaccount=1")
for each obj in colGroup
  igroups(i)=obj.Name
  i=i+1
next
'===============
'Get all of the names within each group
dim strLocal(300)
k=0
Set oLocation = CreateObject("WbemScripting.SWbemLocator") 
Set oServices = oLocation.ConnectServer(, "root\cimv2" ) 

'group name, domain name, user or group
for j = 0 to i-1

squery = "select partcomponent from win32_groupuser where groupcomponent = ""\\\\" &_
 strComputer & "\\root\\cimv2:Win32_Group.Domain=\""" & strComputer &_
 "\"",Name=\""" &igroups(j) & "\""""" 

Set oInstances = oServices.ExecQuery(sQuery) 
 FOR EACH oObject in oInstances 
  strLocal(k)=igroups(j) & "!" & oObject.PartComponent

  k=k+1

 Next
next
'==================
'Drop that into a custom wmi Namespace
'==================


' Create data class structure 
Set oDataObject = oServices.Get 
oDataObject.Path_.Class = "CM_LocalGroupMembers" 
oDataObject.Properties_.add "Account" , wbemCimtypeString 
oDataObject.Properties_("Account").Qualifiers_.add "key" , True 
oDataObject.Properties_.add "Domain" , wbemCimtypeString
oDataObject.Properties_.add "Category" , wbemCimtypeString
oDataObject.Properties_.add "Type" , wbemCimtypeString
oDataObject.Properties_.add "Name" , wbemCimtypeString
oDataObject.Properties_("Name").Qualifiers_.add "key" , True
oDataObject.Put_ 

For m = 0 To k-1
Set oNewObject = oServices.Get("CM_LocalGroupMembers" ).SpawnInstance_ 
str0 = Split(strLocal(m), "!", -1, 1)
str1 = Split(str0(1), "," , -1, 1) 
str2 = Split(str1(0), "\" , -1, 1) 
str4 = Split(str2(4), Chr(34), -1, 1) 


' The Account name or Group Name is inside the quotes after the comma 
str3 = Split(str1(1), Chr(34), -1, 1) 
' if the wmi source name is the same as the domain name inside the quotes, it' s a local account 
' str2(2) is the wmi source name, str4(1) is the domain name inside the quotes. 
If lcase(str2(2)) = lcase(str4(1)) Then 
oNewObject.Type = "Local" 
Else 
oNewObject.Type = "Domain" 
End If
oNewObject.Domain = str4(1) 
oNewObject.Account = str3(1) 
oNewObject.Name = str0(0)
Select Case lcase(str4(0))
  case "cimv2:win32_useraccount.domain="
   oNewObject.Category = "UserAccount"
  Case "cimv2:win32_group.domain="
   oNewObject.Category = "Group"
  Case "cimv2:win32_systemaccount.domain="
   oNewObject.Category = "SystemAccount"
  case else
   oNewObject.Category = "unknown"
end select
oNewObject.Put_ 
Next
wscript.echo "ok"

 end if
Next

'==================
'Done with groups.
'onto Local Users

'==================
'Delete class if exists

Set oServices = oLocation.ConnectServer(,"root\cimv2") 
set oNewObject = oServices.Get("CM_LocalUsers") 
oNewObject.Delete_ 

'==================
'Get the local User Names
'==================

dim objNet, adsCompPath, compFilter, compObj, userObj
set objNet = createobject("WScript.NetWork")
adsCompPath = "WinNT://" & objNet.ComputerName
set objNet = nothing

compFilter = array("User")
set compObj = getobject(adsCompPath)

'==========================================================
 '== apply the filter to the returned object
 '==========================================================
 compObj.filter = compFilter

'==========================================================
 '== ignore any of the attribute not present errors
 '==========================================================
 on error resume next

'==================
'Drop that into a custom wmi Namespace
'==================
' Create data class structure 
Set oDataObject = oServices.Get 
oDataObject.Path_.Class = "CM_LocalUsers" 
oDataObject.Properties_.add "AccountDisabled" , wbemCimtypeBoolean 
oDataObject.Properties_.add "IsAccountLocked" , wbemCimtypeBoolean
oDataObject.Properties_.add "LastLogin" , wbemCimtypeString
oDataObject.Properties_.add "Name" , wbemCimtypeString
oDataObject.Properties_("Name").Qualifiers_.add "key" , True
oDataObject.Properties_.add "PasswordExpirationDate" , wbemCimtypeString
oDataObject.Properties_.add "PasswordRequired" , wbemCimtypeBoolean
oDataObject.Put_ 

for each userObj in compObj
Set oNewObject = oServices.Get("CM_LocalUsers" ).SpawnInstance_ 
	oNewObject.AccountDisabled = userObj.AccountDisabled
	oNewObject.IsAccountLocked = userObj.IsAccountLocked
	oNewObject.LastLogin = userObj.LastLogin
	oNewObject.Name = userObj.Name
	oNewObject.PasswordExpirationDate = userObj.PasswordExpirationDate
	oNewObject.PasswordRequired = userObj.PasswordRequired
    oNewObject.Put_ 
Next



'==========================================================
 '== dump the reference to the domain
 '==========================================================
 set compObj = nothing

wscript.quit

Here is my MOF edit as well:

//=====================Local Group Members, Includes Administrators 
//Pre-requisite: recurring Advertisement, or Recurring DCM Baseline/CI 
//==================================================================== 
#pragma deleteclass ("LocalGroupMembers",NOFAIL) 
[ SMS_Report     (TRUE), 
  SMS_Group_Name ("LocalGroupMembers"), 
  SMS_Class_ID   ("CUSTOM|LocalGroupMembers|1.0") ] 
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; 
}; 


//=====================Local Users
//Pre-requisite: recurring Advertisement, or Recurring DCM Baseline/CI 
//==================================================================== 
#pragma deleteclass ("LocalUsers",NOFAIL) 
[ SMS_Report     (TRUE), 
  SMS_Group_Name ("LocalUsers"), 
  SMS_Class_ID   ("CUSTOM|LocalUsers|1.0") ] 
class CM_LocalUsers : SMS_Class_Template 
{ 
    [SMS_Report (TRUE)      ] string AccountDisabled; 
    [SMS_Report (TRUE)      ] string IsAccountLocked; 
    [SMS_Report (TRUE)      ] string LastLogin; 
    [SMS_Report (TRUE), key ] string Name; 
    [SMS_Report (TRUE)      ] string PasswordExpirationDate; 
    [SMS_Report (TRUE)      ] string PasswordRequired;
}; 

Sherry posted a couple SQL statements for reports based on Local Group Membership.  Here are the ones I wrote for Local User Password Expiration:

declare @olddcm datetime 
declare @oldhinv datetime
set @oldDCM=DATEADD(DAY,-3, getdate())
set @oldHinv=DATEADD(DAY,-3, getdate())
select distinct sys1.netbios_name0
,lu.name0 [Name of the local User]
,lu.PasswordExpirationDate0 as [Password Expiration Date]
, case when lu.PasswordRequired0 = 1 then 'True'
	when lu.PasswordRequired0 = 0 then 'False' end as [Password Required]
, case when lu.IsAccountLocked0 = 1 then 'True'
	when lu.IsAccountLocked0 = 0 then 'False' end as [Account Locked]
, case when lu.AccountDisabled0 = 1 then 'True'
	when lu.AccountDisabled0 = 0 then 'False' end as [Account Disabled]
, lu.LastLogin0 as [Last Login]
, 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_LocalUsers0 lu
join v_gs_workstation_status ws on ws.resourceid=lu.resourceid
join v_r_system_valid sys1 on sys1.resourceid=LU.resourceid
left join v_CICurrentComplianceStatus cs on cs.resourceid=LU.resourceid
left join v_LocalizedCIProperties_SiteLoc loc on loc.ci_id=cs.ci_id
where loc.displayname = 'local users and group members into WMI'
and LU.name0 like @UserName and sys1.netbios_name0 like @MachineName
order by sys1.netbios_name0

The above code takes two prompts. 

@Machinename prompt

select distinct sys1.netbios_name0
from
v_gs_localusers0 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 users and group members into WMI'
order by sys1.netbios_name0

@UserName prompt

select distinct name0 from v_GS_LocalUsers0
order by name0