- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Clever! That seems to work to generate statistics. but I'm now failing to be able to chart that by day.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I changed stats to eventstats and then I could send to| timechart span=1d max(device_count) by account_count
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Great! Smart fix.
