topic Re: Splunk Rex Field in Splunk Search

How to extract Splunk rex field?

GRC — Thu, 17 Mar 2022 04:50:38 GMT

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.

Re: Splunk Rex Field

IZ88 — Mon, 25 Oct 2021 08:57:49 GMT

Hi GRC,

table command will show all columns provided even if they don't exist in some (or all event). So even if the REX command won't capture SELECT, events that has SELECT in their query will still show in your final table, only without any value under the column of "Type".

If you wish to exclude SELECT from your table you can simply add:

| where Type!="SELECT"

Re: Splunk Rex Field

GRC — Mon, 25 Oct 2021 09:31:51 GMT

Hi @IZ88 ,

You are correct, even if I exclude select with |where Type!="SELECT", I still get "Select" in the table.

Do you know how can I exclude select fully ?

Thanks

Re: Splunk Rex Field

IZ88 — Mon, 25 Oct 2021 09:42:58 GMT

Hi @GRC

Sorry for misleading you, instead of "where" use "search"

|search Type!="SELECT"

Re: Splunk Rex Field

GRC — Mon, 25 Oct 2021 10:32:30 GMT

Thanks @IZ88

I completely eliminated Select with this one..

| search "Database Modifications:"="Modification on *"
NOT select | rex field=_raw "SQL_TEXT=\S(?P<Type>\W?......)\s"

I have another question:

In this below mentioned dataset. I want to create a conditional query.

Ex: I want to check first whether rsyslog service is stopped, if it stopped then who stopped it, in which server. Can you please help ?

Oct 25 16:30:06 keybox sudosh: KHYJS6PxEI64zG Henry: service rsyslog start
Oct 25 16:30:02 keybox sudosh: KHYJS6PxEI64zG Joseph: #011service rsyslog stop
Oct 25 15:15:30 keybox sudosh: ssNjFZca22OvaB Henry: service rsyslog stop
Oct 25 15:08:26 keybox sudosh: ssNjFZla22OvaB Henry: #011service rsyslog start
Oct 25 15:07:46 keybox sudosh: ssNjFZla22OvaB Joseph: service rsyslog status
Oct 25 15:06:21 keybox sudosh: ssNjF0la22OvaB Asher: service rsyslog statutss
Oct 25 14:49:57 eqc-03-tpp sudosh: gkrMz1dLey0CS1 John: cat /etc/red#011#177#177#177#177#177#177#177#177#177#177#177#177#177#177#177r#177#177#177#177#177#177#177#177#177#177#177#177#177sys#177#177ervice rsyslog status
Oct 25 14:48:26 keybox sudosh: VSjTDhPH3iM5MY Ahser: service rsyslog status

Thank you

Re: Splunk Rex Field

IZ88 — Mon, 25 Oct 2021 10:59:47 GMT

I'm assuming each line is a separate event:

| rex field=_raw "(?<date>\w{3} \d+ \d+:\d+:\d+) (?<var_name>.+) (?<lnx_command>\w+): (?<var_name2>\w+) (?<user>\w+): (?<sys_command>.*)"

| search sys_command="*rsyslog stop"

| table date user <the var_name thats correspond with your server name>

If your events are parsed into fields, you just need to search the relevant field for the values you desire.

Re: Splunk Rex Field

GRC — Mon, 25 Oct 2021 23:43:32 GMT

Hi @IZ88 ,

Thank you. This solved my problem. I marked it as solution and gave you thumbs up.

Thank you so much for the help.

Re: Splunk Rex Field

GRC — Tue, 26 Oct 2021 03:22:51 GMT

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.

Re: Splunk Rex Field

GRC — Tue, 26 Oct 2021 09:22:23 GMT

Hi @IZ88 ,

I tried to capture the server names from below data:

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

Re: Splunk Rex Field

IZ88 — Tue, 26 Oct 2021 10:47:11 GMT

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.

Re: Splunk Rex Field

GRC — Tue, 26 Oct 2021 10:57:53 GMT

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

Re: Splunk Rex Field

GRC — Tue, 26 Oct 2021 11:13:12 GMT

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*

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

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

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

Thank you

Re: Splunk Rex Field

IZ88 — Tue, 26 Oct 2021 12:23:31 GMT

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

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 "......"

Re: Splunk Rex Field

GRC — Tue, 26 Oct 2021 12:49:21 GMT

Hi @IZ88

Thank you for the append query.

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

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

Re: Splunk Rex Field

IZ88 — Tue, 26 Oct 2021 13:23:06 GMT

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)

Re: Splunk Rex Field

GRC — Wed, 27 Oct 2021 04:32:51 GMT

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.

Re: Splunk Rex Field

GRC — Wed, 27 Oct 2021 04:36:39 GMT

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.

Re: Splunk Rex Field

IZ88 — Wed, 27 Oct 2021 06:15:57 GMT

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.

Re: Splunk Rex Field

GRC — Wed, 27 Oct 2021 06:32:53 GMT

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

Re: Splunk Rex Field

IZ88 — Wed, 27 Oct 2021 06:52:47 GMT

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+)