How to get a stats count for the same field values that are extracted from different unique sample data?

[sjangampeta]

Our logs have errors which we want to extract in a field and get a stats count of those errors.
But sample data is different for each error, though they share the same index and sourcetype. We are trying to extract those errors under same field name.

We successfully extracted all errors with rex, but when I try stats, one or 2 errors are not showing up during count, even though we have that error for the given time frame.

index=xyz  sourcetype=abcd |  rex "(i?)\)\s\]\s\-\s(?Rolling\s\w+\s\w+\s\w+)" | rex "(i?)\)\s\]\s\-\s(?Could\s\w+\s\w+\s\w+)" | rex "(i?)\:\s(?P\w+\-\d{5}\:\s\w+\s(constraint))" | rex "(i?)\n(?Stored\s\w+\s\w+\s\w+)" | rex "(i?)\d\n(?Pure\s\w+\s\w+)" | stats count by error

Results

error                                 count
Could not invoke operation          178
Pure SQL Exception                  100
Rolling back JCA LocalTransaction     320
Stored procedure invocation error    42

And one of the error is not showing in stats count.

[sundareshr · Accepted solution]

Try this

index=xyz  sourcetype=abcd |  rex "(i?)\)\s\]\s\-\s(?<err1>Rolling\s\w+\s\w+\s\w+)" | rex "(i?)\)\s\]\s\-\s(?<err2>Could\s\w+\s\w+\s\w+)" | rex "(i?)\:\s(?P<err3>\w+\-\d{5}\:\s\w+\s(constraint))" | rex "(i?)\n(?<err4>Stored\s\w+\s\w+\s\w+)" | rex "(i?)\d\n(?<err5>Pure\s\w+\s\w+)" | table err* | untable dummy fields error | stats count by error

[sjangampeta]

Thank you given query is displaying results in correct format, but when i click error to view events, they are not displaying any events.

[sundareshr]

If you want to create an interactive report, you will have to create a dashboard. You can save this search as a dashboard panel and add a second 'events' panel with this query.

<query>index=xyz  sourcetype=abcd "$err$"</query>

Once you have the two panels, edit the source and add the following to the first panel (table with stats)

<drilldown>
<set token="err">$row.error$</set>
</drilldown>

[somesoni2]

Could it be possible that one event might have error message that'll match two of your regex? Since you're using same name for field extraction, if an event is matching two rex statement, the 2nd rex will overwrite the 1st and you'll not see the event with that message.

[sjangampeta]

When search is made using rex, new fields extracted are displaying correct error values.
But if we are using stats , one error (ORA-00001: unique constraint) value is missing.

[sundareshr]

The errors that are not showing up in stats count, did they get extracted correctly? In other words, do they show up if you run this query

index=xyz  sourcetype=abcd |  rex "(i?)\)\s\]\s\-\s(?Rolling\s\w+\s\w+\s\w+)" | rex "(i?)\)\s\]\s\-\s(?Could\s\w+\s\w+\s\w+)" | rex "(i?)\:\s(?P\w+\-\d{5}\:\s\w+\s(constraint))" | rex "(i?)\n(?Stored\s\w+\s\w+\s\w+)" | rex "(i?)\d\n(?Pure\s\w+\s\w+)" | table _time error _raw

If not, can you share some sample (sanitized) events with each error text.

[sjangampeta]

Hi Sundaresh, events using table _time error1 error2 error3 error4 error5 displays below events.
I see few of errors occuring at the same time.

2016-10-10 13:52:10 Rolling back JCA LocalTransaction                
2016-10-10 13:52:10     Could not invoke operation  ORA-00001: unique constraint        Pure SQL Exception
2016-10-10 13:51:11 Rolling back JCA LocalTransaction                
2016-10-10 13:51:11     Could not invoke operation  ORA-00001: unique constraint        Pure SQL Exception
2016-10-10 13:51:06 Rolling back JCA LocalTransaction                
2016-10-10 13:51:06     Could not invoke operation      Stored procedure invocation error    
2016-10-10 13:49:31 Rolling back JCA LocalTransaction                
2016-10-10 13:49:31     Could not invoke operation  ORA-00001: unique constraint        Pure SQL Exception
2016-10-10 13:49:24 Rolling back JCA LocalTransaction                
2016-10-10 13:49:24     Could not invoke operation  ORA-00001: unique constraint        Pure SQL Exception
2016-10-10 13:48:40 Rolling back JCA LocalTransaction                
2016-10-10 13:48:40     Could not invoke operation  ORA-00001: unique constraint        Pure SQL Exception
2016-10-10 13:47:34 Rolling back JCA LocalTransaction                
2016-10-10 13:47:34     Could not invoke operation           
2016-10-10 13:47:34                  
2016-10-10 13:47:34                  
2016-10-10 13:46:40 Rolling back JCA LocalTransaction                
2016-10-10 13:46:40     Could not invoke operation  ORA-00001: unique constraint        Pure SQL Exception