Hi, I have the following table:
_time usernameOK
_time usernameFail
example:
2017-09-28 00:10:00 usernameOK=robE
2017-09-28 01:10:20 usernameFail=jonasH
2017-09-28 02:20:23 usernameOK=timN
2017-09-28 02:20:35 usernameOK=robE
2017-09-28 02:30:46 usernameOK=robE
Basically I am trying to get the count of BOTH usernameOK and usernameFAIL, by time (bucketed 1h) by user, akin to a pivot table but my count command is coming back with an error ... Any ideas?
Thank you.
Hi robettinger,
try something like this:
index=your_index (usernameOK=* OR usernameFail=*)
| eval type=if(usernameOK=*,"OK","Fail")
| stats count by type
Bye.
Giuseppe
Hi Giuseppe, I am afraid the query won't solve my problem.
I would like to see a pivot-like table, grouping which users access at what time (this can be a time bucket of 1h) and what users fail at what time (also a time bucket). This needs to be grouped by user. In the example above, the following data should be provided:
range Users OK Users Fail
00:00-01:00 robE
01:00 - 02:00 jonasH
02:00 - 03:00 robE, timN
It can also be multi-value...
R
Hi robettinger,
try something like this
index=your_index (usernameOK=* OR usernameFail=*)
| eval type=if(usernameOK=*,"OK","Fail"), username=coalesce(usernameOK,usernameFail)
| stats count by type username
| bin count span=1
| chart values(username) AS username over count by type
| nomv usernameOK
| nomv usernameFail
Bye.
Giuseppe