Renaming fields generically based on lookup table/second source

[kenbaugher]

We have a setup of data going to splunk, where we query a number of files with varying numbers of fields (sometimes over 100 per file), and have a generic dashboard setup to do some displays of them. We use the first line of the query output for the headings of the files, but the field names are very short and not descriptive. Since this is done via ODBC we don't have direct access to the more descriptive column text. So we have for example a file coming in with fields F1,F2...F100. We are able to get those descriptive field names from SYSCOLUMNS into the form "filename, fieldname, fielddesc".
Is there a reasonable way to have this display a table in splunk to show the fielddesc for each field vs the field name?

[PickleRick]

While both approaches (foreach and transpose) should get you what you want, they might not have very good performance.

Since "we're using first row as column names" I'm wondering if it wouldn't be easier if you didn't pull the data directly to Splunk but rather wrote them to a CSV file and ingested that file with indexed extractions (yes, that's often not the best way either but in this case it might be better).

[yuanliu]

As a versatile alternative, you can use transpose.  Using the same lookup example as @livehybrid does, this is how to transform these extended mock data

F1	F2	F3
Hello	World	Test
Some	thing	else

into this form

FieldName1Example	FieldName2Example	FieldName3Example
Hello	World	Test
Some	thing	else

| transpose 0
| lookup fieldtest.csv fieldID as column
| fields - column
| transpose 0 header_field=fieldName
| fields - column

[livehybrid]

Hi @kenbaugher 

I think Ive wrapped my head around what you're trying to achieve, please see the following working example, note this is two separate SPL queries - the first to generate a lookup and second to use it:

== First create a lookup ==
|makeresults format=csv data="fieldID,fieldName
F1,FieldName1Example
F2,FieldName2Example
F3,FieldName3Example"
| outputlookup fieldtest.csv

== Example event == 
|makeresults | eval F1="Hello", F2="World", F3="Test"
| foreach F* [|eval test="Friendly"+json_extract(lookup("fieldtest.csv",json_object("fieldID","<<FIELD>>"),json_array("fieldName")),"fieldName"), {test}=<<FIELD>>]
| fields _time Friendly*

This loops over fields starting "F" and does a lookup against 'fieldtest.csv" against the "fieldID" field, then sets the value to the friendly field name, after this we then evaluate the existing F<n> value to that friendly field name.

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing