How to write query to get count of total volume and system errors in a single query

[GaneshAryan]

Hi All,
we have a scenario to throw an alert if  System error rate exceeds 5%  i.e. (#system errors / #total volume)*100% .
How to get the count of total events and system errors then calculate the percentage based on count as per above formula.

Ex : Query for total volume : sourcetype="sfdc:transaction_log__c" | eval message = "b2cforce-liveperson" | where like(_raw,"%".message."%")

Query for System errors  :  sourcetype="sfdc:transaction_log__c" | eval message = "userId Retrieval Failure"
| where like(_raw,"%".message."%")

[bowesmana]

Something like this

sourcetype="sfdc:transaction_log__c" (message = "*b2cforce-liveperson*" OR message = "*userId Retrieval Failure*")
| eval isEvent=if(match(message, "b2cforce-liveperson"), 1, 0)
| stats sum(isEvent) as Events count(eval(isEvent=0)) as Errors
| eval FailureRate=Errors/Events*100

It's better if you can use something else other than matching message to differentiate between errors and events, but as long as events contain the b2c... text and errors do not, this should work.

[rnowitzki]

Hi @GaneshAryan ,

Based on what you provided, I came up with this:

index=? sourcetype="sfdc:transaction_log__c" 
| eval totalmessage="b2cforce-liveperson", errormessage="userId Retrieval Failure"
| eval total=if(like(_raw,"%".totalmessage."%"),1,total), errors=if(like(_raw,"%".errormessage."%"),1,errors)
| stats sum(total) as total, sum(errors) as errors

After that you can do your math with "total" and "errors".

Not sure I'd use the like() function to search something in _raw in general. But it works it seems.

Maybe you can setup a field that indicates if the event is an error, instead of searching for a string in _raw all the time.

Cheers
Ralph