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

[anushaashok]

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?

[cmerriman]

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]

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?

[cmerriman]

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.

[anushaashok]

this worked!! thanks a lot!