Splunk Search

How to extract Splunk rex field?

GRC
Path Finder

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.

Labels (2)
0 Karma
1 Solution

IZ88
Path Finder

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.

View solution in original post

GRC
Path Finder

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"

GRC_1-1635157113057.png

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

0 Karma

IZ88
Path Finder

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
Path Finder

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.

0 Karma

GRC
Path Finder

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

0 Karma

IZ88
Path Finder

Hi @GRC 

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

|search Type!="SELECT"

IZ88
Path Finder

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"

 

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...