Splunk Search

how to combine timechart for 2 different evals which are seperated by append?

anushaashok
New Member

here is my query :
index="test1" sourcetype="test2" "login success*" OR "login failed*" | timechart span=1d dc(user) as total | append[ search index="test1" sourcetype="test2" "login success*" | timechart span=1d dc(user) as success] | eval fail=total-success | timechart span=1d count(fail)

there is a mixup of timecharts all over the query. ultimately i want the count(fail) for each day which is the difference of total and success of each day.

how do i make this work?

0 Karma

cmerriman
Super Champion

try to combine it into one query:

index="test1" sourcetype="test2" "login success*" OR "login failed*" |eval failed=if(searchmatch("login failed"),user,null())|eval success=if(searchmatch("login success"),user,null())| timechart span=1d dc(user) as total dc(failed) as failed dc(success) as success

anushaashok
New Member

cmerriman,
the query you gave does not do the math of subtracting total and success. let me elaborate :
if a user logged in successfully we see "login success"
if a user was unable to login we see "login fail"
but a user who failed to login might login successfully in second attempt.
so I am trying to find the count of users who failed to login only and did not successfully login.

in my query:
index="test1" sourcetype="test2" "login success*" OR "login failed*" | timechart span=1d dc(user) as total | append[ search index="test1" sourcetype="test2" "login success*" | timechart span=1d dc(user) as success] | eval fail=total-success | timechart span=1d count(fail)

total contains all users who failed or succeed login, success contains users who logged in successfully only, so the difference of total and success will give me true failure count.

how do I do this?

0 Karma

cmerriman
Super Champion

thanks for explaining that. if i'm understanding, and if my query does what i believe it's doing, i think this might work:

index="test1" sourcetype="test2" "login success*" OR "login failed*" |eval success=if(searchmatch("login success"),user,null())| timechart span=1d dc(user) as total dc(success) as success|eval failed=total-success|fields _time failed

the success eval should give you all users with "login success", the timechart should give you the total distinct users and the total distinct successful logins by day. then you should be able to subtract the two with the failed eval and show only the two fields you're interested in.

0 Karma

anushaashok
New Member

this worked!! thanks a lot!

0 Karma
Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...