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

  • in reply to RaviSoni

    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
  • in reply to RaviSoni

    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
Unfiltered HTML