Security

We are starting to do some testing with certificates and including the Microsoft Strong Mapping feature. We have upgraded to the latest Intune Extension, which was noted to address the parsing of the Intune Device ID attribute from the SAN URI field in the certificate, but looks like we might be running into an issue with this and using the Device Id lookup attribute in the Endpoint database. We have been using the following query to pull the data;

select attributes->>'Intune User Principal Name' as "Intune User Principal Name",attributes->>'Intune Model' as "Intune Model",attributes->>'Intune Jail Broken' as "Intune Jail Broken",attributes->>'Intune Operating System' as "Intune Operating System",attributes->>'Intune Managed Device Owner Type' as "Intune Managed Device Owner Type",attributes->>'Intune Management Agent' as "Intune Management Agent",attributes->>'Intune Azure AD Registered' as "Intune Azure AD Registered",attributes->>'Intune Compliance State' as "Intune Compliance State",attributes->>'Intune Device Name' as "Intune Device Name",attributes->>'Intune Azure AD Device Id' as "Intune Azure AD Device Id" FROM tips_endpoints WHERE attributes->>'Intune ID' = LOWER('%{Certificate:Subject-AltName-URI}')

However, we keep getting the following error;

Failed to get value for attributes=[Intune Azure AD Device Id, Intune Azure AD Registered, Intune Compliance State, Intune Device Name, Intune Jail Broken, Intune Managed Device Owner Type, Intune Management Agent, Intune Model, Intune Operating System, Intune User Principal Name].

I have confirmed the details are in the field as needed for the Endpoint;

I have tried to revise the query to pull just the first field but no luck so far (tried using split part and substring) but still not able to get it to grab the first field only. I can confirm it does work if we drop the strong mapping field from the certificate, which indicates the query is correct, but is not parsing as desired. We would like to maintain the details in the SAN URI field but curious if anyone has run into this?

Thanks very much, 

Strong Mapping and support for SAN URI fields are two completely separate topics. Strong mapping is needed for hybrid joined computers to verify the on-premises AD relation. For computers that are only joined to Entra ID, it should not be needed.

What you refer to here is support for the SAN URI that can have multiple values, and the latest Intune Extension can parse from multiple SAN-URI values the DeviceId:xxxx. However that works for the HTTP method where you realtime query the extension.

The method described above is an SQL Query into the endpoint database, which doesn't work the way you try it. What you may try is extracting the DeviceId: value with SQL language:

select attributes->>'Intune User Principal Name' as "Intune User Principal Name",attributes->>'Intune Model' as "Intune Model",attributes->>'Intune Jail Broken' as "Intune Jail Broken",attributes->>'Intune Operating System' as "Intune Operating System",attributes->>'Intune Managed Device Owner Type' as "Intune Managed Device Owner Type",attributes->>'Intune Management Agent' as "Intune Management Agent",attributes->>'Intune Azure AD Registered' as "Intune Azure AD Registered",attributes->>'Intune Compliance State' as "Intune Compliance State",attributes->>'Intune Device Name' as "Intune Device Name",attributes->>'Intune Azure AD Device Id' as "Intune Azure AD Device Id" FROM tips_endpoints WHERE attributes->>'Intune ID' = split_part(regexp_replace('%{Certificate:Subject-AltName-URI}','^.*DeviceId:',''),',',1)

Most relevant here is the part after 'Intune ID' = and you could try to adapt your query to this method. What happens here is that everything up to DeviceId: is first stripped out, then split on the ',' and taking the first part only: (DeviceId:55a52ad8-XXXX-4a86-XXXX-2f5436fd5779, tag:microsoft.com,2022-09-14:sid:S-X-X-XX-XXXX05284-3415300890-1872512156-XXXXXX). Hope this helps... or use the HTTP Realtime method.

Thanks so much for the reply and details - much appreciated.

We have some hybrid joined devices still, thus we were looking to ensure we had the Strong Mapping enabled, until we have all the machines as Entra joined only. Our understanding also was that the Strong Mapping was needed on both the Device certificate and also the User certificate, with it being the User certificate that seems that we need to tweak the query for. We are currently using the HTTP realtime method, but were hoping to add the Endpoint DB query also. I gave a quick test of the query and it looks to have made the fix on our end!!!

Thanks so very much again for all the assistance on this!!!

select attributes->>'Intune User Principal Name' as "Intune User Principal Name",attributes->>'Intune Model' as "Intune Model",attributes->>'Intune Jail Broken' as "Intune Jail Broken",attributes->>'Intune Operating System' as "Intune Operating System",attributes->>'Intune Managed Device Owner Type' as "Intune Managed Device Owner Type",attributes->>'Intune Management Agent' as "Intune Management Agent",attributes->>'Intune Azure AD Registered' as "Intune Azure AD Registered",attributes->>'Intune Compliance State' as "Intune Compliance State",attributes->>'Intune Device Name' as "Intune Device Name",attributes->>'Intune Azure AD Device Id' as "Intune Azure AD Device Id" FROM tips_endpoints WHERE attributes->>'Intune ID' = split_part(regexp_replace('%{Certificate:Subject-AltName-URI}','^.*DeviceId:',''),',',1)