Friday, May 23, 2014

Server in which SCCM Collection?

Most servers in SCCM would be a member in a couple collections and it would be pretty challenging to track which collections it is a member of.
The following SQL query will enable you just to do that 

select v_FullCollectionMembership.CollectionID, v_Collection.Name, v_R_System.Name0 from v_FullCollectionMembership 
JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID 
JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID 
Where v_R_System.Name0='Servername'

** Replace Servername with the servername you are checking against.

Wednesday, May 21, 2014

Cleaning up expired updates in SCCM

In SCCM, it is essential to house keep the update lists to remove expired updates in order to ensure that the compliance is reported correctly.

Of course you can manually go through all the update lists and deployment packages that you have created to remove those but why not use some tools to help you achieve that instead?

The below post is a VBS written by someone in the community which is very useful for this.

I have made minor amendments to the vbs which you can download from here
To use simply download the file and place it in a folder on the SCCM Server.
Open up command prompt and run the command below in the folder where you have placed the vbs

cscript ExpireClear.vbs [-d] -l|-r:<remoteserver> -u:<Username> -p:<password>

Switches defnitions
-d enable debug logging
-l connect to local machine
-r connect to remote machine

If -r is used, the user's credentials has to be provided.

When the vbs completes, you will be prompted to run a full synchronization whihc can be achieve using the below

1) In the Configuration Manager console at the highest site in the hierarchy with an active software update point, navigate to System Center Configuration Manager / Site Database / Computer Management / Software Updates / Update Repository.

2) Right-click Update Repository, and then click Run Synchronization. Software updates synchronization is initiated as soon as possible.
When synchronization is complete, a request is sent to child sites to initiate synchronization.

List Alerts for Specific SCOM Group

There was a query in the Microsoft System Center Community Forum requesting for assistance for a SQL query to list all alerts for a particular SCOM Group within a specific time frame.
I have create the query below and hopefully this will help anyone that needs something similar.
All is needed is to replace those in bold and underlined based on your needs.

select AlertName
,RGV.targetMonitoringObjectDisplayName [Server Name]
,RGV.SourceMonitoringObjectDisplayName [SCOM Group Name]
,AlertDescription [Alert]
,ars.DWCreatedDateTime [Alert Date]
,alt.Priority [Priority]
,alt.Severity [Severity]
      ,day(ars.DWCreatedDateTime) [triggerdate]
    ,month(ars.DWCreatedDateTime) [triggermonth]
      ,year(ars.DWCreatedDateTime) [triggeryear]
       from Alert.vAlertResolutionState ars
inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
left join dbo.vManagedEntity ME on ME.ManagedEntityRowId = alt.ManagedEntityRowId
left join OperationsManager.dbo.RelationshipGenericView RGV on RGV.TargetMonitoringObjectDisplayName = ME.DisplayName
where ars.DWCreatedDateTime between 'start date (mm/dd/yy)' and  'end date (mm/dd/yy)'
and ME.DisplayName in (select TargetMonitoringObjectDisplayName
from OperationsManager.dbo.RelationshipGenericView
where isDeleted=0
AND SourceMonitoringObjectDisplayName = 'Group Name' )

Thursday, May 15, 2014

Retrieving Local Administrators on Machines by SCCM

IT in some Organizations may grant users with local admin rights
As time goes by when there is a time when we need to know who has local admin rights on their machines , this would be a potential challenge if the process is not defined clearly.

SCCM can be utilized to get this information using the steps below

1) The DCM Configuration Item.
- Create a new, General Configuration Item and name it whateveryou wish, but you'll need to use that name later in a report.

For this case, the name is "Local Group Members into WMI", click Next
- There are no Objects, click Next
- Under Settings, Select New, Script.
- Display Name doesn't matter, I'll call it "WMIFramework For LocalGroupMembers"
- Description can be anything, sych as "Custom WMI Namespace of root\cimv2\cm_localgroupmembers"
- copy and paste the vbs as below

on error resume next
'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
'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") 
'Get the local Group Names
Dim iGroups(300)
Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\.\root\cimv2")
Set colGroup = objWMIService.ExecQuery("select * from win32_group where localaccount=1")
for each obj in colGroup
'Get all of the names within each group
dim strLocal(300)
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


'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

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" 
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

 end if


- Click on the Validation Tab, and change Severity to "Information - no Windows Event Message". Retain the check box for "report a non-compliance event when this instance count fails, of Greater than 0. Click Ok
- In Applicability Tab select "All Windows Platforms."

2) Create a DCM Baseline to target to a collection (If you are gathering information for all clients + servers, you can consider targeting the "All Systems" Collection.). Add the CI to the "These applications and general configuration items are required and must be properly configured"
When you assign, you are required to select a schedule. I recommend a Simple Schedule, and to make it be the same as your existing Simple Hardware Inventory schedule, i.e., if you have Hinv every 3 days, make this every 3 days.

3) Add the mof snippet to the bottom of sms_def.mof on your primarty sites' inboxes\clifiles.src\hinv

//=====================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;

A sample report if you would like to get the list of machines with users' being granted administrators rights is as below.
The query below is working on the assumption that the machines are not "hot seats".

select CS.Name0 [Machinename], account0 [Account Name],lgm.name0 [Groupname] ,lgm.type0 [Accounttype] from v_gs_localgroupmembers0 lgm
left join v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = lgm.resourceid
where CS.UserName0 like '%'+lgm.account0 and lgm.name0 = 'Administrators'

Friday, May 9, 2014

OpsMgr 2012 Scheduled Maintenance Mode tool

This is a very neat new tool, that enabled you to create schedules for agents and groups to go into maintenance mode.

It is a web service, with a VERY simple user interface, to allow users to create schedules for maintenance mode, or for on-demand maintenance mode.
It even includes a nice tool/icon to place on all your servers desktops, to allow server admins to immediately place a server into maintenance mode from the server itself

All System Center Cmdlets you are looking for

Microsoft has released System Center CmdLets reference for all the system center components.
It will definitely be a great help to any System Center Admins.

The reference can be downloaded from the below