ISE 2.4p3 and SCCM 1806 always NonCompliant due to bad SQL query

[Alin Scarlat]

Hello,

We've managed to integrate ISE 2.4p3 with SCCM 1806 (we installed 1802 and then updated to the latest release 1806) but, regardless of the compliance status of the endpoints in SCCM, the return MDM.DeciveCompliantStatus is always NonCompliant.

Upon enabling DEBUG and TRACE for External MDM servers, we've noticed something strange in the logs:

2018-10-05 08:54:59,491 DEBUG  [Thread-477][] cisco.cpm.mdm.pip.MdmPartnerPIPHandler -::::- using default MDM server ISE_SCCM to get the MDM info data)
2018-10-05 08:54:59,712 TRACE  [Thread-477][] cpm.mdm.sccm.util.WmiUtil -::::- WMI Client executeQuery - start query: select SMS_R_System.Name, SMS_G_System_CI_ComplianceState.CI_UniqueID, SMS_G_System_CI_ComplianceState.ComplianceState, SMS_G_System_CI_ComplianceState.LocalizedDisplayName, SMS_G_System_CH_ClientSummary.LastPolicyRequest from SMS_R_System left join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_NETWORK_ADAPTER on SMS_G_System_NETWORK_ADAPTER.ResourceId = SMS_R_System.ResourceId where SMS_R_System.MacAddresses like '%64:31:50:10:DA:60%' OR SMS_G_System_NETWORK_ADAPTER.MACAddress like '%64:31:50:10:DA:60%', keyproperty: SMS_G_System_CI_ComplianceState@CI_UniqueID
2018-10-05 08:54:59,830 TRACE  [Thread-477][] cpm.mdm.sccm.util.WmiUtil -::::- WmiUtil - executeQuery - last element - Incorrect function. [0x00000001]
2018-10-05 08:54:59,830 TRACE  [Thread-477][] cpm.mdm.sccm.util.WmiUtil -::::- WMI Client executeQuery - end
2018-10-05 08:54:59,830 DEBUG  [Thread-477][] cpm.mdm.sccm.api.SccmClient -::::- Device with mac: 64-31-50-10-DA-60 formatted mac 64:31:50:10:DA:60 does not exist in SCCM server: ISE_SCCM
2018-10-05 08:54:59,832 DEBUG  [Thread-477][] cisco.cpm.mdm.pip.MdmPartnerPIPHandler -::::- MDM PIP returns for endpoint sessionId AC101E140000009159E0E557 and macAddr 64-31-50-10-DA-60 
2018-10-05 08:54:59,832 TRACE  [Thread-477][] cisco.cpm.mdm.pip.MdmPartnerPIPHandler -::::- MDM PIP for attribute: MDM.DeviceCompliantStatus, returned: {MDM.MDMServerName=, MDM.MDMServerReachable=true, MDM.Model=, MDM.compliance=[Ljava.lang.String;@533a9de0, MDM.DeviceRegisterStatus=false, MDM.PinLockStatus=false, MDM.Provider=false, MDM.SerialNumber=, MDM.register_url=N/A, MDM.IMEI=, MDM.DiskEncryptionStatus=false, MDM.ServerType=, MDM.OsVersion=, MDM.PhoneNumber=, MDM.UDID=, MDM.JailBrokenStatus=false, MDM.UserNotified=false, MDM.MEID=, MDM.DaysSinceLastCheckin=, MDM.Manufacturer=, MDM.DeviceCompliantStatus=false, MDM.MDMFailureReason=, MDM.query_attribute=MDM.DeviceCompliantStatus}

I have highlighted with red the issues.

The SQL query that is ran against the SCCM seem to be wrong according to this output. Also we've taken this query and ran it in SCCM and it states that it is incorrect.

The SCCM server is installed on a Windows Server 2012 R2, updated, having the SQL Server as MsSQL 2017. That SCCM client installed on the endpoints has the version 5.00.8692.1008.

If we run the query, without that underlined and red string, meaning:

select SMS_R_System.Name, SMS_G_System_CI_ComplianceState.CI_UniqueID, SMS_G_System_CI_ComplianceState.ComplianceState, SMS_G_System_CI_ComplianceState.LocalizedDisplayName, SMS_G_System_CH_ClientSummary.LastPolicyRequest from SMS_R_System left join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_NETWORK_ADAPTER on SMS_G_System_NETWORK_ADAPTER.ResourceId = SMS_R_System.ResourceId where SMS_R_System.MacAddresses like '%64:31:50:10:DA:60%' OR SMS_G_System_NETWORK_ADAPTER.MACAddress like '%64:31:50:10:DA:60%'

Then we get the endpoint from the database without any issues.

Anybody has any ideas how to fix this issue or a workaround?

Looking forward to your reply.

[Nidhi]

The query does not look right. 

Please raise a TAC case to debug this issue. 

Thanks,

Nidh

[Serhii Kucherenko · Accepted solution]

Hi Alin.

Here is some information which could be useful for your and other community members about ISE SCCM MDM integration tshoot.

1. The query which you see is correct. Part of the line which you've highlighted is not the part of the query itself. Actual query is ended with a comma:

Query - 

select SMS_R_System.Name, SMS_G_System_CI_ComplianceState.CI_UniqueID, SMS_G_System_CI_ComplianceState.ComplianceState, SMS_G_System_CI_ComplianceState.LocalizedDisplayName, SMS_G_System_CH_ClientSummary.LastPolicyRequest from SMS_R_System left join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_NETWORK_ADAPTER on SMS_G_System_NETWORK_ADAPTER.ResourceId = SMS_R_System.ResourceId where SMS_R_System.MacAddresses like '%64:31:50:10:DA:60%' OR SMS_G_System_NETWORK_ADAPTER.MACAddress like '%64:31:50:10:DA:60%',

ISE internal use only - 

keyproperty: SMS_G_System_CI_ComplianceState@CI_UniqueID

2. Error which you see actually indicates that ISE got an empty response from SCCM. Internally ISE understands this as 'unregistered' status.

It's actually mentioned in one of the lines in your debugs - 

2018-10-05 08:54:59,830 DEBUG [Thread-477][] cpm.mdm.sccm.api.SccmClient -::::- Device with mac: 64-31-50-10-DA-60 formatted mac 64:31:50:10:DA:60 does not exist in SCCM server: ISE_SCCM

I agree that message - 

WmiUtil - executeQuery - last element - Incorrect function. [0x00000001]

is actually misleading. I'll open an enhancement request to have this corrected in future releases.

3. Here is an example from my lab where same error is printed for the endpoint which does not exist on SCCM server. For other endpoints (registered) same SCCM server works fine (since the same query is used all the time there should be no problems with the query syntax):

2018-10-05 11:14:36,666 TRACE [https-jsse-nio-192.168.43.24-8443-exec-6][] cpm.mdm.sccm.util.WmiUtil -::::- WMI Client executeQuery - start query: select SMS_R_System.Name, SMS_G_System_CI_ComplianceState.CI_UniqueID, SMS_G_System_CI_ComplianceState.ComplianceState, SMS_G_System_CI_ComplianceState.LocalizedDisplayName, SMS_G_System_CH_ClientSummary.LastPolicyRequest from SMS_R_System left join SMS_G_System_CI_ComplianceState on SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_NETWORK_ADAPTER on SMS_G_System_NETWORK_ADAPTER.ResourceId = SMS_R_System.ResourceId where SMS_R_System.MacAddresses like '%C0:4A:00:1F:6B:39%' OR SMS_G_System_NETWORK_ADAPTER.MACAddress like '%C0:4A:00:1F:6B:39%', keyproperty: SMS_G_System_CI_ComplianceState@CI_UniqueID
2018-10-05 11:14:36,805 TRACE [https-jsse-nio-192.168.43.24-8443-exec-6][] cpm.mdm.sccm.util.WmiUtil -::::- WmiUtil - executeQuery - last element - Incorrect function. [0x00000001]

4. You created correct query and I assume that you did it in SCCM itself, please correct me if I'm wrong.

For future troubleshooting (just not to limit query to single MAC) you can use this syntax:

select SMS_R_System.Name,

SMS_G_System_CI_ComplianceState.CI_UniqueID,

SMS_G_System_CI_ComplianceState.ComplianceState,

SMS_G_System_CI_ComplianceState.LocalizedDisplayName,

SMS_G_System_CH_ClientSummary.LastPolicyRequest from

SMS_R_System left join SMS_G_System_CI_ComplianceState on

SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId left join

SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId left join

SMS_G_System_NETWORK_ADAPTER on SMS_G_System_NETWORK_ADAPTER.ResourceId = SMS_R_System.ResourceId where SMS_R_System.MacAddresses=##PRM:SMS_R_System.MACAddresses## OR

SMS_G_System_NETWORK_ADAPTER.MACAddress=##PRM:SMS_G_System_NETWORK_ADAPTER.MACAddress##

After lunching this query same MAC address needs to be provided two times.

5. Now coming back to the original issue. I seen same behaviour some time ago in one setup. Issue there has been caused by missing WMI permissions on SCCM server.

When you run a query on SCCM server you probably have a different account than the one which is used for integration.

Steps listed below are very helpful when we need to exclude ISE from the picture:

a. Run WBEMTest on any windows machine in your network:

b. Press 'Connect'

c. Provide a Namespace path and credentials which needs to be used. Please use same account which is used for ISE integration

In the path place SCCM server name, and before site name place site_. My site name is KRK so as a result I have site_KRK. Then press connect once again.

d. Press on Query

Here we need to use a query with exact MAC address:

select SMS_R_System.Name,

SMS_G_System_CI_ComplianceState.CI_UniqueID,

SMS_G_System_CI_ComplianceState.ComplianceState,

SMS_G_System_CI_ComplianceState.LocalizedDisplayName,

SMS_G_System_CH_ClientSummary.LastPolicyRequest from

SMS_R_System left join SMS_G_System_CI_ComplianceState on

SMS_G_System_CI_ComplianceState.ResourceID = SMS_R_System.ResourceId left join

SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId left join

SMS_G_System_NETWORK_ADAPTER on SMS_G_System_NETWORK_ADAPTER.ResourceId = SMS_R_System.ResourceId where SMS_R_System.MacAddresses like '%C0:4A:00:1F:6B:39%' OR

SMS_G_System_NETWORK_ADAPTER.MACAddress like '%C0:4A:00:1F:6B:39%'

e. Navigate through the query results

Double-click on lines one by one to see what is inside

Choose 'Hide System Properties' in new window, then choose something in the Properties area and press on 'Show MOF'

In case if this is an issue with WMI permissions you will get an empty result in step e. 

Please open a TAC service request as was advised by Nidhi to get further assistance.

[Alin Scarlat]

Hello skuchere,

Thank you very much for this detailed troubleshooting method. I will test this on Monday and come back with a reply.

However, if the account that ISE is querying the SCCM server does not have WMI permissions, then I assume that the "Test connection" from External MDM ISE menu does not cover these kind of tests?

I had WMI issues at the beginning, when I was trying to link the SCCM server with ISE and after I gave the WMI permissions and the permissions on those 2 registry keys (which were mentioned in the Administration Guide), the test was ok.

Looking forward to your reply.

[Serhii Kucherenko]

Test connection button just tries to establish a DCOM connection over MS-RPC (this checks if correct registry keys are in place along with required DCOM permissions).  The actual query does not performed during the test unfortunately. SCCM is a bit different from other MDM servers, when we have classical MDM ISE always execute an API call during a test but with SCCM we only send requests for the specific endpoints at time of the authentication this means that we technically do not have a good way to check WMI permissions required to execute query.

[Alin Scarlat]

Hello skuchere,

I've managed to test your troubleshooting method and it was indeed something with the permissions.

Although the user used in External MDM page had access over those 2 registry entries (as stated in the Administration Guide), it didn't had access in the SCCM itself. After adding it with the smallest access, the query ran just fine and ISE managed to get the compliance status from SCCM as it should.

Thank you very much for your help.

[Serhii Kucherenko]

Glad that it helped!

[Alin Scarlat]

Hello,

There is still an issue that I haven't managed to figure it out or fix it.

When a client is first non-compliant, the query will return the MOF having ComplianceState = 2.

After the client gets compliant and the query MOF has the ComplianceState = 1, the query still returns the old and the current value. At this point ISE will receive the current compliant state and the old compliant state and will interpret that the endpoint is not compliant.

For example:

The endpoint shows as compliant

The query that ISE executes on SCCM returns 4 results:

The first 3 are somehow similar to this output (the difference being the hour that the check ran)

But the last one has the ComplianceState = 2 which is an old compliance check:

At this point I have no idea how to "force" ISE to ignore the old "reports" and just verify the actual and last compliance check.

Looking forward to your reply on how to troubleshoot this issue further.

Thank you.