Splunk Search

How to create a table that will combine search results based on SerialNumber and split them into 3 columns but one row?

kr5345
Engager

Hey there, pretty new to Splunk searching. I am trying to get a table created that will combine search results based on SerialNumber and split them into 3 columns but one row. 

Currently: 

`main_index` SerialNumber IN (XXX-XXX-XXX)
| search "DUKPT KEY #" OR "type=DUKPT"
| rex "DUKPT (Key|KEY) #(?<slot>[0-9]): \[ Status: (?<Status>[A-Z_]+)"
| rex "KSN:(?<Key>.+)\]"
| eval slot = if(LIKE (ApplicationVersion,"6.%"), slot, slot -1)
| eval Key = if(LIKE (ApplicationVersion,"6.%"), ("Slot #".slot.": KSN: ".ksn),if(Status="KEY_PRESENT","Slot #".slot.": KSN: ".Key,"Slot #".slot.": No Key Loaded"))
| dedup slot SerialNumber
| table SerialNumber Key | sort slot


Result:

kr5345_0-1648168728986.png

Desired Result:

SerialNumber, Slot0, Slot1, Slot2
XXX-XXX-XXX, No Key Loaded, No Key Loaded, No Key Loaded

I've tried Transpose, Transaction (which merged the entries into one row, but I couldn't figure out how to split the entries into their own column/field)

Labels (2)
Tags (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

`main_index` SerialNumber IN (XXX-XXX-XXX)
| search "DUKPT KEY #" OR "type=DUKPT"
| rex "DUKPT (Key|KEY) #(?<slot>[0-9]): \[ Status: (?<Status>[A-Z_]+)"
| rex "KSN:(?<Key>.+)\]"
| eval slot = if(LIKE (ApplicationVersion,"6.%"), slot, slot -1)
| eval Key = if(LIKE (ApplicationVersion,"6.%"), ("Slot #".slot.": KSN: ".ksn),if(Status="KEY_PRESENT","Slot #".slot.": KSN: ".Key,"Slot #".slot.": No Key Loaded"))
| dedup slot SerialNumber
| xyseries SerialNumber slot Key

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

`main_index` SerialNumber IN (XXX-XXX-XXX)
| search "DUKPT KEY #" OR "type=DUKPT"
| rex "DUKPT (Key|KEY) #(?<slot>[0-9]): \[ Status: (?<Status>[A-Z_]+)"
| rex "KSN:(?<Key>.+)\]"
| eval slot = if(LIKE (ApplicationVersion,"6.%"), slot, slot -1)
| eval Key = if(LIKE (ApplicationVersion,"6.%"), ("Slot #".slot.": KSN: ".ksn),if(Status="KEY_PRESENT","Slot #".slot.": KSN: ".Key,"Slot #".slot.": No Key Loaded"))
| dedup slot SerialNumber
| xyseries SerialNumber slot Key

kr5345
Engager

Yep! this made it work. I was on the right track but was not getting the xyseries arguments right for some reason. Thanks!

0 Karma
Get Updates on the Splunk Community!

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...

What’s New in Splunk Observability – September 2025

What's NewWe are excited to announce the latest enhancements to Splunk Observability, designed to help ITOps ...

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...