topic Combining Multiple series in Splunk Search

Combining Multiple series

philre — Mon, 28 Sep 2020 10:15:54 GMT

Hi,

I'm pretty new to Splunk reporting, so maybe this is an easy one 😉

I've build up a query joining 3 data series like this:

cluster=cluster_1 AND relay=relayhost1 eventtype=relay | stats count as "Relay Count" | join [search cluster=cluster_1 reject="554 5.7.1 Service unavailable" | stats count as "Reject Count" | join [search cluster=cluster_1 AND (categorization="spam-confirmed" OR reject=551) | stats count as "Spam" ]

This is working as intended, I get one my 3 results. Now I want to take this further, getting the results grouped by time. I got this far:

cluster=cluster_1 AND relay=relayhost1 eventtype=relay | bucket _time span=5m | stats count as "Relay Count" by _time | join [search cluster=cluster_1 reject="554 5.7.1 Service unavailable" | bucket _time span=5n | stats count as "Reject Count" by _time] | join [search cluster=cluster_1 AND (categorization="spam-confirmed" OR reject=551) | bucket _time span=5m | stats count as "Spam" by _time]

It seems to be working for the Relay Count column, but not for the other 2, they always have the same count over all _time rows, e.g.:

_time Relay Count Reject Count Spam

1 1/2/12 2:35:00.000 PM 978 832 33
2 1/2/12 2:40:00.000 PM 1336 832 33
3 1/2/12 2:45:00.000 PM 1313 832 33
4 1/2/12 2:50:00.000 PM 490 832 33

Am I doing something terribly wrong, or is there a way to get this results?

Thanks in advance!

Philipp

Re: Combining Multiple series

eelisio2 — Mon, 02 Jan 2012 14:50:52 GMT

This should give you the results you want:

cluster=cluster_1 | bucket _time span=5m 
| stats count(eval(relay=relayhost1 AND eventtype=relay)) as "Relay Count",
count(eval(reject="554 5.7.1 Service unavailable")) as "Reject Count",
count(eval(categorization="spam-confirmed" OR reject=551)) as "Spam" by _time

Re: Combining Multiple series

philre — Mon, 02 Jan 2012 15:07:15 GMT

Hi, that works great, I need to use the bucket before the searches, I see!
I already tryed working with eval, but never got it right i guess 😉

I also was able to speed it up a bit by adding the filters used in the eval to the search:

cluster=cluster_1 AND (relay="relayhost1" OR reject="554 5.7.1 Service unavailable" OR reject=551)  | bucket _time span=5m 
| stats count(eval(relay="relayhost1")) as "Relay Count",
count(eval(reject="554 5.7.1 Service unavailable")) as "Reject Count",
count(eval(categorization="spam-confirmed" OR reject=551)) as "Spam" by _time

Thank you!