Getting Data In

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

jip31
Motivator

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

Labels (1)
Tags (1)
0 Karma
1 Solution

PickleRick
Ultra Champion

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

View solution in original post

0 Karma

PickleRick
Ultra Champion

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

0 Karma

jip31
Motivator

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]
0 Karma

johnhua
Builder

This could work:

 

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

jip31
Motivator

no it doesnt works

0 Karma

gcusello
Legend

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

 

0 Karma

jip31
Motivator

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

0 Karma

gcusello
Legend

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

0 Karma

jip31
Motivator

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
0 Karma

gcusello
Legend

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

0 Karma

gcusello
Legend

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

0 Karma

jip31
Motivator

it doesnt works

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

0 Karma

jip31
Motivator

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

0 Karma

gcusello
Legend

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

0 Karma

johnhua
Builder

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="-"

 

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...