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 App for Anomaly Detection End of Life Announcement

Q: What is happening to the Splunk App for Anomaly Detection?A: Splunk is officially announcing the ...

Transforming Financial Data into Fraud Intelligence

Every day, banks and financial companies handle millions of transactions, logins, and customer interactions ...

How to send events & findings from AWS to Splunk using Amazon EventBridge

Amazon EventBridge is a serverless service that uses events to connect application components together, making ...