SCCM (ConfigMrg) - WannaCry Ransomware Compliance

WannaCry Ransomware made some damages worldwide, and still lots of doubts about how to check if your infrastructure is safe.
This is been my days lately.

Check if my whole servers are patched, giving management teams compliance reports, and so on.
Lots of online examples, this, yes, is just another one.

Disclaimer: Modify the T-SQL query and VBScript for the specific HotFixID's - in my case are for 2003-2016 windows servers.

So, SCCM query to check out the servers missing the Ransomware fix :

SELECT dbo.v_R_System.Name0 AS 'Computername', v_R_System.Full_Domain_Name0, dbo.v_UpdateInfo.Title AS 'Updatename', dbo.v_StateNames.StateName, dbo.v_Update_ComplianceStatusAll.LastStatusCheckTime, dbo.v_UpdateInfo.DateLastModified, dbo.v_UpdateInfo.IsDeployed, dbo.v_UpdateInfo.IsSuperseded,   
      dbo.v_UpdateInfo.IsExpired, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.DateRevised,   
      catinfo.CategoryInstanceName as 'Vendor',   
      catinfo2.CategoryInstanceName as 'UpdateClassification'   
      FROM dbo.v_StateNames   
      INNER JOIN dbo.v_Update_ComplianceStatusAll   
      INNER JOIN dbo.v_R_System ON dbo.v_R_System.ResourceID = dbo.v_Update_ComplianceStatusAll.ResourceID   
      INNER JOIN dbo.v_UpdateInfo ON dbo.v_UpdateInfo.CI_ID = dbo.v_Update_ComplianceStatusAll.CI_ID ON dbo.v_StateNames.StateID = dbo.v_Update_ComplianceStatusAll.Status   
      INNER JOIN v_CICategories_All catall on catall.CI_ID = dbo.v_UpdateInfo.CI_ID   
      INNER JOIN v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company'   
      INNER JOIN v_CICategories_All catall2 on catall2.CI_ID=dbo.v_UpdateInfo.CI_ID   
      INNER JOIN v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'   
      INNER JOIN v_CH_ClientSummary on v_CH_ClientSummary.ResourceID = v_R_System.ResourceID  
      WHERE (dbo.v_StateNames.TopicType = 500)   
      AND (dbo.v_StateNames.StateName = 'Update is required')   
      AND (dbo.v_R_System.Name0 IN (  
                                     SELECT TOP (100) PERCENT SD.Name0 AS 'Machine Name'   
                                     FROM dbo.v_R_System AS SD INNER JOIN   
                                     dbo.v_FullCollectionMembership AS FCM ON SD.ResourceID = FCM.ResourceID INNER JOIN   
                                     dbo.v_Collection AS COL ON FCM.CollectionID = COL.CollectionID LEFT OUTER JOIN   
                                     dbo.v_R_User AS USR ON SD.User_Name0 = USR.User_Name0 INNER JOIN   
                                     dbo.v_GS_PC_BIOS AS PCB ON SD.ResourceID = PCB.ResourceID INNER JOIN   
                                     dbo.v_GS_COMPUTER_SYSTEM AS CS ON SD.ResourceID = CS.ResourceID INNER JOIN   
                                     dbo.v_RA_System_SMSAssignedSites AS SAS ON SD.ResourceID = SAS.ResourceID   
                                     WHERE (COL.Name like 'All Windows Servers')  
                                   )  
          )   
      AND ((catinfo2.CategoryInstanceName like 'Critical%' ) OR (catinfo2.CategoryInstanceName like 'Security%' ))   
      AND dbo.v_UpdateInfo.ArticleID in ('4012214','4012212','4012213','4012598')  
      AND v_CH_ClientSummary.ClientActiveStatus = 1  
      ORDER BY dbo.v_R_System.Name0  

And, a SCCM configuration item VBScript (some servers don't have powershell...! yes, there're a few ...!) :

 strComputer = "."  
 Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")  
 Set colItems = objWMIService.ExecQuery("Select * from Win32_QuickFixEngineering",,48)  
 Set objFSO=CreateObject("Scripting.FileSystemObject")  
 Set wshNetwork = WScript.CreateObject( "WScript.Network" )  
 strComputerName = wshNetwork.ComputerName  
 For Each objItem in colItems   
 If objItem.HotfixID = "KB4012214" or objItem.HotfixID = "KB4012213" or objItem.HotfixID = "KB4012212" or objItem.HotfixID = "KB4012598" then  
 wscript.echo "Compliant"  
 End If  
 Next  

So, now just make a configuration baseline or add this configuration item to your existing configuration baseline.

Hope this helps you guys out.

OpsMgr (SCOM) - NTP Deviation Monitor (VBScript)

OpsMgr by default doesn't evaluate the deviation between the NTP server time and the server/workstation time, and since there are some critical services that require this deviation to be almost 0, i've decided to create a three-state monitor for this evaluation.

First, create a "Timed Script Three State Monitor" on the Authoring pane (Create a Monitor) - select "Unit Monitor"
Associate it to a brand new MP.



Then, target it to Windows Computer. I've decided to select "Configuration" to it's parent monitor (this is up to you!).
Also decided to NOT ENABLE IT by default.



Next, select the most effective schedule for you, i've decided to leave it to 5 minutes.



Fill the file name also as the script area, with this script :
(As you might notice, part of this code is from Nagios check check_time.vbs - credits go to the Author : Dmitry Vayntrub (dvayntrub@yahoo.com) )

 Dim oAPI, oBag  
 Set oAPI = CreateObject("MOM.ScriptAPI")  
 Set oBag = oAPI.CreatePropertyBag()  
 Set objWMISvc = GetObject( "winmgmts:\\.\root\cimv2" )  
 Set colItems = objWMISvc.ExecQuery( "Select * from Win32_ComputerSystem" )  
 For Each objItem in colItems  
   strComputerDomain = objItem.Domain  
 Next  
 Set objShell = CreateObject("Wscript.Shell")  
 strCommand = "%SystemRoot%\System32\w32tm.exe /monitor /nowarn /computers:" & strComputerDomain  
 set objProc = objShell.Exec(strCommand)  
 warn = "5"  
 crit = "10"  
 input = ""  
 strOutput = ""  
 Do While Not objProc.StdOut.AtEndOfStream  
     input = objProc.StdOut.ReadLine  
     If InStr(input, "NTP") Then  
         strOutput = strOutput & input  
     End If  
 Loop  
 Set myRegExp = New RegExp  
 myRegExp.IgnoreCase = True  
 myRegExp.Global = True  
 myRegExp.Pattern = " NTP: ([+-][0-9]+\.[0-9]+)s"  
 Set myMatches = myRegExp.Execute(strOutput)  
 result = ""  
 If myMatches(0).SubMatches(0) <> "" Then  
     result = myMatches(0).SubMatches(0)  
 End If  
 For Each myMatch in myMatches  
     If myMatch.SubMatches(0) <> "" Then  
             If abs(result) > Abs(myMatch.SubMatches(0)) Then  
                 result = myMatch.SubMatches(0)  
             End If  
     End If  
 Next  
 If result = "" Then  
     Err = 3  
     Status = "UNKNOWN"  
  ElseIf result > crit Then  
     Err = 2  
     status = "CRITICAL"  
  ElseIf result > warn Then  
     Err = 1  
     status = "WARNING"  
  Else  
     Err = 0  
     status = "OK"  
 End If  
 Call oBag.AddValue("NTPStatus",status)  
 Call oAPI.Return(oBag)  

Next, we'll set the expressions (Unhealthy, Degraded and Healthy).
As you might notice i populated the Property Bag with parameter name as "NTPStatus" so you need to name your Parameter Name as follow :







Let the configure health step as it is.

For last, configure the alert rising as follows :



Now you need to override the monitor to a group or class as you prefer, and this is what it would like:



And that's it!

Hope you enjoy!

Detecting Windows License Activation Status Using ConfigMgr DCM and OpsMgr

If someone already done the work, why not share it ?

Tao Yang's post about this is amazing, like all the other posts he makes.

http://goo.gl/84qkX1

I followed his post until the end, and suddenly i came up with some errors in SCCM about the Powershell Scripts Execution Policie ...!

So, i've made this VBScript for the workaround (already post it too in Tao's blog!)

Just replace the part of the Powershell script with this VBScript if you bump into some execution policie issue.

 strComputer = "."   
 Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")   
 Set colItems = objWMIService.ExecQuery( _  
   "Select * from SoftwareLicensingProduct Where PartialProductKey IS NOT NULL AND ApplicationID = '55c92734-d682-4d71-983e-d6ec3f16059f'",,48)   
 For Each objItem in colItems   
 select case objItem.LicenseStatus  
         case "0"  
                 Wscript.Echo "Unlicensed"  
         case "1"  
                 Wscript.Echo "Licensed"  
         case "2"  
                 Wscript.Echo "Out-of-Box Grace Period"  
         case "3"  
                 Wscript.Echo "Out-of-Tolerance Grace Period"  
         case "4"  
                 Wscript.Echo "Non-Genuine Grace Period"  
         case "5"  
                 Wscript.Echo "Notification"  
         case "6"  
                 Wscript.Echo "ExtendedGrace"  
 end select  
 Next  

Cheers,

SCCM (ConfigMgr) - IIS Inventor with VBScript and WMI Classes

Recently we had this need for a customer of ours - Make an IIS Inventory report in SCCM with all the sites, related application pools, bindings and IIS versions.

It seems easy, but in a few moments it turned into a great nightmare, still a great challange - that i accepted gladly!

The first thing i've banged into was the fact of having multiple IIS versions and Operating Systems (IIS 6, 7, 8 - and 2003, 2008, 2012).
Why ?
Because for IIS version 6 you get information from "ROOT\MicrosoftIISv2" namespace, and for IIS>7 you have "ROOT\WebAdministration" namespace.
The problem wasn't having multiple data sources from where you could collect data from - i'll explain it further!
There's a solution, a really easy one to overcome this issue - install IIS WMI 6 Compatability role on your IIS>7 - this will make/create the "ROOT\MicrosoftIISv2" namespace even on IIS>7 machines - this way you'll only have a datasource to 'drink' data from.
But, there're some security issues, that some sysadmins don't like about this role, so i couldn't go there!

But, right before i set the classes i wanted, i created a simple collection with this WQL (All devices with IIS installed) :

 select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name = 'W3SVC'  

And right after, a custom agent setting where only there i would enable the further classes and the inventory of "C:\Windows\System32\InetSrv" folder so i could inventory inetmgr.exe file so i could've exact IIS version foreach machine.

Now, WMI Classes setup!
Ok! So i checked which classes i was going to set up and read information from, and came up with this :

IIS6 or with IIS 6 WMI Compatability :

 Namespace : MicrosoftIISv2  
 Class : IIsWebVirtualDirSetting  
 Query : SELECT * FROM IIsWebVirtualDirSetting

 Namespace : MicrosoftIISv2  
 Class : IIsWebServerSetting  
 Query : SELECT * FROM IIsWebServerSetting  

For IIS>7 :

 Namespace : WebAdministration  
 Class : Application  
 Query : SELECT * FROM Application

 Namespace : WebAdministration  
 Class : Site  
 Query : SELECT * FROM Site      

But right there, feeling really lucky about how it was going, and banged into my first issue!
At (ROOT\WebAdministration) Application class! You can't enable it because there's already a SCCM built-in class with this name.
So, after some googling i've learned that i could make a UNION class that "mirrors" all the information from a source into this new class in a namespace i wanted - and came up with this code :

 #pragma namespace("\\\\.\\root\\cimv2")  
 [Union,ViewSources{"select ApplicationPool,EnabledProtocols,ServiceAutoStartEnabled,ServiceAutoStartProvider,Path,PreloadEnabled,SiteName from Application"},ViewSpaces{"\\\\.\\root\\webadministration"},dynamic,Provider("MS_VIEW_INSTANCE_PROVIDER")]  
 class IIS_Application  
 {  
     [PropertySources{"ApplicationPool"}] string ApplicationPool;  
     [PropertySources{"EnabledProtocols"}] string EnabledProtocols;  
     [PropertySources{"ServiceAutoStartEnabled"}] boolean ServiceAutoStartEnabled;  
     [PropertySources{"ServiceAutoStartProvider"}] string ServiceAutoStartProvider;  
     [PropertySources{"Path"},key] string Path;  
     [PropertySources{"PreloadEnabled"}] boolean PreloadEnabled;  
     [PropertySources{"SiteName"},key] string SiteName;  
 };  

A Free-Tip : You must have all the KEY propreties mapped in your new class, meaning that if your source class has 3 key properties, your new custom class must also have those 3 key properties.

Basically it's kind of a view of "ROOT\WebAdministration\Application" class that i created in "ROOT\CIMV2" and named it "IIS_Application".

There was another problem i faced - SCCM hardware inventory can't read WMI Classes proprieties when they are objects, meaning that i've tried to set "ROOT\WebAdministration\Site" for having sites information with binding association and i couldn't.
So, again, i needed to make a brand new class :

 #pragma namespace ("\\\\.\\root\\cimv2")  
 class IIS_Bindings  
 {  
 [key]  
 STRING SiteName;   
 STRING Bindings;  
 UInt32 SiteId;  
 };  

But there's a problem - this is not a view! This is a simple new class - with no data, just empty! And the only way (i know) to populate this, was with this script :

 strComputer = "."  
     Set objWMIService = GetObject("winmgmts:{authenticationLevel=pktPrivacy}\\" & strComputer & "\root\cimv2")  
     Set colBindingsItems = objWMIService.ExecQuery("Select * from IIS_Bindings")  
     For Each objItem in colBindingsItems  
             objItem.Delete_()  
     Next  
     Set objWMIService = GetObject("winmgmts:{authenticationLevel=pktPrivacy}\\" & strComputer & "\root\WebAdministration")  
     Set colItems = objWMIService.ExecQuery("Select * from Site")  
     Set oWMI = GetObject("winmgmts:root\cimv2")  
     Set oData = oWMI.Get("IIS_Bindings")  
     Set oInstance = oData.SpawnInstance_  
     For Each objItem in colItems  
             oInstance.SiteName = objItem.Name  
             oInstance.SiteId = objItem.Id  
             Bindings = ""  
             For i = 0 to Ubound(objItem.Bindings)  
         Bindings = Bindings + objItem.Bindings(i).BindingInformation + "|"  
             Next  
             Bindings = LEFT(Bindings, (Len(Bindings) -1))  
             oInstance.Bindings = Bindings  
             oInstance.Put_()  
     Next  

Yes, you need to do a new based script application and deploy it into your collection - it will take a wile so you get data back into your CM database.

So now, that we've done all the setup we need to have all IIS information - in my case "Machine;Site;ApplicationPool;Path;Bindings;IISVersion" - it's time to query!
For every class you enable, SCCM will create a table like "MY_CLASSNAME_DATA", for example : IIS_Application_DATA
And it's here where all the magic can be done.

So, finally, i came up with this query (It needs to be modified to return better results, specially when it comes to the bindings ... i'll do it later someday!)

 SELECT DISTINCT  
     CSYS.Name0 as [Server Name],  
     IISApp.ApplicationPool00 as [AppPools],    
     IISApp.SiteName00 as [Site Name],  
     IISBind.Bindings00 as [Bindings],  
     SUBSTRING(SF.FileVersion, 1,14) AS [IIS Version]  
 FROM   
     [V_R_system] SYS with (nolock)  
     JOIN [v_GS_COMPUTER_SYSTEM] CSYS on CSYS.ResourceID = sys.ResourceID  
     JOIN [v_FullCollectionMembership] FCM on FCM.ResourceID = CSYS.ResourceID  
     JOIN [v_GS_SoftwareFile] SF on SF.ResourceID = SYS.ResourceID  
     FULL JOIN [IIS_Application_DATA] IISApp on IISApp.MachineID = SYS.ResourceID  
     FULL JOIN [IIS_Bindings_DATA] IISBind on IISBind.MachineID = SYS.ResourceID  
 WHERE  
     FCM.CollectionID like 'YOU_Collection_ID_Goes_Here'  
 AND   
     IISApp.SiteName00 = IISBind.SiteName00  
 AND   
     SF.FileName like '%inetmgr%'  

NOTE: This is the first version of the project, a POC if you want, just to show you how to get data, perhpaps it has some flaws - but it's a way of showing how to get data specially if you don't have it in the first place.

Hope this might be useful to you in someway.

Cheers,