How can I count account access to devices (counts) by day?

[pyamamoto]

I want to find/graph the count of (dc(X) as dc_X_count by Y) by day.

In other words, I have some events in a basic search with two id's X and Y. There are 1 or more X values per Y. The max number of X/Y is reasonable (like say < 50/day).

But what I want to know is how many of each number of X/Y's is happening per day. Example use case, X is an account id, Y is a device id. We can have multiple accounts logging into the same device. So I want to find the count of #ofaccounts/device each day (eg 100 devices have 1 account logging in, 50 devices have 2 accounts logging in, etc).

to get the initial pass of data it looks like

search for login events containing accountid(X), deviceid(Y) ... | timechart span=1d dc(accountid) as accounts_count by deviceid

but then I don't know how to get a count of those results by day.

[elliotproebstel · Accepted solution]

Not sure if this is the best approach, but I think it will work:
search for login events
| bin _time span=1d as day
| stats dc(accountid) as account_count BY day deviceid
| stats dc(deviceid) AS device_count BY day account_count

[pyamamoto]

Clever! That seems to work to generate statistics. but I'm now failing to be able to chart that by day.

[DalJeanis]

@pyamamoto - Changing both those stats to eventstats is a little scary to me, since I can't picture whether the results is mathematically accurate. I suspect that it is, but you are making the computer work a lot harder than it needs to, fully scanning the entire result set three times when each scan could be reducing the results for its successor.

All you really need to do to get the timechart to work is add this to the end of @elliotproebstels code...

| rename day as _time
| timechart span=1d max(device_count) by account_count

You could also use max(), min(), values(), avg(), last(), first() or whatever reasonable aggregate function you want, since there is only one answer per day, they all will get the same answer.

[pyamamoto]

Thank you for the feedback. I hadn't seen that mentioned before: rename day as _time to be able to use timechart. That helps a lot.

[pyamamoto]

I changed stats to eventstats and then I could send to| timechart span=1d max(device_count) by account_count

[elliotproebstel]

Great! Smart fix.