Tiernan
09/21/2022, 12:59 PMfritz
09/21/2022, 1:13 PMTiernan
09/21/2022, 2:33 PMfritz
09/21/2022, 2:36 PMTiernan
09/21/2022, 2:38 PMfritz
09/21/2022, 2:49 PMLIKE
to get multiple items under that path, eg.
SELECT *
FROM registry
WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Enrollments\%%'
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;
Tiernan
09/23/2022, 12:17 PMSELECT 1 WHERE EXISTS (
SELECT *
FROM registry
WHERE path LIKE "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Enrollments\%%\ProviderID"
AND data is "MiradoreMDM");
fritz
09/23/2022, 4:53 PM