How to extract Splunk rex field?

[GRC]

Hi There, 

I have a query that I use to extract all database modifications. However, I want to exclude SELECT from capturing via this query. I want to extract only INSERT, DELETE, UPDATE. 

My Query:

index="database_db" source=database_audit sourcetype="database_audit"

| eval "Database Modifications:" = "Modification on " + host, "Date and Time" = EXTENDED_TIMESTAMP_NY, "Type" = SQL_TEXT, "User" = DB_USER , "Source" = sourcetype

| rex field=_raw "SQL_TEXT=\S(?P<Type>\W?......)\s"
| rex field=_raw "DB_USER=(?P<UserName>..........)"

| table "Date and Time", "Database Modifications:" ,"Type", "User", "Source"

Can anybody help ?

Thank you.

[GRC]

Hi @IZ88 Thank you so much for the help. 😀

[GRC]

Hi @IZ88 ,

I hope you are doing really well and thank you for helping me solve my previous issues. I now learnt how to build up regex queries on my own after your explanations and analysis of the queries you built for me, a huge thank you for that. 

I have another issue now, which I hope you would help me get solved. 

I have 2 separate queries that I built using Rex.

1. This query captures the logg on and logg off status of the service.

Query:

index=windows_log host=abc-05-hiddencam logged*

| rex field=_raw  "(?<Date>\w{3}\s+\d+ \d+:\d+:\d+)\s(?<hostname>\w+-\w+-\w+).+Audit\S+\s\w+\s\w+\s(?<status>.+).\s\s\s\sSub.*"

| eval "Hidden Cam Monitoring" = Date + " : " + hostname + " " + status
| table "Hidden Cam Monitoring"

1. Sample Output:

Dec 10 13:35:12 : abc-05-hiddencam successfully logged on

Dec 10 06:19:24 : abc-05-hiddencam successfully logged on

Dec 10 06:17:01 : abc-05-hiddencam logged off

Dec 10 06:11:55 : abc-05-hiddencam logged off

2. This query captures the service entering the start or stop status.

Query:

index=windows_log host=abc-05-hiddencam entered*

| rex field=_raw "(?<Date>\w{3}\s+\d+ \d+:\d+:\d+)\s(?<hostname>\w+-\d+-\w+).*(?<status>service\s\w+\s\w+\s\w+\s\w+)"

| eval "Hidden Cam Monitoring" = Date + " : " + hostname + " " + status

| table "Hidden Cam Monitoring"

2. Sample Output:

Dec 10 16:10:04 : abc-05-hiddencam service entered the stopped state

Dec 10 15:31:31 : abc-05-hiddencam service entered the stopped state

Dec 10 15:28:19 : abc-05-hiddencam service entered the running state

Dec 10 15:28:18 : abc-05-hiddencam service entered the running state

My issue is, I want to combine above queries into a single query and get an output in a table as shown below.

3. Expected sample results:

Dec 10 13:35:12 : abc-05-hiddencam successfully logged on

Dec 10 16:10:04 : abc-05-hiddencam service entered the stopped state

Dec 10 06:19:24 : abc-05-hiddencam successfully logged on

Dec 10 15:28:18 : abc-05-hiddencam service entered the running state

Dec 10 06:17:01 : abc-05-hiddencam logged off

Dec 10 15:28:19 : abc-05-hiddencam service entered the running state

Dec 10 06:11:55 : abc-05-hiddencam logged off

Dec 10 15:31:31 : abc-05-hiddencam service entered the stopped state

( The results are going to be different to above based on the timestamp and the events. What I mean here is the results come mixing together in a single table as and when they take place.)

Thank you heaps in advance. 

[IZ88]

Hi @GRC,

There are 2 way you can approach this task:

1. Build your REX filter so it will take into account the type of event you're looking at - add the "logged" or "entered" as part of your regex.

2. Keep both you searches and add the append command between them. This option is easier to implement, but will take a bit longer to execute since you'll be running two searches.

[GRC]

Hi @IZ88 ,

I created the query as below and got the results.

(index=windows_log host=abc-05-hiddencam entered*) OR (index=windows_log host= abc-05-hiddencam logged*)

| rex field=_raw "(?<Date>\w{3}\s+\d+ \d+:\d+:\d+)\s(?<hostname>\w+-\d+-\w+).*(?<status>service\s\w+\s\w+\s\w+\s\w+)"

| rex field=_raw "(?<Date>\w{3}\s+\d+ \d+:\d+:\d+)\s(?<hostname>\w+-\w+-\w+).+Audit\S+\s\w+\s\w+\s(?<status>.+).\s\s\s\sSub.*"

| eval "Hidden Cam Monitoring" = Date + " : " + hostname + " " + status

| table "Hidden Cam Monitoring"

The query executes and gives the results in one table. 

1st Regex data: 

Dec 14 20:31:05 abc-05-hiddencam MSWinEventLog#0111#011System#011622650#011Tue Dec 14 20:31:05 2021#0117036#011Service Control Manager#011N/A#011N/A#011Information#011abc-05-hiddencam#011None#011#011The Google Update Service (gupdate) service entered the stopped state.#01176625

Dec 14 20:27:53 abc-05-hiddencam MSWinEventLog#0111#011System#011622648#011Tue Dec 14 20:27:53 2021#0117036#011Service Control Manager#011N/A#011N/A#011Information#011abc-05-hiddencam#011None#011#011The WinHTTP Web Proxy Auto-Discovery Service service entered the running state.#01176624

2nd Regex data:

Dec 13 09:03:18 abc-05-hiddencam MSWinEventLog#0111#011Security#011620683#011Mon Dec 13 09:03:16 2021#0114634#011Microsoft-Windows-Security-Auditing#011abc-05-hiddencam\Alis#011N/A#011Success Audit#011abc-05-hiddencam#011Logoff#011#011An account was logged off.    Subject:   Security ID:  S-1-5-21-193828115-2933347444-2245271187-1049   Account Name:  Alis   Account Domain:  abc-05-hiddencam   Logon ID:  0x8ac469ffa    Logon Type:   10    This event is generated when a logon session is destroyed. It may be positively correlated with a logon event using the Logon ID value. Logon IDs are only unique between reboots on the same computer.#011304150

Dec 13 09:00:49 abc-05-hiddencam MSWinEventLog#0111#011Security#011620638#011Mon Dec 13 09:00:47 2021#0114624#011Microsoft-Windows-Security-Auditing#011abc-05-hiddencam\Alis#011N/A#011Success Audit#011abc-05-hiddencam#011Logon#011#011An account was successfully logged on.    Subject:   Security ID:  S-1-5-18   Account Name:  abc-05-hiddencam   Account Domain:  WORKGROUP   Logon ID:  0x3e7    Logon Type:   10    New Logon:   Security ID:  S-1-5-21-193828115-2933347444-2245271187-1049   Account Name:  Alis   Account Domain:

Now the only issue is when I amend the second Regex to capture the user as mentioned below:

| rex field=_raw "(?<Date>\w{3}\s+\d+ \d+:\d+:\d+)\s(?<hostname>\w+-\w+-\w+).+Audit\S+\s\w+\s\w+\s(?<status>.+).\s\s\s\sSub.+Account\sName\S+\s+(?<user>\w+).+"

And when I add "user" to the eval and table, the table does not extract the queries related to the first regex.

Appreciate if you can suggest me a way. 

Thank you 

[IZ88]

Hi @GRC ,

Please see my query below (based on your data):

| makeresults count=2
| streamstats count
| eval _raw=if(count=1,"Dec 14 20:31:05 abc-05-hiddencam MSWinEventLog#0111#011System#011622650#011Tue Dec 14 20:31:05 2021#0117036#011Service Control Manager#011N/A#011N/A#011Information#011abc-05-hiddencam#011None#011#011The Google Update Service (gupdate) service entered the stopped state.#01176625","Dec 13 09:03:18 abc-05-hiddencam MSWinEventLog#0111#011Security#011620683#011Mon Dec 13 09:03:16 2021#0114634#011Microsoft-Windows-Security-Auditing#011abc-05-hiddencam\Alis#011N/A#011Success Audit#011abc-05-hiddencam#011Logoff#011#011An account was logged off. Subject: Security ID: S-1-5-21-193828115-2933347444-2245271187-1049 Account Name: Alis Account Domain: abc-05-hiddencam Logon ID: 0x8ac469ffa Logon Type: 10 This event is generated when a logon session is destroyed. It may be positively correlated with a logon event using the Logon ID value. Logon IDs are only unique between reboots on the same computer.#011304150")
| rex field=_raw "(?<Date>\w{3}\s+\d+ \d+:\d+:\d+)\s(?<hostname>\w+-\d+-\w+).*(?<status>service\s\w+\s\w+\s\w+\s\w+)"
| rex field=_raw "(?<Date>\w{3}\s+\d+ \d+:\d+:\d+)\s(?<hostname>\w+-\w+-\w+).+Audit\S+\s\w+\s\w+\s(?<status>.+).\s\s\s\sSub.+Account\sName\S+\s+(?<user>\w+).+"
| eval "Hidden Cam Monitoring" = Date + " : " + hostname + " " + status + if(isnotnull(user)," "+user,"")
| table "Hidden Cam Monitoring"

If you'll notice, I've added an if clause to the eval function. The reason is that when trying to eval a field based on a filed that doesn't exist in the data, the eval will fail and you'll end up with empty field.

[GRC]

Hi @IZ88 champion,

I hope you can help me. 

I run the below 1,2,3 queries on the given datasets to find out which users ran the enable command on which host at what time:

1. index= networking user* enable* host*

Oct 15 08:17:45 brg-c-1.com.au 8279: Oct 15 2021 08:17:44.820 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:John logged command:!exec: enable
Oct 15 08:17:35 brg-c-1.com.au 8278: Oct 15 2021 08:17:34.082 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:lili logged command:!exec: enable failed
Sep 15 23:29:55 gsw-r-4.com.au 466: Sep 15 23:29:54.009: %PARSER-5-CFGLOG_LOGGEDCMD: User:Khan logged command:!exec: enable
Aug 12 15:18:37 edc-r-4.com.au 02: Aug 12 15:18:36.472: %PARSER-5-CFGLOG_LOGGEDCMD: User:Khan logged command:!exec: enable
Aug 11 03:31:05 ctc-s.com.au 134: Aug 10 17:31:04.859: %PARSER-5-CFGLOG_LOGGEDCMD: User:cijs logged command:!exec: enable
Jan 29 11:30:58 brg-c-1.com.au 2082: Jan 29 2021 11:30:57.141 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:chick logged command:!exec: enable failed

2. index=linux_logs host=edc-03-tacacs enable*

Oct 26 12:56:13 egc-03-ts tc_plus[149]: enable query for 'kim' tty86 from 202.168.5.22 accepted
Oct 26 11:33:44 egc-03-ts tc_plus[259]: enable query for 'kim' tty86 from 202.168.5.22 accepted
Oct 21 11:35:59 egc-03-ts tc_plus[285]: enable query for 'John' tty86 from 202.168.5.23 accepted
Oct 21 11:35:53 egc-03-ts tc_plus[282]: enable query for 'Han' tty86 from 202.168.5.23 rejected

3. index=linux_logs host=gsw-03-tacacs enable*

Sep 30 13:35:53 gdw-02-ts tc_plus[143]: 192.168.2.21 James tty1 192.168.6.56 stop task_id=55161 timezone=AEST service=shell start_time=1632972953 priv-lvl=0 cmd=enable
Sep 29 12:38:17 gdw-02-ts tc_plus[319]: 192.168.2.24 linda tty1 192.168.5.3 stop task_id=15729 timezone=AEST service=shell start_time=1632883097 priv-lvl=0 cmd=enable
Sep 15 22:23:23 gdw-02-ts tc_plus[1649]: 192.168.4.2 Brown tty322 192.168.46.1 stop task_id=2574 timezone=AEST service=shell start_time=1631708603 priv-lvl=0 cmd=enable
Sep 9 14:58:32 gdw-02-ts tc_plus[2030]: 192.168.2.29 Gordan tty1 192.168.26.3 stop task_id=14329 timezone=AEST service=shell start_time=1631163512 priv-lvl=0 cmd=enable

I tried hard but could not find a query to merge all these data (indexes and hosts) to find out who ran enable command successfully at what time on which host. And get those results to a table look like

|table date host user command(enable) status(success)

Could anyone please help me ?

Thank you in advance.

[GRC]

Hi @IZ88 ,

I tried to capture the server names from below data: 

Oct 15 08:17:45 brg-c-1.com.au 8279: Oct 15 2021 08:17:44.820 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:John logged command:!exec: enable
Oct 15 08:17:35 brg-c-1.com.au 8278: Oct 15 2021 08:17:34.082 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:lili logged command:!exec: enable failed
Aug 11 03:31:05 ctc-s.com.au 134: Aug 10 17:31:04.859: %PARSER-5-CFGLOG_LOGGEDCMD: User:cijs logged command:!exec: enable

with the following rex command, but it won't work, can you please help me to see what is wrong ?

| rex field=_raw "\/(?<hostname>[^_\/]+)[\w\.]+$"

Thank you

[IZ88]

Hi @GRC 

The problem seems to be with your regex. 

If all your logs follow the same pattern, the line below should capture what you want:

| rex field=_raw ":\d+ (?<hostname>.+) \d+: \w+"

Basically, what you put after the <field_name> defines what the regex should catch.  Any thing outside the () represents the data surrounding your desired extraction .

So if the pattern you're looking for repeat several times within the field you work on, it would be good practice to provide some regex\precise phrases for the surrounding data, to make sure you capture exactly what you need.

[GRC]

Hi @IZ88,

Could you please help me ? I really appreciate. 

I run the below 1,2,3 queries on the given datasets to find out which users ran the enable command on which host at what time:

1. index= networking user* enable* host*

Oct 15 08:17:45 brg-c-1.com.au 8279: Oct 15 2021 08:17:44.820 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:John logged command:!exec: enable
Oct 15 08:17:35 brg-c-1.com.au 8278: Oct 15 2021 08:17:34.082 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:lili logged command:!exec: enable failed
Sep 15 23:29:55 gsw-r-4.com.au 466: Sep 15 23:29:54.009: %PARSER-5-CFGLOG_LOGGEDCMD: User:Khan logged command:!exec: enable
Aug 12 15:18:37 edc-r-4.com.au 02: Aug 12 15:18:36.472: %PARSER-5-CFGLOG_LOGGEDCMD: User:Khan logged command:!exec: enable
Aug 11 03:31:05 ctc-s.com.au 134: Aug 10 17:31:04.859: %PARSER-5-CFGLOG_LOGGEDCMD: User:cijs logged command:!exec: enable
Jan 29 11:30:58 brg-c-1.com.au 2082: Jan 29 2021 11:30:57.141 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:chick logged command:!exec: enable failed

2. index=linux_logs host=edc-03-tacacs enable*

Oct 26 12:56:13 egc-03-ts tc_plus[149]: enable query for 'kim' tty86 from 202.168.5.22 accepted
Oct 26 11:33:44 egc-03-ts tc_plus[259]: enable query for 'kim' tty86 from 202.168.5.22 accepted
Oct 21 11:35:59 egc-03-ts tc_plus[285]: enable query for 'John' tty86 from 202.168.5.23 accepted
Oct 21 11:35:53 egc-03-ts tc_plus[282]: enable query for 'Han' tty86 from 202.168.5.23 rejected

3. index=linux_logs host=gsw-03-tacacs enable*

Sep 30 13:35:53 gdw-02-ts tc_plus[143]: 192.168.2.21 James tty1 192.168.6.56 stop task_id=55161 timezone=AEST service=shell start_time=1632972953 priv-lvl=0 cmd=enable
Sep 29 12:38:17 gdw-02-ts tc_plus[319]: 192.168.2.24 linda tty1 192.168.5.3 stop task_id=15729 timezone=AEST service=shell start_time=1632883097 priv-lvl=0 cmd=enable
Sep 15 22:23:23 gdw-02-ts tc_plus[1649]: 192.168.4.2 Brown tty322 192.168.46.1 stop task_id=2574 timezone=AEST service=shell start_time=1631708603 priv-lvl=0 cmd=enable
Sep 9 14:58:32 gdw-02-ts tc_plus[2030]: 192.168.2.29 Gordan tty1 192.168.26.3 stop task_id=14329 timezone=AEST service=shell start_time=1631163512 priv-lvl=0 cmd=enable

I tried hard but could not find a query to merge all these data (indexes and hosts) to find out who ran enable command successfully at what time on which host. And get those results to a table look like

|table date host user command(enable) status(success)

Thank you

[IZ88]

Hi @GRC 

If you wish to query multiple indexes you can you the "append" command.

The syntax will be:

index= networking user* enable* host*
| append [search index=linux_logs host=edc-03-tacacs enable*]
| append [search index=linux_logs host=gsw-03-tacacs enable*]

Or, you can combine the 2 appends since they search the same index

index= networking user* enable* host*
| append [search index=linux_logs (host=edc-03-tacacs OR host=gsw-03-tacacs) enable*]

If you need to use the REX command you can do it either separately for each part of the search

index= networking user* enable* host*
| rex field=_raw "......"
| append [search index=linux_logs host=edc-03-tacacs enable* | rex field=_raw "......"]
| append [search index=linux_logs host=gsw-03-tacacs enable* | rex field=_raw "......"]

or if you feel comfortable enough with regex, use just one REX command after appending all necessary searches:

index= networking user* enable* host*
| append [search index=linux_logs host=edc-03-tacacs enable*]
| append [search index=linux_logs host=gsw-03-tacacs enable*]
| rex field=_raw "......" 

[GRC]

Hi @IZ88 

Thank you for the append query. 

I am trying to build the rex command. Let's say this one..

Oct 15 08:17:45 brg-c-1.com.au 8279: Oct 15 2021 08:17:44.820 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:John logged command:!exec: enable
Oct 15 08:17:35 brg-c-1.com.au 8278: Oct 15 2021 08:17:34.082 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:lili logged command:!exec: enable failed
Sep 15 23:29:55 gsw-r-4.com.au 466: Sep 15 23:29:54.009: %PARSER-5-CFGLOG_LOGGEDCMD: User:Khan logged command:!exec: enable
Aug 12 15:18:37 edc-r-4.com.au 02: Aug 12 15:18:36.472: %PARSER-5-CFGLOG_LOGGEDCMD: User:Khan logged command:!exec: enable
Aug 11 03:31:05 ctc-s.com.au 134: Aug 10 17:31:04.859: %PARSER-5-CFGLOG_LOGGEDCMD: User:cijs logged command:!exec: enable
Jan 29 11:30:58 brg-c-1.com.au 2082: Jan 29 2021 11:30:57.141 AEST: %PARSER-5-CFGLOG_LOGGEDCMD: User:chick logged command:!exec: enable failed

I build the query like this to create a table and capture who used the enabled command on which host on which date. 

But it does not seem to be working. If I can get this query right I think I can apply it in the append you showed me. Can you please help me to figure out what is wrong with this ?

index= networking

| rex field=_raw "(?<Date>\w{3} \d+ \d+:\d+:\d+)(?<user>\w+)(:\d+ (?<hostname>.+) \d+: \w+)(?<command>)"

| search command="enable"

| table Date hostname User Command

[IZ88]

The first part of your regex: (?<Date>\w{3} \d+ \d+:\d+:\d+) can be matched twice within _raw

The first time at the beginning of the event, and the second time a bit further down the line.

You can use the following (assuming that I guessed some of your desired fields correctly)

| rex field=_raw "^(?<Date>\w{3} \d+ \d+:\d+:\d+) (?<hostname>.+) \d+: \w{3}.*User:(?<user>\w+).*:(?<command>.*)$"

few things to note:

1. Field names are case sensitive, so your table command must call the field exactly as you name it ("Command"!="command")

2. The regex for field extraction in REX is the following: (?<field_name>SOME_REGEX)

       -you must have some regex after the field name (in your example <command> is missing regex)

       -each extraction should be within a single parenthesis (your <hostname> extraction is within double parenthesis)

        -the extractions should be written in the exact order they appear in the field you're working on (it looks like you mixed the order in your extraction)

[GRC]

Hi @IZ88 ,

You are the only person in the splunk forum who can understand what I want and can exactly deliver. Thank you so much. 

However, I still have a trouble getting the thing sorted.

I found that there are two time formats as shown in the below image. So when I ran the following command only a specific time is captured. 

Can you please help me ?

This is https://regex101.com/ screenshot. The query can only capture the single dot time stamp.

Thank you.

[GRC]

Hi @IZ88 ,

You are the only person in the splunk forum who can understand what I want and can exactly deliver. Thank you so much. 

However, I still have a trouble getting the thing sorted.

I found that there are two time formats as shown in the below image. So when I ran the following command only a specific time is captured. 

Can you please help me ?

This is https://regex101.com/ screenshot. The query can only capture the single dot time stamp.

Thank you.

[IZ88]

Hi @GRC 

If you wish to account for multiple spaces, you can replace each space in the regex with \s+

\s is the regex for space, and the plus sign indicates that it might have more than one consecutive appearance.

[GRC]

Hi @IZ88 

I did it :

(?<Date>\w{3}\s \d+ \d+:\d+:\d+)

Then the query shifted to capture only double dot string. I wonder if there is a way to capture both formats ?

Thank you

[IZ88]

Hi @GRC 

The reason your query only captured double dot string is because you accidentally asked it to do so.

Since \s is regex for space and after it you lest the actual space, your query looked for \s\s.

For example, we used "\d+" when looking at the date because we don't know if a single digit day (the 1st-9th of each month) will appear as a single digit (1-9) or double digits (01-09), adding the "+" means that Splunk will match any length of consecutive digit strings.

The same goes when your unsure of how many spaces are between each part of your data.

You can use this query;

(?<Date>\w{3}\s+\d+ \d+:\d+:\d+)

[GRC]

Hi @IZ88 ,

How are you doing ? I need a help from you. Could you please help me to generate a single query from these 3 separate queries ?

The index is same in 1 & 2 queries. The source types of all 3 are different. Thank you.

1. index="abc_oracle" source=audit_19c sourcetype="audit"

| eval "Database Modifications:" = "Modification on " + host, "Date and Time" = TIMESTAMP, "Type" = SQL_TEXT, "User" = DB_USER , "Source" = sourcetype

| search "Database Modifications:"="Modification on *"

NOT select | rex field=_raw "SQL_TEXT=\S(?P<Type>\W?......)\s"

| rex field=_raw "DB_USER=(?P<UserName>..........)"

| table "Date and Time", "Database Modifications:" ,"Type", "User", "Source"

2. index="abc_oracle" source=audit_row_19c sourcetype="audit"

| eval "Database Modifications:" = "Modification on " + host, "Date and Time" = TIMESTAMP, "Type" = SQL_TEXT, "User" = DB_USER , "Source" = sourcetype

| search "Database Modifications:"="Modification on *"

NOT select | rex field=_raw "SQL_TEXT=\S(?P<Type>\W?......)\s"

| rex field=_raw "DB_USER=(?P<UserName>..........)"

| table "Date and Time", "Database Modifications:" ,"Type", "User", "Source"

3. index="abc_11g" source=oracle_11g sourcetype="audit"

| eval "Database Modifications:" = "Modification on " + host, "Date and Time" = TIMESTAMP_qab, "Type" = SQL_TEXT, "User" = DB_USER , "Source" = sourcetype

| search "Database Modifications:"="Modification on *"

NOT select | rex field=_raw "SQL_TEXT=\S(?P<Type>\W?......)\s"

| rex field=_raw "DB_USER=(?P<UserName>..........)"

| table "Date and Time", "Database Modifications:" ,"Type", "User", "Source"

Thank you

[GRC]

Woww @IZ88 . This fixed the issue and I was missing some parts of the result. Once I got this missing piece from you, it all fallen in place. 

Thank you so much....

[GRC]

@IZ88 Woww....That worked wonders. I really appreciate the explanation.