Approved

Search for Windows systems missing a specific patch

/*  Requires variable type string: kbnum */

/* using trino function to_unixtime() searching systems with ingestion timestamp within 30 days, 30 is hard coded into time filter */

select DISTINCT meta_hostname
from
xdr_data
where
meta_os_platform = 'windows'
AND
meta_hostname NOT IN (select meta_hostname from xdr_data where query_name = 'windows_updates_patch' AND meta_os_platform = 'windows' AND hotfix_id = '$$kbnum$$')
AND
to_unixtime(ingestion_timestamp) > to_unixtime(date_add('day', - 30, now()))