- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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).
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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).
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This could work:
<base search>
| append [| inputlookup site.csv | eval keep=1]
| dedup site
| where keep=1
| table site
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
no it doesnt works
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
it doesnt works
I have site with "Present" status even if they dont exist in my main search
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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="-"
