Only Failed Users without Any other Successful attempts

[moayadalghamdi]

Hello Splunkers

i want to print events for only the users who has failed login attempts but never allowed attempts.

here's my search index=MyApp eventype=authentication action=fail user=*

but this one prints all failures even if they have other successful attempt.

i only want users with only failed attempts without other successful attempts, i hope the picture below clears things:

green: user only have successful logins

Yellow: user have both successful/failed logins

Red: user only have failed logins

i want the red area only

Thanks

[ITWhisperer · Accepted solution]

| makeresults 
| eval _raw="user,attempt
A,success
B,fail
B,success
C,fail
A,success
B,fail
B,success
C,fail"
| multikv forceheader=1 
| fields - _* linecount



| stats values(attempt) as attempt dc(attempt) as count by user
| where attempt="fail" AND count = 1

[moayadalghamdi]

another help Mr. Whisperer

i want to show this value as a single count to show it in a "single value" visualization.

Thanks again ^_^ 

[ITWhisperer]

Which count? The count of users who failed or the count of failures (by user or total)?

[moayadalghamdi]

Hello.

i had 27 results of distinct users who never had a successful login, i want those 27 results as a single count value

i want to show it like this

this is a 3d search with span=1d, i want something similar.

thanks ^_^

[ITWhisperer]

Add

| stats count

to the end to get the 27

[moayadalghamdi]

sorry but i need it in timechart, so i can see the changes overtime.

i used 

| timechart count

and

| timechart span=1d count

but no statistics neither visuals was shown.

pleas help with it, thanks ^_^

[ITWhisperer]

It would help if you were clear from the outset what the full requirement was! Try this:

| bin _time span=1d
| stats values(attempt) as attempt dc(attempt) as count by _time user
| where attempt="fail" AND count = 1
| stats count by _time

[moayadalghamdi]

sorry but its not working.

here's the search.

and here's the search with the count by _time

[ITWhisperer]

That isn't the search with _time that I suggested - you need to bin the time into days, add it to the first stats so that _time in available for the second stats. Please read and implement the suggestions carefully before saying they don't work. I can't guarantee to get it right every time, but if you don't try what is suggested, how will we know if it works or not?

[moayadalghamdi]

sorry for that, i took the wrong screen shot.

here's the actual screenshot with the bin command.

im so sorry to bother you.

[ITWhisperer]

Spoiler

You still haven't got the _time on the first stats!

[moayadalghamdi]

Spoiler

it worked!

thanks man, you're the best !

[moayadalghamdi]

you really deserve the rank LEGEND 

Thanks a lot ^_^