Splunk Search

Removing duplicates in exported Report results

asarolkar
Builder

I have a search like this which produces the result I want (it counts modules per account number and location - the log has moduleId, account number and location)

sourcetype="accountLog" ModuleID=* accountLocation=* | dedup ModuleID | stats count(ModuleID) AS Total by accountNumber, accountLocation




These are the results I get in the correct format.

AccountNumber | Account Location | Total

    1234                   US            123

    1234                   EU             545

How do I get rid of the Account number appearing multiple times?
Can we selectively stop it from duplicating row values for just this column (without using dedup - i cannot use dedup here for obvious reasons) ?

Before an answer is proposed I already know about using list(values).

If I append list(values) by to my current filter, it works, but when i export it into a CSV file (or get splunk to run a query to generate it inline).

I am trying to get non-duplicates for that column in the exported CSV.

Any suggestions ?

Tags (3)
0 Karma

jeff
Contributor

Well you could do what you want with some stats functions... something like

sourcetype="accountLog" ModuleID=* accountLocation=* | dedup ModuleID
| stats count(ModuleID) AS Total by accountNumber, accountLocation
| sort Total desc
| eval location=accountLocation.": ".Total
| stats list(location) by accountNumber

should give you a report with two columns

accountNumber | location
1234            EU: 545
                US: 123

asarolkar
Builder

Man that was a brilliant solution !
Splunk is not excel. I get that.

But this solution saved us a lot of work.

yannK
Splunk Employee
Splunk Employee

This is splunk not excel, you kind of need a value for each field of each event .....

you could play with streamstats to create a new field for the column that will be empty if this is the same than the previous one.

asarolkar
Builder

That is correct !

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Just to be sure I understand your goal, is this the result you're looking for?

AccountNumber | Account Location | Total
1234            US                 123
                EU                 545

In other words, still two rows but empty in row 2 column 1?

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...