Splunk Search

Combining Multiple series

philre
Engager

Hi,

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

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

Tags (2)
0 Karma
1 Solution

eelisio2
Path Finder

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

View solution in original post

eelisio2
Path Finder

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

philre
Engager

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 :winking_face:

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!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...