Splunk Search

Is it possible to set field values based on other field names?

tgpers
Engager

I have the following table:

cp1_date  cp1_status  cp2_date  cp2_status  cp3_date   cp3_status
20190601  ok          20190607  nok         20160613   ok

... and I'm trying to get to something like this:

cp    date      status
cp1   20190601  ok
cp2   20190607  nok
cp3   20190613  ok

Not sure where to start, but at least one question is if it is possible to set field values (e.g. cp1) based on another field names (e.g. cp1_status. (The field named "cp" does not have to be derived from the original event/table.)

Thanks for any suggestions!

0 Karma
1 Solution

niketn
Legend

@tgpers, you can try the following with your existing search. Please try out and confirm!

 <yourCurrentSearchWithResultAsPerQuestion>
| transpose column_name=temp
| rename "row 1" as status
| eval temp=split(temp,"_")
| eval cp=mvindex(temp,0), key=mvindex(temp,1)
| fields - temp
| xyseries cp key status

Community would be able to assist you better if you provide your current SPL, so that we help you with your expected result with an optimized query (if raw data has required fields may be transpose and xyseries can be avoided to directly build the table as your expected output).

Following is a run anywhere search example which builds dummy data as per question and then displays the final results as per the question:

| makeresults
| fields - _time
| eval data= "20190601,ok,20190607,nok,20160613,ok"
| makemv data delim=","
| eval cp1_date=mvindex(data,0),  cp1_status=mvindex(data,1),  cp2_date=mvindex(data,2),  cp2_status=mvindex(data,3),  cp3_date=mvindex(data,4),   cp3_status=mvindex(data,5)
| fields - data
| transpose column_name=temp
| rename "row 1" as status
| eval temp=split(temp,"_")
| eval cp=mvindex(temp,0), key=mvindex(temp,1)
| fields - temp
| xyseries cp key status
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@tgpers, you can try the following with your existing search. Please try out and confirm!

 <yourCurrentSearchWithResultAsPerQuestion>
| transpose column_name=temp
| rename "row 1" as status
| eval temp=split(temp,"_")
| eval cp=mvindex(temp,0), key=mvindex(temp,1)
| fields - temp
| xyseries cp key status

Community would be able to assist you better if you provide your current SPL, so that we help you with your expected result with an optimized query (if raw data has required fields may be transpose and xyseries can be avoided to directly build the table as your expected output).

Following is a run anywhere search example which builds dummy data as per question and then displays the final results as per the question:

| makeresults
| fields - _time
| eval data= "20190601,ok,20190607,nok,20160613,ok"
| makemv data delim=","
| eval cp1_date=mvindex(data,0),  cp1_status=mvindex(data,1),  cp2_date=mvindex(data,2),  cp2_status=mvindex(data,3),  cp3_date=mvindex(data,4),   cp3_status=mvindex(data,5)
| fields - data
| transpose column_name=temp
| rename "row 1" as status
| eval temp=split(temp,"_")
| eval cp=mvindex(temp,0), key=mvindex(temp,1)
| fields - temp
| xyseries cp key status
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

tgpers
Engager

Thanks a lot, worked perfectly!
I had tried both transpose and xyseries, but didn't manage to combine them. You just made my day 🙂
In this particular case the raw data is from a kvstore which includes only these fields, so there is not much I can do with respect to the SPL to build another table directly.

Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...