How do I get a table with a count and distinct count using a field regex

[felipesodre]

I would like to get a count of errors that I have generated on splunk from different objects. All of them have a field error.

This is my query:
index="db-woodchipper" earliest=-7d@d latest=now \"Error\": | table *.Error

Results:
![alt text][1]

RAW:
{"SalesforceUpdater": {"MessageBody": {"ServerName": "xxxxxx", "DbName": "[email protected]"}, "Error": "FATAL: database \"[email protected]\" does not exist\n"}}

{"EmailSettingsCorrection": {"MessageBody": {"ServerName": "xxxxxx", "DbName": "xxxxxxx"}, "Task": "EmailSettingsCorrection", "Error": "FATAL: database \"xxxxxx\" does not exist\n"}}

However I would like to have something like:
Operation. |Count | Count Distinct
EmailSettingsCorrection | 10 | 2
SalesforceUpdater | 5 | 1

And so on....

[manjunathmeti · Accepted solution]

hi @felipesodre,

Try this query:

index="db-woodchipper" earliest=-7d@d latest=now \"Error\": 
| table *.Error 
| stats count(*) as *, dc(*) as Distinct_* 
| transpose column_name=Operation 
| eval Distinct=if(like(Operation, "Distinct%"), 'row 1', ""), count=if(like(Operation, "Distinct%"), "", 'row 1'), Operation=replace(Operation, "Distinct_", "") 
| stats sum(count) as Count, sum(Distinct) as "Count Distinct" by Operation

[felipesodre]

Thank you all good! Please close the ticket.

[richgalloway]

There are no "tickets" here as this is a community supported forum. When you get a solution to your problem, click the "Accept" link to mark the question as resolved.

[felipesodre]

Furthermore, Is there any way that I can configure the errors lines to redirect to the event errors?

Also, do you know how to format the field function to hide ".Error" eg: showing just: "EmailSettingsCorrection"

[manjunathmeti]

Check if you can use drill down to see actual events for each error. Check this: https://docs.splunk.com/Documentation/Splunk/8.0.3/Viz/DrilldownIntro.

To hide .Error use replace command:

 | eval Function=replace(Function, ".ERROR", "")

And please accept answer so that it can help others also.

[felipesodre]

Perfect.
It worked.

Thank you