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

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

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

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!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...