Hi all,
We have data coming from 2 diferent servers and would like to get the count of users on each server by hour. so far I have not been able to do it, this is my search:
| bucket _time span=1h
| stats distinct_count(User) by _time date_hour DC
*DC = server in my case
Any help ?
Thanks!
You are EITHER overcomplicating your solution OR poorly (incorrectly) specifying what you need. Your ask is really this simple:
... | timechart span=1h dc(AGENT) BY DC
If you would like it a more traditional spreadsheet format then add this to the end, too:
| untable _time DC count
Many thanks already resolved by using the 1st option!
OK, then go back and select an appropriate answer and click Accept
and be sure to UpVote
all those other patient contributors who chimed in, too!
Already done 🙂 Thanks
Try like this. Should give you a column for each server and corresponding counts for each hour (the field _time will be buckets for each hour)
your base search
| timechart span=1h count by DC
I figured out some minutes ago.
What I do not understand is that, for a period of time,
lets say : 02/07/2017 11:00:00.000 to 02/07/2017 12:00:00.000
| dedup AGENT |stats count(AGENT) by DC
The result is 1460,
But by executing:
| dedup AGENT | timechart span=1h count(AGENT) by DC
for
(01/07/2017 12:31:09.000 to 03/07/2017 12:31:09.000
The users neither at 11 or 12 are not even close to 1460
Any idea why ?
The dedup will remove duplicate values, regardless of hour, so if there were common records for AGENT in different hours, only the latest record will be kept and thus different count.
For example if your data is like this
02/07/2017 11:00:00.000 DC=DC1 AGENT=agent1
02/07/2017 11:00:00.000 DC=DC1 AGENT=agent2
02/07/2017 11:00:00.000 DC=DC1 AGENT=agent3
02/07/2017 12:00:00.000 DC=DC1 AGENT=agent1
02/07/2017 12:00:00.000 DC=DC1 AGENT=agent2
The ..| timechart span=1h dc(AGENT) by DC
will give your this
_time DC1
02/07/2017 11:00:00.000 3
02/07/2017 12:00:00.000 2
After dedup AGENT, you'll be left with this
02/07/2017 11:00:00.000 DC=DC1 AGENT=agent3
02/07/2017 12:00:00.000 DC=DC1 AGENT=agent1
02/07/2017 12:00:00.000 DC=DC1 AGENT=agent2
The ..| dedup AGENT| timechart span=1h dc(AGENT) by DC
will give your this
_time DC1
02/07/2017 11:00:00.000 1
02/07/2017 12:00:00.000 2
To get proper unique agent count for each hour, you would need to include hour into dedup.
...| bucket span=1h _time | dedup _time AGENT | timechart span=1h count(AGENT) by DC
OR better.
..| timechart span=1h dc(AGENT) by DC
Worked like a charm!
Many thanks!
Thanks for the reply, I tried | bucket _time span=1h
| stats distinct_count(AGENT) by _time date_hour DC | timechart span=1h count by DC
No results 😞
try this
index=xxxx sourcetype=xxxxxx | timechart span=1h count by DC