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

[tgpers]

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!

[niketn · Accepted solution]

@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

[tgpers]

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.