How can I split up values in a field to create new fields? New field names should be extracted from original field.

[eraser]

I've imported a csv file and one of the fields called "Tags" looks like this:

Tags=

"avd:vm, dept:support services, cm-resource-parent:/subscriptions/e9674c3a-f9f8-85cc-b457-94cf0fbd9715/resourcegroups/avd-standard-pool-rg/providers/microsoft.desktopvirtualization/hostpools/avd_standard_pool_1, manager:JohnDoe@email.com"

I'd like to split each of these tags up into their own field/value, AND extract the first part of the tag as the field name.

Result of new fields/values would look like this:

avd="vm"

dept="support services"

cm-resource-parent="/subscriptions/e9674c3a-f9f8-85cc-b457-94cf0fbd9715/resourcegroups/avd-standard-pool-rg/providers/microsoft.desktopvirtualization/hostpools/avd_standard_pool_1"

manager="JohnDoe@email.com"

I've looked at a lot of examples with rex, MV commands, etc, but nothing that pulls the new field name out of the original field.

The format of that Tags field is always the same as listed above, for all events.

Thank you!

[ITWhisperer]

| rex max_match=0 field=tags "(?<namevalue>[^:, ]+:[^, ]+)"
| mvexpand namevalue
| rex field=namevalue "(?<name>[^:]+):(?<value>.*)"
| eval {name}=value

[eraser]

Thanks - this is very close to what I'm looking for (I do want to perform this extraction at search time), but may need a couple tweaks.

1) All of the dept's have a space in them (some more than one)and the rex is only picking up the first word of that dept. Examples: "support services", "xyz operations r&d"

2) Also - when I look into each event to see that the Tags fields are extracted,  only one actually gets extracted. But it's not the same one each time?? The "name" and "namevalue" fields match the one field that does get extracted.

Hope that makes sense?

[ITWhisperer · Accepted solution]

Try this

| rex max_match=0 field=tags "(?<namevalue>[^:,]+:[^, ]+)"
| mvexpand namevalue
| rex field=namevalue "(?<name>[^:]+):(?<value>.*)"
| eval {name}=value

[eraser]

Here's what I ended up doing, seems to work!

| rex max_match=0 field=Tags "(?<namevalue>[^:, ]+:[^,]+)" 
| mvexpand namevalue 
| rex field=namevalue "(?<name>[^:]+):(?<value>.*)" 
| eval {name}=value 

The confusion about seeing only one of the fields being extracted was a result of the mvexpand. I didn't realize that created NEW events, one for each field. Makes sense now...thank you!

[PickleRick]

It depends whether we're talking about configuring extractions in transforms or trying to do it with search commands.

With configured extractions you just need to capture two groups - one for the field name, another for value and either use $1::$2 for format if using unnamed groups or name them _KEY_1 and _VAL_1 respectively if using named groups.

If you want to do that in SPL you need to use the {} notation. Like

| eval {fieldname}=fieldvalue

Where fieldname is a field containing your target field name.

Most probably you'll want to split your input into key:value chunks as multivalued field, then use foreach to iterate over those chunks and split them into final key-value pairs and use the {key} notation to define the output field.

[jawahir007]

Try this one :

<your_search>| rex field=Tags "avd:(?<avd>[^,]+),\s*dept:(?<dept>[^,]+),\s*cm-resource-parent:(?<cm_resource_parent>[^,]+),\s*manager:(?<manager>[^$]+)"

------

If you find this solution helpful, please consider accepting it and awarding karma points !!