topic Re: Convert Multiple Values in single field to multiple fields in Splunk Search

Convert Multiple Values in single field to multiple fields

matt4321 — Tue, 29 Sep 2020 15:15:34 GMT

I am needing some assistance with the following issue with a field with multiple Values.
Since this is coming from a database input I am having some issues trying to get this fixed.

I have a field that looks like this:
Datacenter=FHD (1) | TDC (1) | SDC (1)

I cannot do any functions with this since they show up as text.

Is there a way I can break out the Datacenter values to individual Fields to look like below. Or at the very least be able to calculate the numbers inside the "()"

Datacenter_FHD=1
Datacenter_TDC=1
Datacenter_SDC=1

Thanks for any help on this.
matt4321

Re: Convert Multiple Values in single field to multiple fields

cmerriman — Mon, 07 Aug 2017 15:55:00 GMT

you could write a regex statement to break it out:

|rex field=fieldName "FHD\s\((?<DC_FHD>\d+).*TDC\s\((?<DC_TDC>\d+).*SDC\s\((?<DC_SDC>\d+)"

that should get you three field names with the values inside parens.

Re: Convert Multiple Values in single field to multiple fields

niketn — Mon, 07 Aug 2017 15:57:55 GMT

@matt4321, you can try the following run anywhere search to come up with the query/regular expression you need.

| makeresults
| eval Datacenter="FHD (1) | TDC (1) | SDC (1)"
| eval Datacenter=trim(split(Datacenter,"|"))
| mvexpand Datacenter
| rex field=Datacenter "(?<Name>[^\(]+)\((?<Count>[^\)]+)\)"
| eval Name="Datacenter_".Name
| table Name Count

First two pipes are used to mimic the data as per your example. split() function is used to create multivalue field based on pipe separator (|). The mvexpand command is used to create three single value fields. Finally, rexfield is used to extract the field name and value using regular expression as Name and Count respectively.
Your actual regular expression may change based on the data. Please test and change accordingly. You can use regex101.com to validate the same.

Re: Convert Multiple Values in single field to multiple fields

somesoni2 — Mon, 07 Aug 2017 15:58:19 GMT

Are the values fixed? If yes, will they always available in the same order?

Re: Convert Multiple Values in single field to multiple fields

matt4321 — Mon, 07 Aug 2017 16:11:58 GMT

Unfortunately no.. there are even some other values that are in other events in the Datacenter field. I would love to be able to build a dynamic search on these. Also I have one other similar field that has multiple values formatted similarly but with Status values.

Re: Convert Multiple Values in single field to multiple fields

woodcock — Mon, 07 Aug 2017 22:57:31 GMT

Like this:

| makeresults
| eval Datacenter="FHD (1) | TDC (3) | SDC (2)"
| streamstats count AS _serial

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| makemv Delim=" | " Datacenter
| mvexpand Datacenter
| rex field=Datacenter "^(?<Key>\S+)\s+\((?<Value>.*)\)$"
| eval Datacenter_{Key} = Value
| fields - Datacenter Key Value

At this point you should do your filtering logic on the new fields and if you need to merge back, you can add this:

| stats values(*) AS * BY _serial

Re: Convert Multiple Values in single field to multiple fields

matt4321 — Tue, 08 Aug 2017 02:47:31 GMT

This actually worked out very well for me. Thank you very much.

Re: Convert Multiple Values in single field to multiple fields

matt4321 — Tue, 08 Aug 2017 02:48:29 GMT

This also worked out very well.. Would you know if this is possible to put into transforms.conf and props.conf? if so any suggestions on how that would look?