topic Re: help to compare a search results with a lookup results in Getting Data In

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

jip31 — Thu, 27 Jan 2022 07:26:56 GMT

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

Re: help to compare a search results with a lookup results

johnhuang — Thu, 27 Jan 2022 06:20:09 GMT

This could work:

Re: help to compare a search results with a lookup results

jip31 — Thu, 27 Jan 2022 07:02:21 GMT

no it doesnt works

Re: help to compare a search results with a lookup results

gcusello — Thu, 27 Jan 2022 07:10:27 GMT

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

Re: help to compare a search results with a lookup results

jip31 — Thu, 27 Jan 2022 07:22:00 GMT

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

Re: help to compare a search results with a lookup results

gcusello — Thu, 27 Jan 2022 07:26:27 GMT

Hi @jip31,

ok, please try something like this:

Ciao.

Giuseppe

Re: help to compare a search results with a lookup results

jip31 — Thu, 27 Jan 2022 08:48:00 GMT

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...

Re: help to compare a search results with a lookup results

gcusello — Thu, 27 Jan 2022 07:48:26 GMT

Hi @jip31,

please try this:

Ciao.

Giuseppe

Re: help to compare a search results with a lookup results

jip31 — Thu, 27 Jan 2022 07:50:10 GMT

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

Re: help to compare a search results with a lookup results

gcusello — Thu, 27 Jan 2022 08:00:00 GMT

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

Re: help to compare a search results with a lookup results

jip31 — Thu, 27 Jan 2022 08:45:28 GMT

it doesnt works

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

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

jip31 — Thu, 27 Jan 2022 11:47:47 GMT

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]

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

PickleRick — Thu, 27 Jan 2022 12:16:57 GMT

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).

Re: help to compare a search results with a lookup results

gcusello — Thu, 27 Jan 2022 13:20:39 GMT

Hi @jip31,

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

Ciao.

Giuseppe

Re: help to compare a search results with a lookup results

johnhuang — Thu, 27 Jan 2022 14:12:54 GMT

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.