Splunk Search

Join two searches that have no fields in common and take the sum of a field from one search + field from the other search

mbrazington
Engager

Goal: Build a dashboard that reports on the overall status of a product based on the status of the various services that supports that product. In the example below, the FileMoving service and the Filewatcher service mean that the Ingestion of the product is Up. The search is set up uniquely because I want the count to be "0" if the criteria for the search is not met.

Each search works perfectly independent of eachother, but when I attempt to append the one to the other and then sum the values for "status_fcs" and "status_filewatcher", it returns nothing. Is there a way to join two searches and sum values from different fields?

|stats count | eval host="hostname1" | append [search sourcetype=FileMoving] | where host="hostname1" | stats sum(eval(if(isnull(_time),0,1))) as count_fcs by host | eval status_fcs=if(count_fcs>0,1,0)
|append
[|stats count | eval host="hostname2" | append [search sourcetype=PhLogs "Scanning directory for new files"] | where host="hostname2" | stats sum(eval(if(isnull(_time),0,1))) as count_filewatcher by host | eval status_filewatcher=if(count_filewatcher>0,1,0)]

|eval count=statusfilewatcher+status_fcs | table count

Tags (3)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

tl;dr -

Really, all you are trying to do is count whether there is at least one of each type of record, so this is a far more efficient way to go...

(sourcetype=FileMoving host="hostname1")
OR (sourcetype=PhLogs "Scanning directory for new files" host="hostname2")
| fields sourcetype
| dedup sourcetype 
| stats count
| stats sum(status) as count

Now, for the home audience, here's a step-by-step simplification of your query...

You are not using host for anything after the record selection, so this...

| stats count 
| eval host="hostname1" 
| append [ search sourcetype=FileMoving] 
| where host="hostname1" 
| stats sum(eval(if(isnull(_time),0,1))) as count_fcs by host 
| eval status_fcs=if(count_fcs>0,1,0)

...is equivalent to this...

sourcetype=FileMoving host="hostname1"
| stats count as count_fcs 
| eval status_fcs=if(count_fcs>0,1,0)

...and this...

| stats count | eval host="hostname2" 
| append [search sourcetype=PhLogs "Scanning directory for new files"] 
| where host="hostname2" 
| stats sum(eval(if(isnull(_time),0,1))) as count_filewatcher by host 
| eval status_filewatcher=if(count_filewatcher>0,1,0)

... is equivalent to this...

sourcetype=PhLogs "Scanning directory for new files" host="hostname2"
| stats count as count_filewatcher
| eval status_filewatcher=if(count_filewatcher>0,1,0)

... but you don't need the individual statuses, just the sum of them, so you could do this...

sourcetype=FileMoving host="hostname1"
| stats count as count_fcs 
| eval status=if(count_fcs>0,1,0)
| append [ search 
    sourcetype=PhLogs "Scanning directory for new files" host="hostname2"
    | stats count as count_filewatcher
    | eval status =if(count_filewatcher>0,1,0)
]
| stats sum(status) as count

...and, reviewing the logical results of that search, all you are trying to do is count whether there is at least one of each type of record, so this is a far more efficient way to go...

(sourcetype=FileMoving host="hostname1")
OR (sourcetype=PhLogs "Scanning directory for new files" host="hostname2")
| fields sourcetype
| dedup sourcetype 
| stats count
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...