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