Convert Multiple Values in single field to multiple fields

[matt4321]

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

[woodcock · Accepted solution]

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

[matt4321]

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

[somesoni2]

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

[matt4321]

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.

[niketn]

@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.

[cmerriman]

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.

[matt4321]

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?