topic Re: How to aggregate several similar fields into one and tabulate top values in Splunk Search

How to aggregate several similar fields into one and tabulate top values

shantu — Tue, 17 Mar 2015 21:48:08 GMT

I have created several search-time field extractions to filter out Credit Card numbers from our logs:

\s+(?<CCVisaNumber>4[0-9]{12}(?:[0-9]{3})?)
\s+(?<CCMasterCardNumber>5[1-5][0-9]{14})
\s+(?<CCJCBNumber>(?:2131|1800|35\d{3})\d{11})
\s+(?<CCAmexNumber>3[47][0-9]{13})
\s+(?<CCDinersNumber>3(?:0[0-5]|[68][0-9])[0-9]{11})
\s+(?<CCDiscoverNumber>6(?:011|5[0-9]{2})[0-9]{12})

I would ideally like to aggregate all of these in one field "CreditCardNumber" so that I can then use:

host=stagexx | top CreditCardNumber

(Essentially, I want to display them in descending order of frequency)

Is there any way to do that? The rex command doesn't support this sort of OR use case (using | with quotes et al proved futile).

Instead, could I perhaps aggregate these into one field while trying to create a table? Something like assigning all of them to one type of sorts (such as CreditCardNumber) and then displaying all values in descending order of frequency?

Re: How to aggregate several similar fields into one and tabulate top values

aweitzman — Tue, 17 Mar 2015 21:55:33 GMT

You want to use the coalesce function of the eval command:

...all your extractions... 
| eval CCNumber=coalesce(CCVisaNumber,CCMasterCardNumber,CCJCBNumber,CCAmexNumber,CCDinersNumber,CCDiscoverNumber)
| top CCNumber

(This will only work if you only have one credit card number per event, though. If you have more than one per event, you'll need fancier footwork than this.)

Re: How to aggregate several similar fields into one and tabulate top values

shantu — Tue, 17 Mar 2015 21:59:17 GMT

Solid.

Yeah - at most one credit card number per event, so this works quite well. Thanks for the help!