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]

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

[IZ88 · Accepted solution]

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.

[GRC]

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.

[GRC]

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

[IZ88]

Hi @GRC 

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

|search Type!="SELECT"

[IZ88]

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"