Browse Live Discover and Response Queries by Category
Approved

This query is one you are recommended to read the FULL context of the post to use. It is not simply a copy-paste query, like others in the forum.

Follow-up Windows updates patch (Data Lake)

Hi,

As many of you, I would like to list all machines that are out of date concerning "windows updates patch" (KBxxxxx), I know it is a tricky one after reading lots of idea submissions in this community ;-)

That's why my 1st goal would be to get a list from the data lake of all endpoints that were last patched with [installed_on] older than (today - XX days) with XX as a variable

I started with following standard query, but indeed "installed_on" is a string field "mm/dd/yyyy", that's why the "order by" doesn't work as expected...

SELECT
meta_hostname AS ep_name,
hotfix_id,
caption,
description,
installed_by,
installed_on
FROM xdr_data
WHERE query_name = 'windows_updates_patch'
ORDER BY installed_on DESC

Looking forward to reading your answers/feebacks/inputs, whatever can .

Thanks for your help!

Fabrice B
Parents

  • Hello Fabrice,
    Adding a variable would be a little tricky due to format constraints in the table schema.

    Meanwhile, you can use the ORDER BY statement. I shall post if I get the variable working correctly.

    SELECT 
    meta_hostname AS ep_name,
    hotfix_id,
    caption,
    description,
    installed_by,
    installed_on
FROM xdr_data
WHERE query_name = 'windows_updates_patch'
ORDER BY date_parse(installed_on,'%m/%d/%Y') DESC


  • in reply to RaviSoni

    I was able to manage the date variable working correctly.

    Below is the query exactly as per your requirement. 

    -- DEFINE VARIABLE $$no.of.days$$      STRING

WITH xdr_win_upd_pat AS (SELECT 
    meta_hostname AS ep_name,
    hotfix_id,
    caption,
    description,
    installed_by,
    cast(date_parse(installed_on,'%m/%d/%Y') as date) AS installed_on
FROM xdr_data
WHERE query_name = 'windows_updates_patch'
)
SELECT 
   * 
FROM xdr_win_upd_pat 
WHERE installed_on < current_date - interval '$$no.of.days$$' day
ORDER BY installed_on DESC

  • in reply to Fabrice B

    That's great! Congratulations on the first successful query! Thumbsup tone1

Comment Children
No Data
Unfiltered HTML