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

[Mrig342]

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

[gcusello · Accepted solution]

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]

Hi @gcusello,

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