Issue w/ 'timechart' and 'transactions'

[rowshambow]

I've set up a transaction to determine successful login using the following:

index=main sourcetype=TELEM | transaction SESSIONID startswith="LOGIN" endswith="LOADCOMPLETE" keepevicted=true | search TELEMTAG="LOGIN"

..that seem to accurately give me all logins that succeed or fail, and I've been able to extract the login failure rate using the following:

_| timechart span=1h eval(count(eval(closed_txn=0)) / count * 100) as "Failed Loading %"

Here is where I am hitting my snag:

In addition to the failure rate, I'd also like to be able to determine the percentage of users that were unable to login. Which is to say:

Get the number of users (USERID) who have transactions that only ever fail (closed_tnx 1 and only 1). I've been able to get the desired result using a series of piped stats functions, but I'd ideally like to combine the two numbers on the same report.

I appreciate that both results are using different keys to count by, but I am hoping there may be a way to combine the results w/o having to do the search twice (it is a fairly lengthy search).

Any pointers?

[gkanapathy]

try:

... | eventstats min(closed_txn) as alwaysfailed by USERID
    | timechart count
                count(eval(closed_txn=0)) as failed
                dc(USERID) as totalusers_count
                dc(eval(alwaysfailed=0)) as usersalwaysfailed_count
    | eval failedloading_pct = failed/count
    | eval failedusers_pct = usersalwaysfailed/totalusers