Splitting field then searching each result

[sk]

We are monitoring users who are deleting tables in our system. We have a field "user_query" which I want to parse by ";". Then for each split result, I want to check for the text "Drop table *". In addition, they are allowed to drop tables that are from a "temp" dataset or have _tmp in the table name. The reason I want to parse each statement by ";" is that if I do a -(*temp.*) or -(*_tmp), it will not return the whole "user_query" but in latter statements, the user may have written "Drop table [tablename_they_should_not_drop]".

So basically, I want to split "user_query" by ";" then go through each split result and check for the text "Drop table *" -(*temp.*) or -(*_tmp). If any instances, I want to return the full "user_query" in a table in a Splunk alert.

I do not know if this can be done using regex or I need multiple steps to split and then loop through each result. 

Help please! TIA

[bowesmana]

@sk 

If you could give an example of the data in user_query, that would be useful, but for starters, you can do this

| makeresults
| eval _raw="user1;my_query1;my_query2,user2;drop table protected_table,user3;drop table _tmp_xxx,user4;drop table something_with_temp_in;second_query;drop table illegal_table,user5;first_query;drop table protected_table_2;third_query,user6;bla_bla_bla;drop table important_table,user7;drop table table_1;drop table table_2"
| eval event=split(_raw, ",")
| mvexpand event
| fields - _raw _time
| rex field=event "(?<user>[^;]*);(?<queries>.*)"
| table user queries
| eval queries=split(queries,";")
| eval found=mvfilter(match(queries,"(?i)drop table ((?!_tmp|temp).)*$"))

Note that the SPL up to the table command is setting up the data and it is the last two lines that are of interest

Note that the found field will contain all the values that match the reqex in the match statement.

You can then do '| where !isnull(found) to then filter out those users with illegal drop table statements

Hope this helps

[sk]

@bowesmana 

Appreciate your help.

Query I have below. Note that I will now check for "create or replace table" not "drop table".

index="x" host=x sourcetype = x
| search Query="*create or replace table*"
| eval split_queries=split(Query,";")
| eval found=mvfilter(match(split_queries,"(?i)create or replace table ((?!_tmp|temp).)*$"))
| table _time Query Email

Data sample:

4 rows:
Query column:
1) CREATE OR REPLACE TABLE 'aa_temp.tablename as select * from tableA
2) DECLARE var1='text'; create or replace table 'dataset.table_tmp' as select * from tableB;
3) Create or replace table dataset_no_delete.table1 as select * from tableC

4) CREATE OR REPLACE TABLE 'aa_temp.tablename as select * from tableA; Create or replace table dataset_no_delete.table1 as select * from tableC

I want to return a table with 2 rows (#3 and 4 from above):
1/12/21 1 pm Create or replace table dataset_no_delete.table1 as select * from tableC email@company.com

1/12/21 2 pm   CREATE OR REPLACE TABLE 'aa_temp.tablename as select * from tableA; Create or replace table dataset_no_delete.table1 as select * from tableC email@company.com

so that we can monitor users/queries who are using "create or replace table" in a certain database.

[bowesmana · Accepted solution]

@sk 

Not sure if there is a question here as you are doing what you need to do other than using the 'where' clause mentioned in my first post to remove those rows (1 & 2) that are not 'found'.

| makeresults
| eval _raw="CREATE OR REPLACE TABLE 'aa_temp.tablename as select * from tableA:DECLARE var1='text'; create or replace table 'dataset.table_tmp' as select * from tableB;:Create or replace table dataset_no_delete.table1 as select * from tableC:CREATE OR REPLACE TABLE 'aa_temp.tablename as select * from tableA; Create or replace table dataset_no_delete.table1 as select * from tableC"
| eval Query=split(_raw, ":")
| eval Row=1
| mvexpand Query
| accum Row
| fields - _raw
| search Query="*create or replace table*"
| eval split_queries=split(Query,";")
| eval found=mvfilter(match(split_queries,"(?i)create or replace table ((?!_tmp|temp).)*$"))
| where !isnull(found)
| eval Email=Row."@mycompany.com"
| table _time Row Query Email

[sk]

Thank you @bowesmana !

I realized the line breaks in some text was throwing me off so I added the following as well:

| eval query = replace (query, "[\n\r]"," ")