Splunk Search

How to run "stats dc()" in place of dedup in my search?

IRHM73
Motivator

Hi, I wonder whether someone may be able to help me please.

The search I'm using correctly extract the information I need.

index=main auditSource=filing auditType=FilingCompleted OR auditType=FilingSubmissionAcknowledged | dedup "detail.Filing ID" 
| table field1, field2, field3, field4

But to make this a little more efficient, I would like to change this to use the stats dc() command. I know how to use this instead of the 'dedup', but can't work out how to then incorporate this into a table to return all the fields I need to see.

I just wondered whether someone could possibly look at this please and offer some guidance on how I may be able to achieve this.

Many thanks and kind regards

Chris

0 Karma
1 Solution

Jeremiah
Motivator

Not sure you necessarily need dc. If you wanted count of unique combinations using the 5 fields you listed:

 index=main auditSource=filing auditType=FilingCompleted OR auditType=FilingSubmissionAcknowledged | stats count by "detail.Filing ID", field1, field2, field3, field4 | table field1, field2, field3, field4

However, if you really only want to use "detail.Filing ID" to remove duplicates:

 index=main auditSource=filing auditType=FilingCompleted OR auditType=FilingSubmissionAcknowledged | stats latest(*) AS * by "detail.Filing ID"  | table field1, field2, field3, field4

You can substitute latest for earliest, first, or last, depending on if you want the first matching field or the last, or you want the earliest chronologically matching field or the latest.

http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/CommonStatsFunctions

View solution in original post

javiergn
SplunkTrust
SplunkTrust

Hi, I'm not too sure if this is what you need. If not please post an example in tabular format:

 index=main auditSource=filing auditType=FilingCompleted OR auditType=FilingSubmissionAcknowledged
| stats dc("detail.Filing ID") as count by field1, field2, field3, field4, 

IRHM73
Motivator

Hi @javiergn, thank you also for taking the time to come back to me with this. Your solution also works fine.

Kind Regards

Chris

0 Karma

Jeremiah
Motivator

Not sure you necessarily need dc. If you wanted count of unique combinations using the 5 fields you listed:

 index=main auditSource=filing auditType=FilingCompleted OR auditType=FilingSubmissionAcknowledged | stats count by "detail.Filing ID", field1, field2, field3, field4 | table field1, field2, field3, field4

However, if you really only want to use "detail.Filing ID" to remove duplicates:

 index=main auditSource=filing auditType=FilingCompleted OR auditType=FilingSubmissionAcknowledged | stats latest(*) AS * by "detail.Filing ID"  | table field1, field2, field3, field4

You can substitute latest for earliest, first, or last, depending on if you want the first matching field or the last, or you want the earliest chronologically matching field or the latest.

http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/CommonStatsFunctions

IRHM73
Motivator

Hi @jeremiah, thank you for taking the time to reply to my post and add the solution. Perhaps I was being a little over zealous, and is something I'l have to think about. But your solution works fine.

Kind Regards

Chris

0 Karma

jplumsdaine22
Influencer

awesome answer

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...