help to display the difference between a search results and a lookup results

[jip31]

hi

I use a basic search which returns results by site

| stats count(x) as x, count(y) as y by site

 In a lookup I have also a site list 

| inputlookup site.csv

Now I would like to display in a table panel the site that exists in the lookup but not in the search

Is it possible to do this?

Thanks

[PickleRick · Accepted solution]

I think you're overcomplicating things and btw, the problem is somehow weirdly specified.

If you want the results which are not in the search, why would you count the stats?

If you just want the sites which are in the lookup but not in your events, do

<your search>
| stats values(site) as site
| mvexpand site
| eval siteid=1
| append 
  [ | inputlookup site.csv
    | fields site
    | eval siteid=2 ]
| stats sum(siteid) by site
| where siteid=2

This way you only get those sites which are in the lookup (and thus have combined siteid=2) but not those that are only in your index (siteid=1) or are in both of those places (siteid=3).

[jip31]

Is anybody can help please?

I tried also with set diff but it doesnt works

| set diff 
    [| search index=toto ((sourcetype=tutu hang > 0) OR ( sourcetype=titi crash=*unlo*) OR (sourcetype="tete web_as > 7000)) 
| eval sante=if((hang>5) AND (crash>2), "Etat dégradé","Etat acceptable") 
    | stats count(hang_process_name) as hang, count(crash_process_name) as crash by site 
    | table site]
   [ | search inputlookup site.csv
    | table site]

[johnhuang]

This could work:

<base search>
| append [| inputlookup site.csv | eval keep=1]
| dedup site
| where keep=1
| table site

[jip31]

no it doesnt works

[gcusello]

Hi @jip31,

you can filter your results after the stats command or (better) in the main search, something like this:

index=your_index [ | inputlookup site.csv | fields site ]
| stats count(x) as x count(y) as y by site

Put attention that the fiield name is the same in the main search and in the lookup (field name is case sensitive).

Ciao.

Giuseppe

[jip31]

sorry I am not explaining correctly

what I want to display in my table panel it's the site which exists in my lookup and which dont exits in my main search

[gcusello]

Hi @jip31,

ok, please try something like this:

index=your_index 
| stats count(x) as x count(y) as y by site
| append [ | inputlookup site.csv | eval status="Present" | fields site status ]
| stats values(x) AS x values(y) AS y values(Status) AS Status BY site
| eval Status=if(isnull(Status),"Not present",Status)

Ciao.

Giuseppe

[jip31]

here is the search I run

index=toto ((sourcetype=tutu hang > 0) OR ( sourcetype=titi crash=*unlo*) 
    OR (sourcetype="tete web_as > 7000)) 
| eval sante=if((hang>5) AND (crash>2), "Etat de santé dégradé","Etat de santé acceptable") 
| stats count(hang_process_name) as hang, count(crash_process_name) as crash by site 
| append 
    [| inputlookup site.csv 
    | eval status="Present" 
    | fields site status ] 
| stats values(hang) as hang values(crash) AS crash values(Status) AS Status BY site 
| eval Status=if(isnull(Status),"Not present",Status

[gcusello]

Hi @jip31,

please try my second hint:

index=toto ((sourcetype=tutu hang > 0) OR ( sourcetype=titi crash=*unlo*) 
    OR (sourcetype="tete web_as > 7000)) 
| eval sante=if((hang>5) AND (crash>2), "Etat de santé dégradé","Etat de santé acceptable") 
| stats count(hang_process_name) as hang count(crash_process_name) as crash count by site 
| append 
    [| inputlookup site.csv 
    | eval count=0 
    | fields site count ] 
| stats values(hang) as hang values(crash) AS crash values(Status) AS Status sum(count) AS total BY site 
| eval Status=if(total=0,"Not present","Present")
| table site hang crash Status

Ciao.

Giuseppe

[gcusello]

Hi @jip31,

please try this:

index=your_index 
| stats count(x) as x count(y) as y count by site
| append [ | inputlookup site.csv | eval count=0 | fields site count ]
| stats values(x) AS x values(y) AS y values(Status) AS Status sum(count) AS total BY site
| eval Status=if(total=0,"Not present","Present")
| table site x y Status

Ciao.

Giuseppe

[jip31]

it doesnt works

I have site with "Present" status even if they dont exist in my main search

[jip31]

there is something wrong because some site have a "Not present" status even if the exists in the main search

and all the site are in "Present" status...

[gcusello]

Hi @jip31,

strange! I used this solution in many similar use cases:

index=your_index 
| stats count(x) as x count(y) as y count by site
| append [ | inputlookup site.csv | eval count=0 | fields site count ]
| stats values(x) AS x values(y) AS y sum(count) AS total BY site
| eval Status=if(total=0,"Not present","Present")
| table site x y Status

Ciao.

Giuseppe

[johnhuang]

Try these 2. Could be format issues (case, padding, trailing characters) between your lookup site vs your main search. So Iet's try to sanitize and normalize the site field and see if it helps.

| stats count(x) as x, count(y) as y by site
| append [| inputlookup site.csv | eval keep=1 | fields site keep]
| eval site=UPPER(TRIM(site))
| dedup site
| where keep=1
| table site

| stats count(x) AS x count(y) AS y BY site
| eval search_data="Y"
| append [| inputlookup site.csv | eval lookup_data="Y" | table site lookup_data]
| eval site=UPPER(TRIM(site))
| stats MAX(x) AS x MAX(y) AS y VALUES(search_data) AS search_data VALUES(lookup_data) AS lookup_data BY site
| fillnull value="-"