topic How do I get a table with a count and distinct count using a field regex in Splunk Search

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

felipesodre — Mon, 20 Apr 2020 18:39:25 GMT

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": "xxx@xxxxx.com"}, "Error": "FATAL: database \"xxxx@xxx.xxx\" 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....

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

manjunathmeti — Mon, 20 Apr 2020 21:07:47 GMT

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

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

felipesodre — Mon, 20 Apr 2020 22:51:42 GMT

Perfect.
It worked.

Thank you

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

felipesodre — Mon, 20 Apr 2020 23:01:51 GMT

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"

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

manjunathmeti — Tue, 21 Apr 2020 10:36:26 GMT

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.

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

felipesodre — Wed, 22 Apr 2020 13:48:02 GMT

Thank you all good! Please close the ticket.

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

richgalloway — Wed, 22 Apr 2020 14:31:26 GMT

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.