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?

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.