October 2011 - Posts

Small Bug in Windows Server OS MP
Thursday, October 13, 2011 6:44 PM

As usual, Kevin Holman has a great post on the 6.0.6957.0 MP. There are issues with it so read the blog and comments.

Anyway… There’s a small text error in the alert details of the Available Megabytes of Memory is too low alert. There are a few rogue percent characters thrown in there to make it appear as though the threshold is a percentage. It’s not. I cracked open the data source just to be sure, it’s a script and it’s value is from AvailableMBytes in Win32_PerfRawData_PerfOS_Memory. Also, it looks like if you’ve got a server with the Microsoft Exchange System Attendant Service (MSExchangeSA) the monitor will always be healthy.

 

Check out http://scug.be/blogs/christopher/archive/2011/10/05/scom2007-new-server-os-mp-version-6-0-6957-0-has-some-bad-bugs.aspx for more issues with the MP.

by CSummers311 | with no comments

Filed under: OpsMgr

[NAP] Knowledge Dump
Sunday, October 09, 2011 3:38 PM

Shortly after I started my “series” of NAP related posts I was assigned back to ConfigMgr and had some personal life problems that got me disinterested in finishing the posts. Instead of spending a bunch of time finishing the series, I’m going to dump some of the useful things I worked on here in case some of it can be helpful to someone else.

Warning: These are all examples of how I did something many years ago. This was my first (brief) experience with NAP and I was a complete n00b with SSRS at the time. There may be better ways of accomplishing this now. If you know of any that are free-ish, please let me know.

Here’s an example of the report I created off of the Audit Collection Services data. The current RDL file has some things that aren’t shareable so I can’t post it here.

ACS Event Schema Modification

The default EventSchema.xml document in ACS doesn’t include the QuarantineSystemHealthResult string so I made these modifications to get it working. This was vanilla ACS so it may be included in the latest version.

        <Event SourceId="6272" SourceName="SE_AUDITID_ETW_NPS_RESPONSE_ACCEPT">
          <Call Name="AppendString" Param1="1" Param2="0" />
          <Call Name="AppendString" Param1="2" Param2="0" />
          <Call Name="AppendString" Param1="3" Param2="0" />
          <Call Name="AppendString" Param1="4" Param2="0" />
          <Call Name="AppendString" Param1="5" Param2="0" />
          <Call Name="AppendString" Param1="6" Param2="0" />
          <Call Name="AppendString" Param1="7" Param2="0" />
          <Call Name="AppendString" Param1="8" Param2="0" />
          <Call Name="AppendString" Param1="9" Param2="0" />
          <Call Name="AppendString" Param1="10" Param2="0" />
          <Call Name="AppendString" Param1="11" Param2="0" />
          <Call Name="AppendString" Param1="12" Param2="0" />
          <Call Name="AppendString" Param1="13" Param2="0" />
          <Call Name="AppendString" Param1="14" Param2="0" />
          <Call Name="AppendString" Param1="15" Param2="0" />
          <Call Name="AppendString" Param1="16" Param2="0" />
          <Call Name="AppendString" Param1="17" Param2="0" />
          <Call Name="AppendString" Param1="18" Param2="0" />
          <Call Name="AppendString" Param1="19" Param2="0" />
          <Call Name="AppendString" Param1="20" Param2="0" />
          <Call Name="AppendString" Param1="21" Param2="0" />
          <Call Name="AppendString" Param1="22" Param2="0" />
          <Call Name="AppendString" Param1="23" Param2="0" />
          <Call Name="AppendString" Param1="24" Param2="0" />
          <Call Name="AppendString" Param1="25" Param2="0" />
          <Call Name="AppendString" Param1="26" Param2="0" />
          <Call Name="AppendString" Param1="27" Param2="0" />
          <Param TypeName="typePrimarySid" />
          <Param TypeName="typePrimaryUser" />
          <Param TypeName="typePrimaryDomain" />
          <Param TypeName="typeFullyQualifiedSubjectUserName" />
          <Param TypeName="typeTargetSid" />
          <Param TypeName="typeTargetUser" />
          <Param TypeName="FullyQualifiedSubjectMachineName" />
          <Param TypeName="MachineInventory" />
          <Param TypeName="CalledStationID" />
          <Param TypeName="CallingStationID" />
          <Param TypeName="NASIPv4Address" />
          <Param TypeName="NASIPv6Address" />
          <Param TypeName="typeClientLogonId" />
          <Param TypeName="NASPortType" />
          <Param TypeName="NASPort" />
          <Param TypeName="typeClientUser" />
          <Param TypeName="ClientIPAddress" />
          <Param TypeName="ProxyPolicyName" />
          <Param TypeName="NetworkPolicyName" />
          <Param TypeName="AuthenticationProvider" />
          <Param TypeName="AuthenticationServer" />
          <Param TypeName="AuthenticationType" />
          <Param TypeName="EAPType" />
          <Param TypeName="AccountSessionIdentifier" />
          <Param TypeName="QuarantineState" />
          <Param TypeName="QuarantineSessionIdentifier" />
          <Param TypeName="typeString" />
        </Event>            
        <Event SourceId="6273" SourceName="SE_AUDITID_ETW_NPS_RESPONSE_REJECT">
          <Call Name="AppendString" Param1="1" Param2="0" />
          <Call Name="AppendString" Param1="2" Param2="0" />
          <Call Name="AppendString" Param1="3" Param2="0" />
          <Call Name="AppendString" Param1="4" Param2="0" />
          <Call Name="AppendString" Param1="5" Param2="0" />
          <Call Name="AppendString" Param1="6" Param2="0" />
          <Call Name="AppendString" Param1="7" Param2="0" />
          <Call Name="AppendString" Param1="8" Param2="0" />
          <Call Name="AppendString" Param1="9" Param2="0" />
          <Call Name="AppendString" Param1="10" Param2="0" />
          <Call Name="AppendString" Param1="11" Param2="0" />
          <Call Name="AppendString" Param1="12" Param2="0" />
          <Call Name="AppendString" Param1="13" Param2="0" />
          <Call Name="AppendString" Param1="14" Param2="0" />
          <Call Name="AppendString" Param1="15" Param2="0" />
          <Call Name="AppendString" Param1="16" Param2="0" />
          <Call Name="AppendString" Param1="17" Param2="0" />
          <Call Name="AppendString" Param1="18" Param2="0" />
          <Call Name="AppendString" Param1="19" Param2="0" />
          <Call Name="AppendString" Param1="20" Param2="0" />
          <Call Name="AppendString" Param1="21" Param2="0" />
          <Call Name="AppendString" Param1="22" Param2="0" />
          <Call Name="AppendString" Param1="23" Param2="0" />
          <Call Name="AppendString" Param1="24" Param2="0" />
          <Call Name="AppendString" Param1="25" Param2="0" />
          <Call Name="AppendString" Param1="26" Param2="0" />
          <Call Name="AppendString" Param1="27" Param2="0" />
          <Param TypeName="typePrimarySid" />
          <Param TypeName="typePrimaryUser" />
          <Param TypeName="typePrimaryDomain" />
          <Param TypeName="typeFullyQualifiedSubjectUserName" />
          <Param TypeName="typeTargetSid" />
          <Param TypeName="typeTargetUser" />
          <Param TypeName="FullyQualifiedSubjectMachineName" />
          <Param TypeName="MachineInventory" />
          <Param TypeName="CalledStationID" />
          <Param TypeName="CallingStationID" />
          <Param TypeName="NASIPv4Address" />
          <Param TypeName="NASIPv6Address" />
          <Param TypeName="typeClientLogonId" />
          <Param TypeName="NASPortType" />
          <Param TypeName="NASPort" />
          <Param TypeName="typeClientUser" />
          <Param TypeName="ClientIPAddress" />
          <Param TypeName="ProxyPolicyName" />
          <Param TypeName="NetworkPolicyName" />
          <Param TypeName="AuthenticationProvider" />
          <Param TypeName="AuthenticationServer" />
          <Param TypeName="AuthenticationType" />
          <Param TypeName="EAPType" />
          <Param TypeName="AccountSessionIdentifier" />
          <Param TypeName="ReasonCode" />
          <Param TypeName="Reason" />
          <Param TypeName="typeString" />
        </Event>
        <Event SourceId="6274" SourceName="SE_AUDITID_ETW_NPS_RESPONSE_DISCARD">
          <Call Name="AppendString" Param1="1" Param2="0" />
          <Call Name="AppendString" Param1="2" Param2="0" />
          <Call Name="AppendString" Param1="3" Param2="0" />
          <Call Name="AppendString" Param1="4" Param2="0" />
          <Call Name="AppendString" Param1="5" Param2="0" />
          <Call Name="AppendString" Param1="6" Param2="0" />
          <Call Name="AppendString" Param1="7" Param2="0" />
          <Call Name="AppendString" Param1="8" Param2="0" />
          <Call Name="AppendString" Param1="9" Param2="0" />
          <Call Name="AppendString" Param1="10" Param2="0" />
          <Call Name="AppendString" Param1="11" Param2="0" />
          <Call Name="AppendString" Param1="12" Param2="0" />
          <Call Name="AppendString" Param1="13" Param2="0" />
          <Call Name="AppendString" Param1="14" Param2="0" />
          <Call Name="AppendString" Param1="15" Param2="0" />
          <Call Name="AppendString" Param1="16" Param2="0" />
          <Call Name="AppendString" Param1="17" Param2="0" />
          <Call Name="AppendString" Param1="18" Param2="0" />
          <Call Name="AppendString" Param1="19" Param2="0" />
          <Call Name="AppendString" Param1="20" Param2="0" />
          <Call Name="AppendString" Param1="21" Param2="0" />
          <Call Name="AppendString" Param1="22" Param2="0" />
          <Call Name="AppendString" Param1="23" Param2="0" />
          <Call Name="AppendString" Param1="24" Param2="0" />
          <Call Name="AppendString" Param1="25" Param2="0" />
          <Call Name="AppendString" Param1="26" Param2="0" />
          <Param TypeName="typePrimarySid" />
          <Param TypeName="typePrimaryUser" />
          <Param TypeName="typePrimaryDomain" />
          <Param TypeName="typeFullyQualifiedSubjectUserName" />
          <Param TypeName="typeTargetSid" />
          <Param TypeName="typeTargetUser" />
          <Param TypeName="FullyQualifiedSubjectMachineName" />
          <Param TypeName="MachineInventory" />
          <Param TypeName="CalledStationID" />
          <Param TypeName="CallingStationID" />
          <Param TypeName="NASIPv4Address" />
          <Param TypeName="NASIPv6Address" />
          <Param TypeName="typeClientLogonId" />
          <Param TypeName="NASPortType" />
          <Param TypeName="NASPort" />
          <Param TypeName="typeClientUser" />
          <Param TypeName="ClientIPAddress" />
          <Param TypeName="ProxyPolicyName" />
          <Param TypeName="NetworkPolicyName" />
          <Param TypeName="AuthenticationProvider" />
          <Param TypeName="AuthenticationServer" />
          <Param TypeName="AuthenticationType" />
          <Param TypeName="EAPType" />
          <Param TypeName="AccountSessionIdentifier" />
          <Param TypeName="ReasonCode" />
          <Param TypeName="Reason" />
        </Event>
        <Event SourceId="6275" SourceName="SE_AUDITID_ETW_NPS_ACCT_RESPONSE_DISCARD">
          <Call Name="AppendString" Param1="1" Param2="0" />
          <Call Name="AppendString" Param1="2" Param2="0" />
          <Call Name="AppendString" Param1="3" Param2="0" />
          <Call Name="AppendString" Param1="4" Param2="0" />
          <Call Name="AppendString" Param1="5" Param2="0" />
          <Call Name="AppendString" Param1="6" Param2="0" />
          <Call Name="AppendString" Param1="7" Param2="0" />
          <Call Name="AppendString" Param1="8" Param2="0" />
          <Call Name="AppendString" Param1="9" Param2="0" />
          <Call Name="AppendString" Param1="10" Param2="0" />
          <Call Name="AppendString" Param1="11" Param2="0" />
          <Call Name="AppendString" Param1="12" Param2="0" />
          <Call Name="AppendString" Param1="13" Param2="0" />
          <Call Name="AppendString" Param1="14" Param2="0" />
          <Call Name="AppendString" Param1="15" Param2="0" />
          <Call Name="AppendString" Param1="16" Param2="0" />
          <Call Name="AppendString" Param1="17" Param2="0" />
          <Call Name="AppendString" Param1="18" Param2="0" />
          <Call Name="AppendString" Param1="19" Param2="0" />
          <Call Name="AppendString" Param1="20" Param2="0" />
          <Call Name="AppendString" Param1="21" Param2="0" />
          <Call Name="AppendString" Param1="22" Param2="0" />
          <Call Name="AppendString" Param1="23" Param2="0" />
          <Call Name="AppendString" Param1="24" Param2="0" />
          <Call Name="AppendString" Param1="25" Param2="0" />
          <Call Name="AppendString" Param1="26" Param2="0" />
          <Param TypeName="typePrimarySid" />
          <Param TypeName="typePrimaryUser" />
          <Param TypeName="typePrimaryDomain" />
          <Param TypeName="typeFullyQualifiedSubjectUserName" />
          <Param TypeName="typeTargetSid" />
          <Param TypeName="typeTargetUser" />
          <Param TypeName="FullyQualifiedSubjectMachineName" />
          <Param TypeName="MachineInventory" />
          <Param TypeName="CalledStationID" />
          <Param TypeName="CallingStationID" />
          <Param TypeName="NASIPv4Address" />
          <Param TypeName="NASIPv6Address" />
          <Param TypeName="typeClientLogonId" />
          <Param TypeName="NASPortType" />
          <Param TypeName="NASPort" />
          <Param TypeName="typeClientUser" />
          <Param TypeName="ClientIPAddress" />
          <Param TypeName="ProxyPolicyName" />
          <Param TypeName="NetworkPolicyName" />
          <Param TypeName="AuthenticationProvider" />
          <Param TypeName="AuthenticationServer" />
          <Param TypeName="AuthenticationType" />
          <Param TypeName="EAPType" />
          <Param TypeName="AccountSessionIdentifier" />
          <Param TypeName="ReasonCode" />
          <Param TypeName="Reason" />
        </Event>
    <Event SourceId="6276" SourceName="SE_AUDITID_ETW_NPS_RESPONSE_QUARANTINED">
          <Call Name="AppendString" Param1="1" Param2="0" />
          <Call Name="AppendString" Param1="2" Param2="0" />
          <Call Name="AppendString" Param1="3" Param2="0" />
          <Call Name="AppendString" Param1="4" Param2="0" />
          <Call Name="AppendString" Param1="5" Param2="0" />
          <Call Name="AppendString" Param1="6" Param2="0" />
          <Call Name="AppendString" Param1="7" Param2="0" />
          <Call Name="AppendString" Param1="8" Param2="0" />
          <Call Name="AppendString" Param1="9" Param2="0" />
          <Call Name="AppendString" Param1="10" Param2="0" />
          <Call Name="AppendString" Param1="11" Param2="0" />
          <Call Name="AppendString" Param1="12" Param2="0" />
          <Call Name="AppendString" Param1="13" Param2="0" />
          <Call Name="AppendString" Param1="14" Param2="0" />
          <Call Name="AppendString" Param1="15" Param2="0" />
          <Call Name="AppendString" Param1="16" Param2="0" />
          <Call Name="AppendString" Param1="17" Param2="0" />
          <Call Name="AppendString" Param1="18" Param2="0" />
          <Call Name="AppendString" Param1="19" Param2="0" />
          <Call Name="AppendString" Param1="20" Param2="0" />
          <Call Name="AppendString" Param1="21" Param2="0" />
          <Call Name="AppendString" Param1="22" Param2="0" />
          <Call Name="AppendString" Param1="23" Param2="0" />
          <Call Name="AppendString" Param1="24" Param2="0" />
          <Call Name="AppendString" Param1="25" Param2="0" />
          <Call Name="AppendString" Param1="26" Param2="0" />
          <Call Name="AppendString" Param1="27" Param2="0" />
          <Call Name="AppendString" Param1="28" Param2="0" />
          <Call Name="AppendString" Param1="29" Param2="0" />
          <Param TypeName="typePrimarySid" />
          <Param TypeName="typePrimaryUser" />
          <Param TypeName="typePrimaryDomain" />
          <Param TypeName="typePrimaryLogonId" />
          <Param TypeName="typeTargetSid" />
          <Param TypeName="typeTargetUser" />
          <Param TypeName="FullyQualifiedSubjectMachineName" />
          <Param TypeName="MachineInventory" />
          <Param TypeName="CalledStationID" />
          <Param TypeName="CallingStationID" />
          <Param TypeName="NASIPv4Address" />
          <Param TypeName="NASIPv6Address" />
          <Param TypeName="typeClientLogonId" />
          <Param TypeName="NASPortType" />
          <Param TypeName="NASPort" />
          <Param TypeName="typeClientUser" />
          <Param TypeName="ClientIPAddress" />
          <Param TypeName="ProxyPolicyName" />
          <Param TypeName="NetworkPolicyName" />
          <Param TypeName="AuthenticationProvider" />
          <Param TypeName="AuthenticationServer" />
          <Param TypeName="AuthenticationType" />
          <Param TypeName="EAPType" />
          <Param TypeName="AccountSessionIdentifier" />
          <Param TypeName="QuarantineState" />
          <Param TypeName="ExtendedQuarantineState" />
          <Param TypeName="QuarantineSessionID" />
          <Param TypeName="QuarantineHelpURL" />
          <Param TypeName="QuarantineSystemHealthResult" />
    </Event>            
    <Event SourceId="6277" SourceName="SE_AUDITID_ETW_NPS_RESPONSE_ON_PROBATION">
          <Call Name="AppendString" Param1="1" Param2="0" />
          <Call Name="AppendString" Param1="2" Param2="0" />
          <Call Name="AppendString" Param1="3" Param2="0" />
          <Call Name="AppendString" Param1="4" Param2="0" />
          <Call Name="AppendString" Param1="5" Param2="0" />
          <Call Name="AppendString" Param1="6" Param2="0" />
          <Call Name="AppendString" Param1="7" Param2="0" />
          <Call Name="AppendString" Param1="8" Param2="0" />
          <Call Name="AppendString" Param1="9" Param2="0" />
          <Call Name="AppendString" Param1="10" Param2="0" />
          <Call Name="AppendString" Param1="11" Param2="0" />
          <Call Name="AppendString" Param1="12" Param2="0" />
          <Call Name="AppendString" Param1="13" Param2="0" />
          <Call Name="AppendString" Param1="14" Param2="0" />
          <Call Name="AppendString" Param1="15" Param2="0" />
          <Call Name="AppendString" Param1="16" Param2="0" />
          <Call Name="AppendString" Param1="17" Param2="0" />
          <Call Name="AppendString" Param1="18" Param2="0" />
          <Call Name="AppendString" Param1="19" Param2="0" />
          <Call Name="AppendString" Param1="20" Param2="0" />
          <Call Name="AppendString" Param1="21" Param2="0" />
          <Call Name="AppendString" Param1="22" Param2="0" />
          <Call Name="AppendString" Param1="23" Param2="0" />
          <Call Name="AppendString" Param1="24" Param2="0" />
          <Call Name="AppendString" Param1="25" Param2="0" />
          <Call Name="AppendString" Param1="26" Param2="0" />
          <Call Name="AppendString" Param1="27" Param2="0" />
          <Call Name="AppendString" Param1="28" Param2="0" />
          <Call Name="AppendString" Param1="29" Param2="0" />
          <Call Name="AppendString" Param1="30" Param2="0" />
          <Param TypeName="typePrimarySid" />
          <Param TypeName="typePrimaryUser" />
          <Param TypeName="typePrimaryDomain" />
          <Param TypeName="typePrimaryLogonId" />
          <Param TypeName="typeTargetSid" />
          <Param TypeName="typeTargetUser" />
          <Param TypeName="FullyQualifiedSubjectMachineName" />
          <Param TypeName="MachineInventory" />
          <Param TypeName="CalledStationID" />
          <Param TypeName="CallingStationID" />
          <Param TypeName="NASIPv4Address" />
          <Param TypeName="NASIPv6Address" />
          <Param TypeName="typeClientLogonId" />
          <Param TypeName="NASPortType" />
          <Param TypeName="NASPort" />
          <Param TypeName="typeClientUser" />
          <Param TypeName="ClientIPAddress" />
          <Param TypeName="ProxyPolicyName" />
          <Param TypeName="NetworkPolicyName" />
          <Param TypeName="AuthenticationProvider" />
          <Param TypeName="AuthenticationServer" />
          <Param TypeName="AuthenticationType" />
          <Param TypeName="EAPType" />
          <Param TypeName="AccountSessionIdentifier" />
          <Param TypeName="QuarantineState" />
          <Param TypeName="ExtendedQuarantineState" />
          <Param TypeName="QuarantineSessionID" />
          <Param TypeName="QuarantineHelpURL" />
          <Param TypeName="QuarantineSystemHealthResult" />
          <Param TypeName="QuarantineGraceTime" />
    </Event>
    <Event SourceId="6278" SourceName="SE_AUDITID_ETW_NPS_RESPONSE_FULL_ACCESS">
          <Call Name="AppendString" Param1="1" Param2="0" />
          <Call Name="AppendString" Param1="2" Param2="0" />
          <Call Name="AppendString" Param1="3" Param2="0" />
          <Call Name="AppendString" Param1="4" Param2="0" />
          <Call Name="AppendString" Param1="5" Param2="0" />
          <Call Name="AppendString" Param1="6" Param2="0" />
          <Call Name="AppendString" Param1="7" Param2="0" />
          <Call Name="AppendString" Param1="8" Param2="0" />
          <Call Name="AppendString" Param1="9" Param2="0" />
          <Call Name="AppendString" Param1="10" Param2="0" />
          <Call Name="AppendString" Param1="11" Param2="0" />
          <Call Name="AppendString" Param1="12" Param2="0" />
          <Call Name="AppendString" Param1="13" Param2="0" />
          <Call Name="AppendString" Param1="14" Param2="0" />
          <Call Name="AppendString" Param1="15" Param2="0" />
          <Call Name="AppendString" Param1="16" Param2="0" />
          <Call Name="AppendString" Param1="17" Param2="0" />
          <Call Name="AppendString" Param1="18" Param2="0" />
          <Call Name="AppendString" Param1="19" Param2="0" />
          <Call Name="AppendString" Param1="20" Param2="0" />
          <Call Name="AppendString" Param1="21" Param2="0" />
          <Call Name="AppendString" Param1="22" Param2="0" />
          <Call Name="AppendString" Param1="23" Param2="0" />
          <Call Name="AppendString" Param1="24" Param2="0" />
          <Call Name="AppendString" Param1="25" Param2="0" />
          <Call Name="AppendString" Param1="26" Param2="0" />
          <Call Name="AppendString" Param1="27" Param2="0" />
          <Call Name="AppendString" Param1="28" Param2="0" />
          <Call Name="AppendString" Param1="29" Param2="0" />
          <Param TypeName="typePrimarySid" />
          <Param TypeName="typePrimaryUser" />
          <Param TypeName="typePrimaryDomain" />
          <Param TypeName="typePrimaryLogonId" />
          <Param TypeName="typeTargetSid" />
          <Param TypeName="typeTargetUser" />
          <Param TypeName="FullyQualifiedSubjectMachineName" />
          <Param TypeName="MachineInventory" />
          <Param TypeName="CalledStationID" />
          <Param TypeName="CallingStationID" />
          <Param TypeName="NASIPv4Address" />
          <Param TypeName="NASIPv6Address" />
          <Param TypeName="typeClientLogonId" />
          <Param TypeName="NASPortType" />
          <Param TypeName="NASPort" />
          <Param TypeName="typeClientUser" />
          <Param TypeName="ClientIPAddress" />
          <Param TypeName="ProxyPolicyName" />
          <Param TypeName="NetworkPolicyName" />
          <Param TypeName="AuthenticationProvider" />
          <Param TypeName="AuthenticationServer" />
          <Param TypeName="AuthenticationType" />
          <Param TypeName="EAPType" />
          <Param TypeName="AccountSessionIdentifier" />
          <Param TypeName="QuarantineState" />
          <Param TypeName="ExtendedQuarantineState" />
          <Param TypeName="QuarantineSessionID" />
          <Param TypeName="QuarantineHelpURL" />
          <Param TypeName="QuarantineSystemHealthResult" />
    </Event>

Parse the Health Result

I used 2 functions in my reports. fn_ParseHealthResult is used in my summary reports and just outputs Passed/Failed. fn_ParseHeathResultFull is used in my client details report and outputs the error code for each health check.

Example Usage

Select TOP(10) TargetUser as ClientUser, String21, CreationTime, Firewall, [Antivirus (On/Off)], [Antivirus Up-to-date], [Antispyware (On/Off)], [Antispyware Up-to-date], [Automatic Updates (On/Off)], [Security Updates Compliance], [Security Updates Severity], [Security Updates Source]
FROM AdtServer.dvAll
CROSS APPLY 
    fn_ParseHealthResultFull(String21)
WHERE EventId = 6278
AND TargetUser LIKE @ClientUser1 + '%' 
and String04 = @MAC
ORDER BY SequenceNo DESC

fn_ParseHealthResult

CREATE FUNCTION [dbo].[fn_ParseHealthResult] 
(    
    -- Add the parameters for the function here
    @HealthResult varchar(max)
)
RETURNS @Results TABLE
(
[Firewall] varchar(max), 
[Antivirus (On/Off)] varchar(max),
[Antivirus Up-to-date] varchar(max),
[Antispyware (On/Off)] varchar(max),
[Antispyware Up-to-date] varchar(max),
[Automatic Updates (On/Off)] varchar(max),
[Security Updates Compliance] varchar(max),
[Security Updates Severity] varchar(max),
[Security Updates Source] varchar(max)    
)
AS
BEGIN

DECLARE @Elements int
DECLARE @Firewall varchar(max)
DECLARE @AVONOFF varchar(max)
DECLARE @AVU2D varchar(max)
DECLARE @ASONOFF varchar(max)
DECLARE @ASU2D varchar(max)
DECLARE @AUONOFF varchar(max)
DECLARE @SUCompliance varchar(max)
DECLARE @SUSeverity varchar(max)
DECLARE @SUSource varchar(max)
DECLARE @Status varchar(max)
DECLARE @i int
DECLARE @Start int
DECLARE @End int

SET @End = 0
SET @i = 1
SET @Elements = LEN(@HealthResult) - LEN(REPLACE(@HealthResult, '(', ''))

IF @Elements = 0
BEGIN
    Set @Firewall = 'Unknown'
    Set @AVONOFF = 'Unknown'
    Set @AVU2D = 'Unknown'
    Set @ASONOFF = 'Unknown'
    Set @ASU2D = 'Unknown'
    Set @AUONOFF = 'Unknown'
    Set @SUCompliance = 'Unknown'
    Set @SUSeverity = 'Unknown'
    Set @SUSource = 'Unknown'
END
ELSE
BEGIN

while @i <= @Elements
    BEGIN
        SET @Start = CHARINDEX('(',@HealthResult,@End)
        SET @End = CHARINDEX(')',@HealthResult,@Start)
        SET @Status = SUBSTRING(@HealthResult, @Start + 1, @End - @Start -1)
        IF @Status = '0x0 - '
            SET @Status = 'Passed'
        ELSE
            SET @Status = 'Failed'
        IF @i = 1
            SET @Firewall = @Status
        IF @i = 2
            SET @AVONOFF = @Status
        IF @i = 3
        BEGIN
            IF @AVONOFF = 'Failed'
                SET @AVU2D = 'Failed'
            ELSE
                SET @AVU2D = @Status
        END
        IF @Elements > 6
            BEGIN
            IF @i = 4
                SET @ASONOFF = @Status
            IF @i = 5
                SET @ASU2D = @Status
            IF @i = 6
                SET @AUONOFF = @Status
            IF @i = 7
                SET @SUCompliance = @Status
            IF @i = 8
                SET @SUSeverity = @Status
            IF @i = 9
                SET @SUSource = @Status
            END
        ELSE
            BEGIN
            SET @ASONOFF = 'Not monitored'
            SET @ASU2D = 'Not monitored'
            IF @i = 4
                SET @AUONOFF = @Status
            IF @i = 5
                SET @SUCompliance = @Status
            IF @i = 6
                SET @SUSeverity = @Status
            END
        SET @i = @i + 1    
    END    
END
INSERT INTO @Results VALUES (@Firewall, 
    @AVONOFF,
    @AVU2D,
    @ASONOFF,
    @ASU2D,
    @AUONOFF,
    @SUCompliance,
    @SUSeverity,
    @SUSource)
RETURN 
END

fn_ParseHealthResultFull

CREATE FUNCTION [dbo].[fn_ParseHealthResultFull] 
(    
    -- Add the parameters for the function here
    @HealthResult varchar(max)
)
RETURNS @Results TABLE
(
[Firewall] varchar(max), 
[Antivirus (On/Off)] varchar(max),
[Antivirus Up-to-date] varchar(max),
[Antispyware (On/Off)] varchar(max),
[Antispyware Up-to-date] varchar(max),
[Automatic Updates (On/Off)] varchar(max),
[Security Updates Compliance] varchar(max),
[Security Updates Severity] varchar(max),
[Security Updates Source] varchar(max)    
)
AS
BEGIN

DECLARE @Elements int
DECLARE @Firewall varchar(max)
DECLARE @AVONOFF varchar(max)
DECLARE @AVU2D varchar(max)
DECLARE @ASONOFF varchar(max)
DECLARE @ASU2D varchar(max)
DECLARE @AUONOFF varchar(max)
DECLARE @SUCompliance varchar(max)
DECLARE @SUSeverity varchar(max)
DECLARE @SUSource varchar(max)
DECLARE @Status varchar(max)
DECLARE @i int
DECLARE @Start int
DECLARE @End int

SET @End = 0
SET @i = 1
SET @Elements = LEN(@HealthResult) - LEN(REPLACE(@HealthResult, '(', ''))

IF @Elements = 0
BEGIN
    Set @Firewall = 'Unknown'
    Set @AVONOFF = 'Unknown'
    Set @AVU2D = 'Unknown'
    Set @ASONOFF = 'Unknown'
    Set @ASU2D = 'Unknown'
    Set @AUONOFF = 'Unknown'
    Set @SUCompliance = 'Unknown'
    Set @SUSeverity = 'Unknown'
    Set @SUSource = 'Unknown'
END
ELSE
BEGIN

while @i <= @Elements
    BEGIN
        SET @Start = CHARINDEX('(',@HealthResult,@End)
        SET @End = CHARINDEX(')',@HealthResult,@Start)
        SET @Status = SUBSTRING(@HealthResult, @Start + 1, @End - @Start -1)
        IF @Status = '0x0 - '
            SET @Status = 'Passed'
        IF @Status = '0xc0ff0001 - A system health component is not enabled. ..'
            SET @Status = 'Disabled'
        IF @Status = '0xc0ff0048 - The signatures for a particular third-party system health component are not up to date...'
            Set @Status = 'Signature out of date'
        If @Status = '0xc0ff0002 - A system health component is not installed...'
            Set @Status = 'Not installed'
        If @Status = '0xc0ff0047 - A third-party system health component is not enabled. ..'
            Set @Status = 'Disabled'
        If @Status = '0xc0ff0003 - The Windows Security Center service is not running...'
            SET @Status = 'Windows Security Center is not running'
        IF @i = 1
            SET @Firewall = @Status
        IF @i = 2
            SET @AVONOFF = @Status
        IF @i = 3
        BEGIN
            IF @AVONOFF <> 'Passed' and @Status = 'Passed'
                SET @AVU2D = @AVONOFF
            ELSE
                SET @AVU2D = @Status
        END
        IF @Elements > 6
            BEGIN
            IF @i = 4
                SET @ASONOFF = @Status
            IF @i = 5
                SET @ASU2D = @Status
            IF @i = 6
                SET @AUONOFF = @Status
            IF @i = 7
                SET @SUCompliance = @Status
            IF @i = 8
                SET @SUSeverity = @Status
            IF @i = 9
                SET @SUSource = @Status
            END
        ELSE
            BEGIN
            SET @ASONOFF = 'Not monitored'
            SET @ASU2D = 'Not monitored'
            IF @i = 4
                SET @AUONOFF = @Status
            IF @i = 5
                SET @SUCompliance = @Status
            IF @i = 6
                SET @SUSeverity = @Status
            END
        SET @i = @i + 1    
    END    
END
INSERT INTO @Results VALUES (@Firewall, 
    @AVONOFF,
    @AVU2D,
    @ASONOFF,
    @ASU2D,
    @AUONOFF,
    @SUCompliance,
    @SUSeverity,
    @SUSource)
RETURN 
END

by CSummers311 | with no comments

Filed under: NAP