Access Granted/Denied query

[robettinger]

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.

[gcusello]

Hi robettinger,
try something like this:

index=your_index (usernameOK=* OR usernameFail=*)
| eval type=if(usernameOK=*,"OK","Fail")
| stats count by type

Bye.
Giuseppe

[robettinger]

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

[gcusello]

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