https://github.com/osquery/osquery logo
Powered by
Title
t

Tiernan

09/21/2022, 12:59 PM
Hello All, Do any of you have a query for checking if a Windows Machine is enrolled into MDM? I’m trying to build a one & I’m a bit stuck...
f

fritz

09/21/2022, 1:13 PM
@Tiernan which MDM do you use?
t

Tiernan

09/21/2022, 2:33 PM
@fritz Currently using Miradore
I do have a PS script that is able to run a check, look like this:
$EnrollmentStatus = Get-Item -Path HKLM:\SOFTWARE\Microsoft\Enrollments\* | Get-ItemProperty | Where-Object -FilterScript {$null -ne $_.UPN} if ($EnrollmentStatus.ProviderID -eq "MiradoreMDM") { Write-Host "The device is enrolled.” } else{ Write-Host "No Enrolment found," }
I was hoping to use the same registry entries to make a query but it hasn't worked so far
f

fritz

09/21/2022, 2:36 PM
Registry should be your path based on that powershell script
what problems are you having with creating a registry query?
If you paste your WIP query, I can take a look
t

Tiernan

09/21/2022, 2:38 PM
I'm starting with "Select * from registry where path='HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Enrollments\' " on a machine I now to be enrolled just to see what information is returned
But its not returning any info at all
f

fritz

09/21/2022, 2:49 PM
Enrollments is a subkey which is like a directory, you need to specify an exact key/path, or use a 
LIKE
to get multiple items under that path, eg. 
SELECT * 
FROM registry 
WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Enrollments\%%'
Likewise, registry will output data in EAV format so you will need to perform a pivot if you want it in columns
I just wrote up a rather untested example of this approach: 
WITH 
registry_raw AS (
    SELECT * FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Enrollments\%%'
),
enrollment_pivoted AS (
SELECT 
  MAX(CASE WHEN name = 'UPN' THEN data END) AS upn,
  MAX(CASE WHEN name = 'EnrollmentState' THEN data END) AS enrollment_state,
  MAX(CASE WHEN name = 'EnrollmentType' THEN data END) AS enrollment_type,
  MAX(CASE WHEN name = 'ProviderID' THEN data END) AS provider_id,
SPLIT(key,'\',4) AS parent
FROM registry_raw 
GROUP BY key
)
SELECT * FROM enrollment_pivoted WHERE upn NOT NULL;
t

Tiernan

09/23/2022, 12:17 PM
@fritz I managed to get this to work!! Thank you so much for the assist.
The query/policy that worked for me is:
SELECT 1 WHERE EXISTS (
SELECT *
FROM registry 
WHERE path LIKE "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Enrollments\%%\ProviderID"
AND data is "MiradoreMDM");
f

fritz

09/23/2022, 4:53 PM
👍 glad you were able to get what you needed!
3 Views
#generalJoin Slack
Powered by