Query missing on each host

[CarolinaHB]

Good Morning, 

I'm working in a query to see which application is missing on each host. 

Can you help me, please?

For example

Host     application

            Guardicore

 Host1 cortex

              Tenable

               Trend Micro

Host2 cortex

             Tenable

I need, it to show me what is missing

• In its example Guardicore y tenable

Regardes

[gcusello]

Hi @CarolinaHB,

there are two solutions that depend on the  location of the monitoring perimeter:

if you have a lookup containing the list of each app that should be present in each host (called e.g. app_perimeter.csv and containing at least two fields: host and application), you could run something like this:

<your_search>
| stats count BY host application
| append [ | inputlookup app_perimeter.csv | eval count=0 | fields host application count ]
| stats sum(count) AS total BY host application
| eval status=if(total=0,"Missing","Present")
| table host application status

If instead you don't have this lookup and you want to compare results e.g. of the last 24 hours with the results of the last 30 days, you could run something like this:

<your_search> earliest=30d latest=now
| eval period=if(_time>now()-86400,"Last day","Previous days")
| stats dc(period) AS period_count values(period) AS period BY host application
| eval status=if(period_count=1 AND period="Previous days","Missing","Present")
| table host application status

Ciao.

Giuseppe