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

[kr5345]

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:

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)

[ITWhisperer · Accepted solution]

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]

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