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 ?
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
Man that was a brilliant solution !
Splunk is not excel. I get that.
But this solution saved us a lot of work.
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.
That is correct !
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?