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!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...