Rename fields based on csv file

[ahansson89]

I have a search, which have different field names per event which I need to output in a table. There is no pattern in the values, all I know is the that the value can be called ABC, ACB, CBA, CAB, BCA or BAC the list will be growing over time, so maintaining this in a CSV file in crucial and I do not want to go with FIELDALIAS's. I basically want to rename ABC, ACB, CBA, CAB, BCA and BAC to COMMON to use it in my output.

I came up with this, which do what I want for ABC, but I need a solution that renames all fields in my list:

rename.csv:
rename
ABC
ACB
CBA
CAB
BCA
BAC

Query:

base search | rename [| rename.csv | fields rename| return $rename ] as COMMON| table COMMON

What is the better solution to what I am trying to do?

Thanks,

[DalJeanis · Accepted solution]

Unless there is a reason to get rid of the other fields, I believe that coalesce will do what you want.

In the sample code below, we have changed the name of the field and file, because you just shouldn't ever use a keyword like rename as a field name. Too many ways to shoot yourself in the foot that way.

This run-anywhere sample might help illustrate...

| makeresults | eval myrename="field1 field2 field3" | makemv myrename | mvexpand myrename 
| rename COMMENT as "The above just creates sample data - input your myrename.csv there ."

| rename COMMENT as "Now we format the csv into a coalesce command."
| format "coalesce(" "" "" "" "," ")" 
| rex mode=sed field=search "s/myrename=|\"//g"

The above results in the following output in the field "search"...

coalesce( field1 , field2 , field3 ) 

...so you can use it, more or less, like this ...

| eval COMMON = [
    | inputcsv myrename.csv | table myrename  
    | format "coalesce(" "" "" "" "," ")" 
    | rex mode=sed field=search "s/myrename=|\"//g"
    ]

---

Updated to change the word concatenate to coalesce.

However, as noted in the comments, any of three splunk verbs - concatenate, coalesce, and mvappend - will function in the above code to achieve the business requirements.

Use mvappend if there is a possibility of more than one of the fields having a value, and you want to keep all values. (Consider having an | mvdedup COMMON afterwards.)

On the other hand, if only one of the fields could have a value at a time, then coalesce might be marginally more efficient than the others, but they will all have the same final result.

[ahansson89]

I am not trying to concatenate the fields.

My base search returns a number of events. Each event has different fields, where I want to output one of them for each event. The field names can be ABC, ACB, CBA, CAB, BCA or BAC, but there are only one for each event.

[DalJeanis]

@ahansson89 - my bad - I meant coalesce, but since I was only testing to see that the rex code worked, I didn't notice my brain had used the wrong c verb.

Actually, come to think of it, concatenate would work just as well, since all the other fields would have to be null anyway for your specifications to be right, and concatenating a bunch of nulls together with a single value would result in the same as the original value.

If there is a possibility that more than one of the fields will have a value, and you want to retain the multiple values, then use the same code as above with mvappend.

[Richfez]

Could we get some examples of the events underlying this problem set, in case there's an easier way to extract the fields so that the problem becomes trivial at the search layer?

[Richfez]

Could you provide a sample of the data? In this case a half dozen lines maybe?

(I'm wondering if a different sort of rex/regex might make this much easier, but we need to see some of the data to know.)

[ahansson89]

For pretty much all events the COMMON field will be different and with no patterns, so I do not want to maintain a complicated regex. I am fine doing a regex if we can use the CSV as a base for the regex and match on ABC, ACB, CBA, CAB, BCA and BAC.