topic Re: Removing duplicates in exported Report results in Splunk Search

Removing duplicates in exported Report results

asarolkar — Thu, 31 Jan 2013 00:23:41 GMT

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 ?

Re: Removing duplicates in exported Report results

martin_mueller — Thu, 31 Jan 2013 09:35:42 GMT

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?

Re: Removing duplicates in exported Report results

asarolkar — Thu, 31 Jan 2013 15:37:27 GMT

That is correct !

Re: Removing duplicates in exported Report results

yannK — Thu, 31 Jan 2013 16:35:02 GMT

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.

Re: Removing duplicates in exported Report results

jeff — Thu, 31 Jan 2013 16:47:52 GMT

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

Re: Removing duplicates in exported Report results

asarolkar — Fri, 01 Feb 2013 07:52:02 GMT

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

But this solution saved us a lot of work.