topic How do I extract these 2 fields from my sample multiline event? in Splunk Search

How do I extract these 2 fields from my sample multiline event?

shaker_ali — Tue, 29 Sep 2020 08:48:55 GMT

Hi Guys,

I would like to be able to extract fields from the sample log below. In bold I have highlighted IP address and user ID that are listed, and with normal field extraction, Splunk will only take the first value and ignore the rest. I have tried other suggestions (using regex) from similar threads, but wasn't able to extract.

Jan 03 12:51:29 10.133.xxx.xxx Feb 16 12:35:49 host01 guard_sender[7071]: #015#012#015#012Alert Name: PruAlert_03.#015#012Alert Description: CA: Suspicious Database Activity, App User, UPDATE on Customer Master.#015#012#015#012Alert Classification: APPNAME #015#012Category: APPNAME #015#012Severity: LOW#015#012Query period: 3/1/15 - 3/1/15 #015#012#012#012Alert Details#012Timestamp Client IP DB User Name App User Name Total Records Affected SQL Verb Object Name #0122015-01-16 12:34:54.0 10.133.55.121 USERXYZ1 10 UPDATE cdb_address #0122015-01-16 12:33:53.0 10.133.55.122 USERXYZ2 10 UPDATE cdb_address #0122015-01-16 12:32:54.0 10.133.55.131 USERXYZ2 10 UPDATE cdb_address #0122015-01-16 12:32:51.0 10.133.55.132 USERXYZ1 10 UPDATE cdb_address

The above log varies from event to event. Some contain one value while some contains ten values (IP and username). The same log when put in notepad and adjusted will be like a table, but in the results its shown as above.

Timestamp Client IP DB User Name App User Name Total Records Affected SQL Verb Object Name

0122015-01-16 12:34:54.0 10.133.55.121 USERXYZ1 10 UPDATE cdb_address

0122015-01-16 12:33:53.0 10.133.55.122 USERXYZ2 10 UPDATE cdb_address

0122015-01-16 12:32:54.0 10.133.55.131 USERXYZ2 10 UPDATE cdb_address

0122015-01-16 12:32:51.0 10.133.55.132 USERXYZ1 10 UPDATE cdb_address

i would like to extract Username and IP_add, either in-search regex or
any suggestions?

Re: How do I extract these 2 fields from my sample multiline event?

jmallorquin — Tue, 16 Feb 2016 16:08:00 GMT

First you should use LINE_BREAKER just to split the one line log to events.

Re: How do I extract these 2 fields from my sample multiline event?

somesoni2 — Tue, 29 Sep 2020 08:49:21 GMT

Try something like this
For search (inline)

your base search | rex max_match=0 "#\d+-\d+-\d+\s+\d+:\d+:\d+\.\d+\s+(?<Client_IP>\d+\.\d+\.\d+\.\d+)\s+(?<DB_User_Name>\S+)"

This will create a multivalued field for Client_IP and DB_User_Name.

Saved same in conf files. (On Search Head)

props.conf

[yourSourceType]
...other configurations..
REPORT-fields = extract_ip_user

transforms.conf

[extract_ip_user]
REGEX = #\d+-\d+-\d+\s+\d+:\d+:\d+\.\d+\s+(?<Client_IP>\d+\.\d+\.\d+\.\d+)\s+(?<DB_User_Name>\S+)
MV_ADD = true

Re: How do I extract these 2 fields from my sample multiline event?

shaker_ali — Wed, 17 Feb 2016 07:43:25 GMT

Thank you. This works! 🙂