I Want to split the results by hour and server

[leandrot]

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!

[woodcock]

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

[leandrot]

Many thanks already resolved by using the 1st option!

[woodcock]

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!

[leandrot]

Already done 🙂 Thanks

[somesoni2]

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

[leandrot]

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 ?

[somesoni2]

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

[leandrot]

Worked like a charm!
Many thanks!

[leandrot]

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 😞

[sbbadri]

try this

index=xxxx sourcetype=xxxxxx | timechart span=1h count by DC