topic Re: Extract multiple values from a single existing field, using regex in Splunk Search

Extract multiple values from a single existing field, using regex

hketer — Wed, 08 May 2019 11:03:20 GMT

Hey,
I have this event.
as you can see there is field named cs1.
I need to create new field lets say cs_1 and extract the values

FTP - using unsecured file transfer
FTP plaintext user & password

output for cs_1 will be

                 cs_1

FTP - using unsecured file transfer

FTP plaintext user & password

please assist:)

Re: Extract multiple values from a single existing field, using regex

koshyk — Wed, 08 May 2019 11:08:20 GMT

any chance you can paste the sample data (rather than image)

Re: Extract multiple values from a single existing field, using regex

hketer — Wed, 30 Sep 2020 00:23:08 GMT

04-29-2019 11:03:53 Local7.Debug CEF:0||6.5.7|A12755045326585428785|black policy|1|app=ftp cnt=2 cs1=['5050447193422847;;FTP - using unsecured file transfer;;;;;;', '2742510939747842;;FTP plaintext user & password;;;;;;'] cs1Label=reason cs2=['server_to_client', 'client_to_server'] cs2Label=direction deviceExternalId=Sensor dmac= dpt=3592 dst= end=1556525018000 proto=TCP smac= spt=21 src= start=1556525018000

Re: Extract multiple values from a single existing field, using regex

koshyk — Wed, 08 May 2019 11:19:25 GMT

Please try

|makeresults
|eval my_raw="04-29-2019 11:03:53 Local7.Debug xx CEF:0 [somehost] 6.5.7|a12345|black plicy|1|app=ftp cs1=['50512345;;FTP - using unsecured file transfer;;;;;;','272510;;FTP plaintext user & password;;;;;;'] cs1Label=Reason ]"
| rex field=my_raw "\[\'.+?\;\;(?<cs_1_ftpA>FTP[^\;]+).+?(?<cs_1_ftpB>FTP[^\;]+)"

Regex101 demo => https://regex101.com/r/azZv9k/1/

Also if you need to club the messages into a single Key

|makeresults
|eval my_raw="04-29-2019 11:03:53 Local7.Debug xx CEF:0 [somehost] 6.5.7|a12345|black plicy|1|app=ftp cs1=['50512345;;FTP - using unsecured file transfer;;;;;;','272510;;FTP plaintext user & password;;;;;;'] cs1Label=Reason ]"
| rex field=my_raw "\[\'.+?\;\;(?<cs_1_ftpA>FTP[^\;]+).+?(?<cs_1_ftpB>FTP[^\;]+)"
| eval cs1 = cs_1_ftpA + ";" + cs_1_ftpB |makemv delim=";" cs1| table cs1

cheers

Re: Extract multiple values from a single existing field, using regex

DavidHourani — Wed, 08 May 2019 11:21:53 GMT

Hi @hketer,

Extracting a field from another can be done using props and transforms and shown here : https://answers.splunk.com/answers/493083/how-to-transformregex-on-already-extracted-field-w.html

If you don't want to go through this hassle you can use a calculated field which is easier to setup as shown in this answer here : https://answers.splunk.com/answers/177878/how-to-extract-a-field-from-within-the-value-of-an.html

Let me know which part of cs1 you want to extract and I can help you with the regexp if needed.

Cheers,
David

Re: Extract multiple values from a single existing field, using regex

hketer — Wed, 08 May 2019 11:27:38 GMT

I'm trying to extract like you sent in the first link.
Thanks I'll try it again

Re: Extract multiple values from a single existing field, using regex

hketer — Wed, 08 May 2019 11:31:22 GMT

Thank you! I appreciate it!!

I want it to be separate values in the same field, and right now both of them is 1 value

cs1

FTP - using unsecured file transfer

FTP plaintext user & password

FTP - using unsecured file transfer
FTP plaintext user & password

Re: Extract multiple values from a single existing field, using regex

koshyk — Wed, 08 May 2019 11:41:44 GMT

@hketer, how you want it to be separate values? Comma limited? you can just play around with

 | eval cs1 = cs_1_ftpA + ";" + cs_1_ftpB

to fit your purpose

Re: Extract multiple values from a single existing field, using regex

DavidHourani — Wed, 08 May 2019 11:46:50 GMT

Awesome ! Let me know if you hit a stonewall with the config files, happy to help out and feel free to accept the answer if it helped !
Cheers

Re: Extract multiple values from a single existing field, using regex

damann — Wed, 08 May 2019 12:27:20 GMT

What about using max_match=0 in your rex ?

|makeresults
|eval my_raw="04-29-2019 11:03:53 Local7.Debug xx CEF:0 [somehost] 6.5.7|a12345|black plicy|1|app=ftp cs1=['50512345;;FTP - using unsecured file transfer;;;;;;','272510;;FTP plaintext user & password;;;;;;'] cs1Label=Reason ]"
 | rex max_match=0 field=my_raw ";(?<cs_1>FTP[^;]+)"
 | table cs_1

This gives me a nice multivalue Field for cs_1

Re: Extract multiple values from a single existing field, using regex

hketer — Fri, 10 May 2019 14:52:43 GMT

Hi,
Thanks for the answer!
I've tried it and yet its not the result I'm looking for.

the 2 FTP lines are in the same cell and I need it to be separate

Re: Extract multiple values from a single existing field, using regex

woodcock — Fri, 10 May 2019 20:07:09 GMT

Like this:

.... | rex max_match=0 field=my_raw ";(?<cs_1>FTP[^;]+)" 
| table cs_1
| mvexpand cs_1

Re: Extract multiple values from a single existing field, using regex

hketer — Mon, 13 May 2019 08:00:01 GMT

Hi! 🙂
Thank you for your help! I appreciate it!!

I'll explain myself again

I have 4 different events that contain the field:

cs1=['5050447193422847;;FTP - using unsecured file transfer;;;;;;', '2742510939747842;;FTP plaintext user & password;;;;;;']
cs1=['4163302463635179;;SMB2 login failure;;;;;;']
cs1=['4277463150217252;;Stop;;;;;;']
cs1=['5050447193422847;;FTP - using unsecured file transfer;;;;;;']

as you can see in one of the events I have 2 FTP messages in the same field.
I need to extract (not in search) the values to a new field, for example, cs_1
the field now will contain 4 different values

cs1=FTP - using unsecured file transfer
cs1=FTP plaintext user & password
cs1=SMB2 login failure
cs1=Stop

cs1=FTP - using unsecured file transfer
cs1=FTP - using unsecured file transfer , FTP plaintext user & password
cs1=SMB2 login failure
cs1=Stop

haven't decided yet but if someone can help me with one of them it would be amazing!!!! 🙂
Thank YOU!

Re: Extract multiple values from a single existing field, using regex

woodcock — Mon, 13 May 2019 23:42:10 GMT

Like this:

| makeresults 
| eval raw="cs1=['5050447193422847;;FTP - using unsecured file transfer;;;;;;', '2742510939747842;;FTP plaintext user & password;;;;;;']:::cs1=['4163302463635179;;SMB2 login failure;;;;;;']:::cs1=['4277463150217252;;Stop;;;;;;']:::cs1=['5050447193422847;;FTP - using unsecured file transfer;;;;;;']"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| kv
| table cs1

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

| rex field=cs1 mode=sed "s/^\[// s/\]$//"
| makemv delim="', '" cs1
| rex field=cs1 mode=sed "s/^'// s/'$//"
| rex field=cs1 "^(?:[^;]*;){2}(?<cs_1>[^;]+)"