Use a count from one index to help the calculation in another

[mtmoore]

I have been racking my brains over this for most of the day so i'm hoping someone will put me out of my misery!

I want to calculate the average amount of messages per user per day. The number of users is contained in one index, the amount of messages per day in another.

How can I use the count of users to divide into the number of messages per day to get average per user?

This is what I have so far

One solution (doesn't work)

index="msexchange" eventtype=storedriver-mail | bucket _time span=1d | stats dc(message_id) as test by _time | append[search index="msexchange" sourcetype="msexchange:2010:mailbox-usage"  | dedup UserPrincipalName | eventstats count as test2] |eval test3 = test/test2

Another solution (as soon as you put by _time in there after stats dc(message_id) it complains):

index="msexchange" sourcetype="msexchange:2010:mailbox-usage"  | dedup UserPrincipalName | eventstats count as Total | eval cur=[search index="msexchange" eventtype=storedriver-mail | bucket _time span=1d | stats dc(message_id) as cur| rename cur as query] |eval finalanswer = cur / Total |table finalanswer

Help! 🙂

[somesoni2]

And your index/sourcetype containing messages don't have reference to user (UserPrincipalName)? (in the same event)

[kristian_kolb]

Would something like this work?

index=msexchange eventtype=storedriver-mail OR sourcetype=msexchange:2010:mailbox-usage
| timechart span=1d dc(UserPrincipalName) as users dc(message_id) as msgs 
| eval ratio = msgs/users

/K

[mtmoore]

Awesome Kristian, did the job perfectly, thankyou!