topic Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu in Splunk Enterprise

How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splunk

sandeepreddy947 — Thu, 12 Oct 2023 18:18:04 GMT

I need help in regex for key and value to be extracted from raw data, below regex working with xml_kv_extraction. While its working in regex101 but not in splunk with rex, any suggesstions.

<(?<field_header>[^>]+)>(?<field_value>[^<]+)<\/\1>https://regex101.com/r/IBsMhK/1

eg: events with

<field_title><field_header1>field_value1</field_header1><field_header2>field_value2</field_header2></field_title>

Should appear fields as below.

field title = <field_header1>field_value1</field_header1><field_header2>field_value2</field_header2>

field_header1=field_value1

field_header2=field_value2

1997-10-10 15:35:13.046, CREATE_DATE="1997-10-10 13:36:22.742479", LAST_UPDATE_DATE="1997-10-10 13:36:22.74", ACTION="externalFactor", STATUS="info", DATA_STRING="<?xml version="1.0" encoding="UTF-8"?> <externalFactor><current>parker</current><keywordp><encrypted>true</encrypted><keywordp>******</keywordp></keywordp><boriskhan>boriskhan1-CMX_PRTY</boriskhan></externalFactor>" 1997-10-10 15:35:13.046, CREATE_DATE="1997-10-10 13:03:58.388887", LAST_UPDATE_DATE="1997-10-10 13:03:58.388", ACTION="externalFactor.RESPONSE", STATUS="info", DATA_STRING="<?xml version="1.0" encoding="UTF-8"?> <externalFactorReturn><roleName>ROLE.CustomerManager</roleName><roleName>ROLE.DataSteward</roleName><pepres>false</pepres><externalFactor>false</externalFactor><parkeristrator>true</parkeristrator><current>parker</current></externalFactorReturn>" 1997-10-10 15:35:13.046, CREATE_DATE="1997-10-10 13:03:58.384984", LAST_UPDATE_DATE="1997-10-10 13:03:58.384", ACTION="externalFactor.RESPONSE", STATUS="info", DATA_STRING="<?xml version="1.0" encoding="UTF-8"?> <externalFactorReturn><roleName>ROLE.CustomerManager</roleName><roleName>ROLE.DataSteward</roleName><pepres>false</pepres><externalFactor>false</externalFactor><parkeristrator>true</parkeristrator><current>parker</current></externalFactorReturn>" 1997-10-10 15:35:13.046, CREATE_DATE="1997-10-10 13:03:58.384947", LAST_UPDATE_DATE="1997-10-10 13:03:58.384", ACTION="externalFactor.RESPONSE", STATUS="info", DATA_STRING="<?xml version="1.0" encoding="UTF-8"?> <externalFactorReturn><roleName>ROLE.CustomerManager</roleName><roleName>ROLE.DataSteward</roleName><pepres>false</pepres><externalFactor>false</externalFactor><parkeristrator>true</parkeristrator><current>parker</current></externalFactorReturn>" 1997-10-10 15:35:13.046, CREATE_DATE="1997-10-10 13:03:58.378965", LAST_UPDATE_DATE="1997-10-10 13:03:58.378", ACTION="externalFactor", STATUS="info", DATA_STRING="<?xml version="1.0" encoding="UTF-8"?> <externalFactor><current>parker</current><keywordp><encrypted>true</encrypted><keywordp>******</keywordp></keywordp><boriskhan>boriskhan1-CMX_PRTY</boriskhan></externalFactor>" 1997-10-10 15:35:13.046, CREATE_DATE="1997-10-10 13:03:58.374242", LAST_UPDATE_DATE="1997-10-10 13:03:58.373", ACTION="externalFactor", STATUS="info", DATA_STRING="<?xml version="1.0" encoding="UTF-8"?> <externalFactor><current>parker</current><keywordp><encrypted>true</encrypted><keywordp>******</keywordp></keywordp><boriskhan>boriskhan1-CMX_PRTY</boriskhan></externalFactor>" 1997-10-10 15:35:13.046, CREATE_DATE="1997-10-10 13:03:58.374235", LAST_UPDATE_DATE="1997-10-10 13:03:58.373", ACTION="externalFactor", STATUS="info", DATA_STRING="<?xml version="1.0" encoding="UTF-8"?> <externalFactor><current>parker</current><keywordp><encrypted>true</encrypted><keywordp>******</keywordp></keywordp><boriskhan>boriskhan1-CMX_PRTY</boriskhan></externalFactor>" 1997-10-10 15:35:13.046, CREATE_DATE="1997-10-10 13:03:58.325953", LAST_UPDATE_DATE="1997-10-10 13:03:58.325", ACTION="externalFactor.RESPONSE", STATUS="info", DATA_STRING="<?xml version="1.0" encoding="UTF-8"?> <externalFactorReturn><roleName>ROLE.CustomerManager</roleName><roleName>ROLE.DataSteward</roleName><pepres>false</pepres><externalFactor>false</externalFactor><parkeristrator>true</parkeristrator><current>parker</current></externalFactorReturn>"

@priit @PriA @yonmost @jameshgibson @bnikhil0584

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

richgalloway — Thu, 12 Oct 2023 18:38:27 GMT

Where in Splunk are you using this regex? If it's in transforms.conf then please share the full stanza.

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

sandeepreddy947 — Thu, 12 Oct 2023 18:44:22 GMT

i'm trying to find right regex for Splunk search time extraction first, via Splunk GUI.

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

sandeepreddy947 — Thu, 12 Oct 2023 18:51:54 GMT

I have regex in description and also url from regex101.com. its not working in Splunk when i used rex with SPL query.

index=universe sourcetype=planet | rex field=_raw "<(?<key>[^>]+)> (?<value>[^<]+)<\/\1>"

Results i got,

key

current
encrypted
keywordp
boriskhan
rolename
.
.

value

parker
true
******
role.customermanager
false
.
.
.

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

sandeepreddy947 — Thu, 12 Oct 2023 18:55:07 GMT

Result i need from raw data is,

current=parker
encrypted=true
keywordp=******
boriskhan=boriskhan1-cmx_prty
rolename=role.customermanager
.
.
.
.

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

PickleRick — Fri, 13 Oct 2023 06:03:39 GMT

You need to extract special capture groups from each match called _KEY_1 and _VAL_1

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

richgalloway — Fri, 13 Oct 2023 12:17:50 GMT

See if this run-anywhere example query helps.

| makeresults | eval data="1997-10-10 15:35:13.046, CREATE_DATE=\"1997-10-10 13:36:22.742479\", LAST_UPDATE_DATE=\"1997-10-10 13:36:22.74\", ACTION=\"externalFactor\", STATUS=\"info\", DATA_STRING=\"<?xml version=\"1.0\" encoding=\"UTF-8\"?><externalFactor><current>parker</current><keywordp><encrypted>true</encrypted><keywordp>******</keywordp></keywordp><boriskhan>boriskhan1-CMX_PRTY</boriskhan></externalFactor>\" 1997-10-10 15:35:13.046, CREATE_DATE=\"1997-10-10 13:03:58.388887\", LAST_UPDATE_DATE=\"1997-10-10 13:03:58.388\", ACTION=\"externalFactor.RESPONSE\", STATUS=\"info\", DATA_STRING=\"<?xml version=\"1.0\" encoding=\"UTF-8\"?><externalFactorReturn><roleName>ROLE.CustomerManager</roleName><roleName>ROLE.DataSteward</roleName><pepres>false</pepres><externalFactor>false</externalFactor><parkeristrator>true</parkeristrator><current>parker</current></externalFactorReturn>" | eval data=split(data," ") | mvexpand data | eval _raw=data | fields - data ``` Everything above sets up demo data. Delete IRL ``` ``` Extract keys and values ``` | rex max_match=0 "<(?<key>[^>]+)>(?<value>[^<]+)<\/\1>" ``` Match keys and values so they stayed paired during mvexpand ``` | eval pairs=mvzip(key,value) | mvexpand pairs ``` Separate key from value ``` | eval pairs=split(pairs,",") ``` Define key=value result ``` | eval pairs=mvindex(pairs,0) . "=" . mvindex(pairs,1) | table pairs

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

sandeepreddy947 — Mon, 16 Oct 2023 13:13:41 GMT

Its great, i have all key as in field_header and all valuesas in field_value assigned to a field called paris. So, here all key and value extracted in one field called paris. But, i'm trying to extract field headers and field values from raw data. As to search for SPL query "index=index_name sourcetype=sourcetype |table current, encrypted, keywordp,boriskhan, rolename, prepres, externalFactor, parkeristrator".

so, what i got is field_header as paris and field_values as current=parker, encrypted=true, . . . .

paris

current=parker

encrypted=true

keywordp=******

boriskhan=1-CMX_PRTY

roleName=ROLE.CustomerManager

pepres=false

externalFactor=false

parkeristrator=true

current=parker

What goal is, field_header_1 is current, field_header_2 is encrypted, field_header_3 is keyword as below and to get field_headers showup in Splunk interesting fields with its values.

current	encrypted	keywordp	boriskhan	roleName	pepres	externalFactor
parker	true	******	1-CMX_PRTY	ROLE.CustomerManager	false	false

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

richgalloway — Mon, 16 Oct 2023 14:26:17 GMT

Try this query. The transpose command "rotates" the results table into the desired format.

| makeresults | eval data="1997-10-10 15:35:13.046, CREATE_DATE=\"1997-10-10 13:36:22.742479\", LAST_UPDATE_DATE=\"1997-10-10 13:36:22.74\", ACTION=\"externalFactor\", STATUS=\"info\", DATA_STRING=\"<?xml version=\"1.0\" encoding=\"UTF-8\"?><externalFactor><current>parker</current><keywordp><encrypted>true</encrypted><keywordp>******</keywordp></keywordp><boriskhan>boriskhan1-CMX_PRTY</boriskhan></externalFactor>\" 1997-10-10 15:35:13.046, CREATE_DATE=\"1997-10-10 13:03:58.388887\", LAST_UPDATE_DATE=\"1997-10-10 13:03:58.388\", ACTION=\"externalFactor.RESPONSE\", STATUS=\"info\", DATA_STRING=\"<?xml version=\"1.0\" encoding=\"UTF-8\"?><externalFactorReturn><roleName>ROLE.CustomerManager</roleName><roleName>ROLE.DataSteward</roleName><pepres>false</pepres><externalFactor>false</externalFactor><parkeristrator>true</parkeristrator><current>parker</current></externalFactorReturn>" | eval data=split(data," ") | mvexpand data | eval _raw=data | fields - data ``` Everything above sets up demo data. Delete IRL ``` ``` Extract keys and values ``` | rex max_match=0 "<(?<key>[^>]+)>(?<value>[^<]+)<\/\1>" ``` Match keys and values so they stayed paired during mvexpand ``` | eval pairs=mvzip(key,value) | mvexpand pairs ``` Separate key from value ``` | eval pairs=split(pairs,",") ``` Define key=value result ``` | eval key=mvindex(pairs,0), value=mvindex(pairs,1) | fields key,value | fields - _* | transpose 0 header_field=key

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

sandeepreddy947 — Tue, 17 Oct 2023 16:14:48 GMT

But, i'm still not able to view extracted field_header properly in interesting fields. i can only view "key" and "value" in interesting fields. I'm searching in verbose mode.

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

richgalloway — Tue, 17 Oct 2023 16:45:13 GMT

Only the key and value fields are visible because that is what the fields command does. If you prefer different names then change "key" to "field_header" throughout the query. Likewise for "value".

Re: How do we extract field header to a value that changes and assigns to new value. Working in regex101 but not in Splu

sandeepreddy947 — Thu, 19 Oct 2023 14:17:38 GMT

I've tried identifying all individual fields in events and extracted using rex.

| rex "\s\<externalFactor\>(?<externalFactor>.*)\<\/externalFactor\>" | rex "\s\<externalFactorReturn\>(?<externalFactorReturn>.*)\<\/externalFactorReturn\>" | rex "\<current\>(?<current>.*)\<\/current\>" | rex "\<encrypted\>(?<encrypted>.*)\<\/encrypted\>" | rex "\<keywordp\>(?<keywordp>.*)\<\/keywordp\>" | rex "\<pepres\>(?<pepres>.*)\<\/pepres\>" | rex "\<roleName\>(?<roleName>.*)\<\/roleName\>" | rex "\<boriskhan\>(?<boriskhan>.*)\<\/boriskhan\>" | rex "\<sload\>(?<sload>.*)\<\/sload\>" | rex "\<externalFactor\>(?<externalFactor>.*)\<\/externalFactor\>" | rex "\<parkeristrator\>(?<parkeristrator>.*)\<\/parkeristrator\>"