Dashboards & Visualizations

Need create a query to compare two tables from different search and populate the difference

Mrig342
Contributor

Hi All,

I have got two queries to populate the host, region, tech stack & environment details. One query is a lookup table that has the list of total number of host. 

 

| inputlookup Master_List.csv | search Region="Asia" | search "Tech Stack"="Apple" | rename host as Total_Servers | table Total_Servers

 

which gives below table:

Total_Servers
Apple1
Apple2
Apple3
Apple4
Apple5
Apple6

The second query gives us the list of hosts that are currently populating in splunk.

 

... | rex field=_raw "(?ms)]\|(?P<host>\w+\-\w+)\|"
| rex field=_raw "(?ms)]\|(?P<host>\w+)\|"
| rex field=_raw "\]\,(?P<host>[^\,]+)\," 
| rex field=_raw "\]\|(?P<host>[^\|]+)\|"
| regex _raw!="^\d+(\.\d+){0,2}\w"
| regex _raw!="/apps/tibco/datastore"
| lookup Master_List.csv "host"
| search "Tech Stack"="Apple"
| search Region="Asia"
| rename host as "Reporting_Servers" | table "Reporting_Servers"

 

which gives below table:

Reporting_Servers
Apple1
Apple4
Apple5

Now I want to create a query to compare these two table and populate the servers that are missing from the total servers. So that output of the above tables after comparison should be like below:

Non_Reporting_Servers
Apple2
Apple3
Apple6

 

Please help me to create a query to achieve the expected output table. Your kind inputs are highly appreciated.

 

Thank You..!!!

Labels (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @Mrig342,

you have to run a simplesearch like the following:

... | rex field=_raw "(?ms)]\|(?P<host>\w+\-\w+)\|"
| rex field=_raw "(?ms)]\|(?P<host>\w+)\|"
| rex field=_raw "\]\,(?P<host>[^\,]+)\," 
| rex field=_raw "\]\|(?P<host>[^\|]+)\|"
| regex _raw!="^\d+(\.\d+){0,2}\w"
| regex _raw!="/apps/tibco/datastore"
| lookup Master_List.csv "host"
| search "Tech Stack"="Apple"
| search Region="Asia"
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup Master_List.csv WHERE Region="Asia" AND "Tech Stack"="Apple" | eval eval host=lower(host), count=0 | fields host ]
| stats sum(count) AS total BY host
| where total=0
| rename host as "Not_Reporting_Servers" 
| table "Not_Reporting_Servers"

Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @Mrig342,

you have to run a simplesearch like the following:

... | rex field=_raw "(?ms)]\|(?P<host>\w+\-\w+)\|"
| rex field=_raw "(?ms)]\|(?P<host>\w+)\|"
| rex field=_raw "\]\,(?P<host>[^\,]+)\," 
| rex field=_raw "\]\|(?P<host>[^\|]+)\|"
| regex _raw!="^\d+(\.\d+){0,2}\w"
| regex _raw!="/apps/tibco/datastore"
| lookup Master_List.csv "host"
| search "Tech Stack"="Apple"
| search Region="Asia"
| eval host=lower(host)
| stats count BY host
| append [ | inputlookup Master_List.csv WHERE Region="Asia" AND "Tech Stack"="Apple" | eval eval host=lower(host), count=0 | fields host ]
| stats sum(count) AS total BY host
| where total=0
| rename host as "Not_Reporting_Servers" 
| table "Not_Reporting_Servers"

Ciao.

Giuseppe

Mrig342
Contributor

Hi @gcusello,

Thank you very much for your inputs..!! The query worked perfect for me.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...