topic Re: Trying to extract the value of a field which occurs twice in one event. Regex maybe? in Splunk Search

Trying to extract the value of a field which occurs twice in one event. Regex maybe?

rob_gibson — Tue, 29 Sep 2020 14:08:23 GMT

I am hopeful someone has a suggestion for this reporting issue.

I have an event generated by Microsoft SQL Audit, which is being written to the Windows:Security log on the forwarder. I need to create a search string which captures the value of the 2nd "Account_Name" field, containing the value "userid". However, "Account_Name" field appears twice in each record. As the field name is not unique, my table is showing the value of both fields concatenated into one string.

Source Data Sample;
Subject:
Account Name: hostname1$
New Logon:
Account Name: userid

Table results displayed for "Account_Name";
hostname1$
userid

The following is sample event data (scrubbed);
LogName=Security
SourceName=Microsoft Windows security auditing.
EventCode=4624
EventType=0
Type=Information
ComputerName=hostname1.network.com
TaskCategory=Logon
OpCode=Info
RecordNumber=559165
Keywords=Audit Success
Message=An account was successfully logged on.

Subject:
Security ID: S-1-5-18
Account Name: hostname1$
Account Domain: MyDomain
Logon ID: 0x3E7

Logon Type: 10

Impersonation Level: Impersonation

New Logon:
Security ID: S-1-5-21-606747145-790525478-839522115-29674
Account Name: userid
Account Domain: MySubDomain
Logon ID: 0x16ABZ9093
Logon GUID: {00000000-0000-0000-0000-000000000000}

Re: Trying to extract the value of a field which occurs twice in one event. Regex maybe?

scottrunyon — Tue, 29 Sep 2020 14:08:31 GMT

Rob,

You can set a the query using the "rex" command and then mvindex using "eval".

Something like this -

| rex field=_raw max_match=5 "Account Name:\s+(?\w+\$?)"
| eval Wanted_ID=mvindex(Account_Name,1)

Note – The “1” in the mvindex returns the second instance of “Account Name”, count starts at 0.

Hope this helps,

Scott

Re: Trying to extract the value of a field which occurs twice in one event. Regex maybe?

rob_gibson — Wed, 24 May 2017 14:47:34 GMT

Thanks Scott,

I'm getting errors trying to add this to my search. I think I'm missing something.
Error in 'rex' command: Encountered the following error while compiling the regex 'Account Name:\s+(?\w+\$?)': Regex: unrecognized character after (? or (?-

In your example is 'Account Name: a reference to my existing duplicate field, and Wanted_ID is a new name for the extracted field value?

Re: Trying to extract the value of a field which occurs twice in one event. Regex maybe?

rob_gibson — Tue, 29 Sep 2020 14:11:38 GMT

Hey I got it to work!

I left out the rex command and just used | eval Account_Name=mvindex(Account_Name,1) | in order to extract the second occurrence of the Account_Name field.

Many thanks Scott!!