Splunk Search

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

felipesodre
Path Finder

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....

0 Karma
1 Solution

manjunathmeti
Champion

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

View solution in original post

0 Karma

manjunathmeti
Champion

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
0 Karma

felipesodre
Path Finder

Thank you all good! Please close the ticket.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

felipesodre
Path Finder

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"

alt text

0 Karma

manjunathmeti
Champion

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.

0 Karma

felipesodre
Path Finder

Perfect.
It worked.

Thank you

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...