1. Queries
   1. Get a snapshot of which Entra users are logged in to hosts
   2. Analyse specific processes running to understand how much RAM/CPU they are consuming
   3. Get current IP from hosts
2. Policies
   1. Check if device is still enrolled in Intune
   2. Check presence of applications
      1. Mac
      2. Windows
   3. Detect if an app is running

Queries

Get a snapshot of which Entra users are logged in to hosts

Compatibility: Windows

SELECT
	upn,
	user,
	logon_domain,
	dns_domain_name,
	authentication_package
FROM
	logon_sessions
WHERE
	upn LIKE "< your email domain here >"
GROUP BY
	upn

Analyse specific processes running to understand how much RAM/CPU they are consuming

Compatibility: Mac, Windows, Linux

In this example, we are looking at processes where the name contains RTProtection (short for “real-time protection”, aka Antivirus/EDR-type softwares). We get their pid (process Id), name , system_time (which we divide by 86400 to convert to days), start_time (which we convert to dd/mm/yyyy format) and RAM consumed (total_size which we divide by 1048576 to have it in megabytes).

SELECT
	pid,
	name,
	ROUND((system_time / 86400), 2) AS system_time_days,
	strftime('%d/%m/%Y', datetime(start_time, 'unixepoch')) AS start_time_date,
	ROUND((total_size / 1048576), 2) AS total_size_mb
FROM
	processes
WHERE
	name LIKE '%RTProtection%';

Get current IP from hosts

Compatibility: Mac, Windows, Linux

SELECT
	JSON_EXTRACT(result, '$.ip') AS ip,
	JSON_EXTRACT(result, '$.city') AS city,
	JSON_EXTRACT(result, '$.region') AS region,
	JSON_EXTRACT(result, '$.country') AS country
FROM
	curl
WHERE
	url = 'http://ipapi.co/json';

Policies

Check if device is still enrolled in Intune

Compatibility: Windows

This policy will fail if the device is still enrolled in Intune.

SELECT 1
FROM
	registry
WHERE
	path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Enrollments\%%'
	AND name =  "DiscoveryServiceFullURL"
	AND data NOT IN (
"https://enrollment.manage.microsoft.com/enrollmentserver/discovery.svc",
"https://discovery.dm.microsoft.com/EnrollmentConfiguration?api-version=1.0"
)

Check presence of applications

(for example to install an application if it is missing)

Mac

SELECT 1 FROM apps WHERE bundle_identifier = 'your_bundle_identifier';

Windows

SELECT 1 FROM programs WHERE name LIKE '%application_name%';

Detect if an app is running

SELECT
	1
FROM
	processes
WHERE
	name LIKE '% process_name %';