How to create a search that shows a daily message count and the average for each direction?

[weetabixsplunk]

I'm trying to create a search that shows a daily message count (both inbound and outobound) and the average for each direction. Although it doesn't give me any errors, when the table gets created, the results show as zero (I know this is inaccurate as I pulled a message trace from o365 to confirm).

index=vs_email sampledesign@victoriasecret.com
| eval direction=case(RecipientAddress="sampledesign@victoriasecret.com", "inbound", RecipientAddress!="sampledesign@victoriasecret.com", "outbound")
| dedup MessageId
| bin _time span=1d
| eventstats count(direction="inbound") as inbound_count
| eventstats count(direction="outbound") as outbound_count
| dedup _time
| eventstats avg(inbound_count) as average_inbound_count
| eventstats avg(outbound_count) as average_outbound_count
| table inbound_count outbound_count average_inbound_count average_outbound_count

All of the results are showing as zero. Any help would be much appreciated.

Thanks!

[weetabixsplunk]

Thank you!! ❤️

[bowesmana]

You do not need to make your search so complicated - using eventstats is not often needed and is a slow command to run. You can do it with stats, which will be far more efficient than 4 eventstats calls.

index=vs_email sampledesign@victoriasecret.com
| eval direction=case(RecipientAddress="sampledesign@victoriasecret.com", "inbound", RecipientAddress!="sampledesign@victoriasecret.com", "outbound")
| dedup MessageId
| bin _time span=1d
| stats count(eval(direction="inbound")) as inbound_count count(eval(direction="outbound")) as outbound_count by _time
| eventstats avg(inbound_count) as average_inbound_count avg(outbound_count) as average_outbound_count
| table _time inbound_count outbound_count average_inbound_count average_outbound_count

so

• bin _time - will allow you to group by day
• stats count... - will count the inbound and outbound messages by day (by _time at end)
• eventstats will then calculate the average of counts for the days

[richgalloway · Accepted solution]

The stats, eventstats, and streamstats expect field names rather than expressions.   To use an expression, embed the eval function.

index=vs_email sampledesign@victoriasecret.com
| eval direction=case(RecipientAddress="sampledesign@victoriasecret.com", "inbound", RecipientAddress!="sampledesign@victoriasecret.com", "outbound")
| dedup MessageId
| bin _time span=1d
| eventstats count(eval(direction="inbound")) as inbound_count
| eventstats count(eval(direction="outbound")) as outbound_count
| dedup _time
| eventstats avg(inbound_count) as average_inbound_count
| eventstats avg(outbound_count) as average_outbound_count
| table inbound_count outbound_count average_inbound_count average_outbound_count