I want to get fail number and total number from one data model, but I cannot figure out how to do this.
My search is as below:
|tstats count AS Requests,count(eval(Log.success="false")) as Failed from datamodel="EventLog" by Log.eventName.
This would give you counts by Log.success... so you'll have counts for fail, success, FAIL, etc.
|datamodel EventLog | stats c AS Requests by Log.success
This would give you just two columns, using pivot for both:
| pivot EventLog success count(success) AS "Requests" | appendcols [|pivot EventLog success count(success) as "Failures" | where success="false"]
This is same as above but using datamodel
|datamodel EventLog | stats c(Log.success) AS Requests | appendcols [| datamodel EventLog | stats c AS Failures | where Log.success="false"]
Thank you for your answer. But the result is not correct when I use appendcols instead of join TransactionsLog.eventName,TransactionsLog.mpsUserId, type=left
in my search below. Could you help to look?
My search:
|tstats count AS Requests, avg(TransactionsLog.duration) as avgdur, max(TransactionsLog.duration) as "Max duration", p99(TransactionsLog.duration) as "TP99", p90(TransactionsLog.duration) as "TP90" from datamodel="MarketplaceService_TransactionsLog" by TransactionsLog.eventName, TransactionsLog.mpsUserId | join TransactionsLog.eventName,TransactionsLog.mpsUserId, type=left [|tstats count AS Failed from datamodel="MarketplaceService_TransactionsLog" where TransactionsLog.success=false by TransactionsLog.eventName ,TransactionsLog.mpsUserId, _time| stats sparkline(count(Failed)) as FailedTimeLine sum(Failed) as Failed by TransactionsLog.eventName,TransactionsLog.mpsUserId]|fillnull value=0 Failed|eval "Average duration" = round(avgdur, 2) | eval "Success %" = round((Requests-Failed)/Requests*100, 2) |rename TransactionsLog.eventName as "Service"|rename TransactionsLog.mpsUserId as "mpsUserId" |makemv delim="," setsv=true FailedTimeLine| fields Service,mpsUserId, Requests, "Average duration", "Max duration", "TP99", "TP90", Failed, "Success %",FailedTimeLine | sort Requests desc