topic Need create a query to compare two tables from different search and populate the difference in Dashboards & Visualizations

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

Mrig342 — Sun, 24 Sep 2023 08:20:07 GMT

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.

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.

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

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

gcusello — Sun, 24 Sep 2023 08:27:45 GMT

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

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

Mrig342 — Sun, 24 Sep 2023 16:10:42 GMT

Hi @gcusello,

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