Hello All,
Do any of you have a query for checkin...
# generalt
Tiernan
09/21/2022, 12:59 PMHello 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
Tiernan
09/21/2022, 2:34 PMI do have a PS script that is able to run a check, look like this:
Tiernan
09/21/2022, 2:34 PM$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,"
}
Tiernan
09/21/2022, 2:35 PMI 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 PMRegistry should be your path based on that powershell script
fritz
09/21/2022, 2:36 PMwhat problems are you having with creating a registry query?
fritz
09/21/2022, 2:36 PMIf you paste your WIP query, I can take a look
t
Tiernan
09/21/2022, 2:38 PMI'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
Tiernan
09/21/2022, 2:38 PMBut its not returning any info at all
f
fritz
09/21/2022, 2:49 PMEnrollments is a subkey which is like a directory, you need to specify an exact key/path, or use a
LIKE Copy code
SELECT *
FROM registry
WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Enrollments\%%'fritz
09/21/2022, 2:50 PMLikewise, registry will output data in EAV format so you will need to perform a pivot if you want it in columns
fritz
09/21/2022, 2:58 PMI just wrote up a rather untested example of this approach:
Copy code
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.
Tiernan
09/23/2022, 12:17 PMThe query/policy that worked for me is:
Tiernan
09/23/2022, 12:17 PM Copy code
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