How to consolidate two columns into a single column without losing data?

[msarro]

We have a data source which contains two columns, both of which contain valuable information. In any event, either one of them, or both, or neither, can be populated. I need to create a search which takes both of these columns and creates a new column with all of the values found in either one of the columns.
So for example -

Imagine this was the hypothetical source data:

timestamp,ID1,ID2
00000000000,"USERA",""
00000000000,"","USERB"
00000000000,"USERC","USERD"
00000000000,"",""
00000000000,"USERX",""
00000000000,"USERY","USERZ"

And this is the sort of search I would like to run:

index=myindex sourcetype=mysource (ID1="*" OR ID2="*")
|SOMECOMMAND newcolumn=SOMEFUNCTION(ID1,ID2)
|table newcolumn

Results expected would be:

USERA
USERB
USERC
USERD
USERX
USERY
USERZ

Is there any command like this?? Or any creative way to get results with data like that? Coalesce does not work because it will only take the value from the first column if both are populated. The results we would see with coalesce and the supplied sample data would be:

USERA
USERB
USERC
USERX
USERY

[msarro]

To solve this I ended up using a combination of the suggestions from SplunkFu and gkanapathy. Here is the search to get the desired result:
index=myindex sourcetype=mysource (ID1="" OR ID2="")|eval ID_LIST=mvappend(ID1,ID2)| table ID_LIST|mvexpand ID_LIST

[Kyoma]

God, thank you

[gkanapathy]

... | eval new=mvappend(field1,field2)

Though I would ask what it means to you to have two values in a single field in a single record. Using a Splunk multivalue field is one way, but perhaps the answer given by another poster where you simply concatenate the string values together is more appropriate.

[duartet]

worked for me! Thanks

[msarro]

The goal is not to end up with two values in a single field in a single record. The goal is to end up with a single column containing all of the values found in either of the fields, this way it can be used as a subsearch in another search string 🙂

[SplunkFu · Accepted solution]

Hi,

How about using eval to combine the two fields, with something like:

eval newfield=field1+","+field2

Then you could expand the fields using mvexpand.

Hope this helps.

[SplunkFu]

cool, glad it helped (in part)

[msarro]

This worked in tandem with gkanapathy's suggestion of mvappend. So mvappend combined the values (or took either one when it was present) and created mv fields. Then mvexpand split the multivalue fields into their own separate events, creating the single column of values that I was looking for. Thanks!