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!

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


  • 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

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

  • Thank you @RaviSoni for your help and input.

    Nevertheless your query doesn't work as expected, indeed running your query I still faced issue with the string field "installed_on" because of its format "mm/dd/yyyy", indeed sometimes you get 1/3/2020 and sometimes you can get 11/15/2020, meaning that %m isn't always two chars, same for %d...

    It was tricky but I manage to find out a solution and build my query to get the expected result, yes I'm proud because it's my real first query ;-)

    -- DEFINE VARIABLE $$no.of.days$$      STRING
    
    WITH xdr_win_upd_pat AS (
    SELECT 
        meta_hostname AS ep_name,
        meta_endpoint_type,
        meta_ip_address,
        hotfix_id,
        caption,
        description,
        installed_by,
        /*
        current_date today,
        rtrim(substr(installed_on, 1,2),'/') mois,
        rtrim(substr(installed_on, 3,2),'/') jour,
        substr(installed_on, length(installed_on)-3,4) annee,
        concat(substr(installed_on, length(installed_on)-3,4),'-',rtrim(substr(installed_on, 1,2),'/'),'-',rtrim(substr(installed_on, 3,2),'/')) date_str,
        */
        CASE length(rtrim(substr(installed_on, 1,2),'/')) 
          WHEN 1 THEN concat('0',rtrim(substr(installed_on, 1,2),'/'))
          ELSE rtrim(substr(installed_on, 1,2),'/')
       END mois,
       CASE length(ltrim(rtrim(substr(installed_on, 3,2),'/'),'/')) 
          WHEN 1 THEN concat('0',ltrim(rtrim(substr(installed_on, 3,2),'/'),'/'))
          ELSE rtrim(substr(installed_on, 3,2),'/')
       END jour,
       substr(installed_on, length(installed_on)-3,4) annee
    FROM xdr_data
    WHERE query_name = 'windows_updates_patch'
    ),
    xdr_win_upd_pat2 AS (
    
    SELECT 
       ep_name,
       meta_ip_address,
       description,
       /*caption,*/
       hotfix_id,
       meta_endpoint_type,
       /*length(concat(jour,'-',mois,'-',annee)) installed_on_length
       concat(jour,'-',mois,'-',annee) installed_on*/
       /*cast(date_parse('01-01-1900','%d-%m-%Y') as date) installed_on*/
       
       CASE length(concat(jour,'-',mois,'-',annee))
          WHEN 10 THEN cast(date_parse(concat(jour,'-',mois,'-',annee),'%d-%m-%Y') as date)
          ELSE cast(date_parse('01-01-1900','%d-%m-%Y') as date)
       END installed_on
       /*current_date - interval '$$no.of.days$$' day check_date
       cast(date_parse(concat(jour,'-',mois,'-',annee),'%d-%m-%Y') as date) AS installed_on*/
    FROM xdr_win_upd_pat 
    /*WHERE 
       installed_on < current_date - interval '$$no.of.days$$' day
    ORDER BY installed_on DESC*/
    
    ),
    
    xdr_win_upd_pat3 AS (
    SELECT 
       ep_name,
       /*meta_ip_address,
       description,
       caption,
       hotfix_id,
       meta_endpoint_type,*/
       MAX(installed_on) AS max_date
    FROM xdr_win_upd_pat2
    GROUP BY ep_name
    )
    SELECT 
       ep_name,
       current_date - interval '$$no.of.days$$' day checkdate,
       max_date
    FROM xdr_win_upd_pat3 
    WHERE 
       max_date < current_date - interval '$$no.of.days$$' day
    ORDER BY max_date DESC

Comment
  • Thank you @RaviSoni for your help and input.

    Nevertheless your query doesn't work as expected, indeed running your query I still faced issue with the string field "installed_on" because of its format "mm/dd/yyyy", indeed sometimes you get 1/3/2020 and sometimes you can get 11/15/2020, meaning that %m isn't always two chars, same for %d...

    It was tricky but I manage to find out a solution and build my query to get the expected result, yes I'm proud because it's my real first query ;-)

    -- DEFINE VARIABLE $$no.of.days$$      STRING
    
    WITH xdr_win_upd_pat AS (
    SELECT 
        meta_hostname AS ep_name,
        meta_endpoint_type,
        meta_ip_address,
        hotfix_id,
        caption,
        description,
        installed_by,
        /*
        current_date today,
        rtrim(substr(installed_on, 1,2),'/') mois,
        rtrim(substr(installed_on, 3,2),'/') jour,
        substr(installed_on, length(installed_on)-3,4) annee,
        concat(substr(installed_on, length(installed_on)-3,4),'-',rtrim(substr(installed_on, 1,2),'/'),'-',rtrim(substr(installed_on, 3,2),'/')) date_str,
        */
        CASE length(rtrim(substr(installed_on, 1,2),'/')) 
          WHEN 1 THEN concat('0',rtrim(substr(installed_on, 1,2),'/'))
          ELSE rtrim(substr(installed_on, 1,2),'/')
       END mois,
       CASE length(ltrim(rtrim(substr(installed_on, 3,2),'/'),'/')) 
          WHEN 1 THEN concat('0',ltrim(rtrim(substr(installed_on, 3,2),'/'),'/'))
          ELSE rtrim(substr(installed_on, 3,2),'/')
       END jour,
       substr(installed_on, length(installed_on)-3,4) annee
    FROM xdr_data
    WHERE query_name = 'windows_updates_patch'
    ),
    xdr_win_upd_pat2 AS (
    
    SELECT 
       ep_name,
       meta_ip_address,
       description,
       /*caption,*/
       hotfix_id,
       meta_endpoint_type,
       /*length(concat(jour,'-',mois,'-',annee)) installed_on_length
       concat(jour,'-',mois,'-',annee) installed_on*/
       /*cast(date_parse('01-01-1900','%d-%m-%Y') as date) installed_on*/
       
       CASE length(concat(jour,'-',mois,'-',annee))
          WHEN 10 THEN cast(date_parse(concat(jour,'-',mois,'-',annee),'%d-%m-%Y') as date)
          ELSE cast(date_parse('01-01-1900','%d-%m-%Y') as date)
       END installed_on
       /*current_date - interval '$$no.of.days$$' day check_date
       cast(date_parse(concat(jour,'-',mois,'-',annee),'%d-%m-%Y') as date) AS installed_on*/
    FROM xdr_win_upd_pat 
    /*WHERE 
       installed_on < current_date - interval '$$no.of.days$$' day
    ORDER BY installed_on DESC*/
    
    ),
    
    xdr_win_upd_pat3 AS (
    SELECT 
       ep_name,
       /*meta_ip_address,
       description,
       caption,
       hotfix_id,
       meta_endpoint_type,*/
       MAX(installed_on) AS max_date
    FROM xdr_win_upd_pat2
    GROUP BY ep_name
    )
    SELECT 
       ep_name,
       current_date - interval '$$no.of.days$$' day checkdate,
       max_date
    FROM xdr_win_upd_pat3 
    WHERE 
       max_date < current_date - interval '$$no.of.days$$' day
    ORDER BY max_date DESC

Children