Splunk Search
Highlighted

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

Explorer

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?

Tags (4)
Highlighted

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

Motivator

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.)

View solution in original post

Highlighted

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

Explorer

Solid.

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

0 Karma