How to determine the number of "BAD" transactions

[dbcase]

Hi, I have this query

earliest =-30m index=relay_json host=betamax* relayPairId!="null" | transaction relayPairId startswith="extracted_eventType=NewRelayCreated*" endswith="extracted_eventType=RelayClosed"|reverse

And it returns GOOD events like this

{"camIP":"172.16.12.104","camUser":"iculh11C","byteTransferred":0,"txDuration":0,"videoToken":"app%3DdefaultKey%3Blogin%3Dexr108016%3Bts%3D1521576297434%3Bsig%3D0EA0F5E4F1396E44182AA7EF5A4BAF32","userConnPairId":443149,"severity":"DEBUG","eventTimeStamp":"15:04:58.154 -0500 2018-03-19","eventType":"NewRelayCreatedInUserConn","viewType":"image.jpg","viewerId":"37d55aa8a12c29df4eed2e24bec394af","message":null,"relaySessionId":"2178135031060637398","siteId":"1001408","relayPairId":123526,"camConnPairId":443145}

And BAD events like this. A BAD event is determined with Severity=ERROR

{"camIP":"172.16.12.116","camUser":"icdoG6T0","byteTransferred":0,"txDuration":0,"videoToken":"app%3DdefaultKey%3Blogin%3Dexr108016%3Bts%3D1521576297928%3Bsig%3DAF26351C7DD9A4CE85F6B95636C7AA8E","userConnPairId":443150,"severity":"DEBUG","eventTimeStamp":"15:05:03.479 -0500 2018-03-19","eventType":"NewRelayCreatedInCamConn","viewType":"image.jpg","viewerId":"71384048eea747bfc2167d85b23aaba0","message":null,"relaySessionId":"2948482279907346821","siteId":"1001408","relayPairId":123529,"camConnPairId":443147}
{"camIP":"172.16.12.116","camUser":"icdoG6T0","byteTransferred":0,"txDuration":488,"videoToken":"app%3DdefaultKey%3Blogin%3Dexr108016%3Bts%3D1521576297928%3Bsig%3DAF26351C7DD9A4CE85F6B95636C7AA8E","userConnPairId":443150,"severity":"DEBUG","eventTimeStamp":"15:05:03.967 -0500 2018-03-19","eventType":"TerminatingExistingRelay","viewType":"image.jpg","viewerId":"71384048eea747bfc2167d85b23aaba0","message":"This relay is terminated by other user connection with pair id: 443155; data read from camera: 0","relaySessionId":"2948482279907346821","siteId":"1001408","relayPairId":123529,"camConnPairId":443147}
{"camIP":"172.16.12.116","camUser":"icdoG6T0","byteTransferred":0,"txDuration":489,"videoToken":"app%3DdefaultKey%3Blogin%3Dexr108016%3Bts%3D1521576297928%3Bsig%3DAF26351C7DD9A4CE85F6B95636C7AA8E","userConnPairId":443150,"severity":"ERROR","eventTimeStamp":"15:05:03.968 -0500 2018-03-19","eventType":"TerminatingExistingRelay","viewType":"image.jpg","viewerId":"71384048eea747bfc2167d85b23aaba0","message":"This relay is terminated by other user connection with pair id: 443155; data read from camera: 0; content-length header: 0","relaySessionId":"2948482279907346821","siteId":"1001408","relayPairId":123529,"errorCode":"UCE-13802","camConnPairId":443147}
{"camIP":"172.16.12.116","camUser":"icdoG6T0","byteTransferred":0,"txDuration":490,"videoToken":"app%3DdefaultKey%3Blogin%3Dexr108016%3Bts%3D1521576297928%3Bsig%3DAF26351C7DD9A4CE85F6B95636C7AA8E","userConnPairId":443150,"severity":"DEBUG","eventTimeStamp":"15:05:03.969 -0500 2018-03-19","eventType":"RelayClosed","viewType":"image.jpg","viewerId":"71384048eea747bfc2167d85b23aaba0","message":"Camera read bytes: 0","relaySessionId":"2948482279907346821","siteId":"1001408","relayPairId":123529,"camConnPairId":443147}

What I need to do is determine the percentage of BAD transactions vs GOOD transactions. Any thoughts???

[skoelpin · Accepted solution]

Try this

index=... sourcetype=...
| eval decision=if(Severity= ERROR, "Bad","Good")
| timechart span=5m count  by decision 
| eval sum='Bad'+'Good'
| eval ratio_good='Good'/'sum'
| eval ratio_bad='Bad'/'sum'
| timechart span=5m min(ratio_bad) AS ratio_bad max(ratio_bad) AS ratio_bad