Compliance query to report on uptime, last date of a Windows OS patch installation and any pending restart requests

-- Recomended use: export results, filter devices reporting rsults in "Reboot pending evidence" column,
-- then sort by "Uptime, decial days)", descending. Reboot the longest running machines first.
-- Then sort by "Latest MS patch install data", ascending and check for updates on the machines
-- which have not had an update applied for the longest time.
-- 
-- Thanks to Sophos Rapid Response team and Mike Graves for their input with this query

WITH 
daysup AS (SELECT CAST((1.0*total_seconds) / (86400*1.0) as DECIMAL) as "Uptime (decimal days)" FROM uptime),
lastpatch AS (select installed_on as "Latest MS patch install date" FROM patches 
ORDER BY installed_on DESC
LIMIT 1),
reboot AS (
SELECT 
	CAST(group_concat(path, CHAR(10)) AS TEXT) as "Reboot pending evidence"
FROM
	registry
WHERE
	(path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations')
	OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations2')
	OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Updates\UpdateExeVolatile' AND data != 0)
	OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired')
	OR (path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Services\Pending\%-%')
	OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\PostRebootReporting')
	OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\RunOnce\DVDRebootSignal')
	OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending')
	OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootInProgress')
	OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\PackagesPending')
	OR (path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ServerManager\CurrentRebootAttempts')
	OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Netlogon\JoinDomain')
	OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Netlogon\AvoidSpnSet')
	OR (
		path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ComputerName\ActiveComputerName\ComputerName'
		AND data != (SELECT data FROM registry WHERE path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\ComputerName')
	)
	OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Sophos Endpoint Defense\sophosPFRs')
	OR (path = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Sophos Endpoint Defense\cleanPFRs'))
SELECT * FROM daysup JOIN lastpatch JOIN reboot;