How to use rex to extract a value and use it as an alert condition MS SQL Audit

[rob_gibson]

I think I'm close on this, but I'm missing something;

I have events forwarding to my indexer from MS SQL Audit via WinEventLog:Application. A number of fields are not being automatically extracted, so I'm relying on rex to pull out the values I need for an alert. There is a large, multi-line field called "Message" which contains all the values I need. Within "Message" are other values like object_name, database_name, user_name, etc. I have no trouble pulling the values I need into a search and creating a table, but I'm not clear how to use a rex-returned value as a condition to trigger an alert.

The alert is only required if object_name:table1 or object_name:table2. Also if the "statement value is alter or create or delete, etc.

Partial Search String (outputs values to a table - not shown)

    index=SQLAUDIT ComputerName=SERVER12 | search Message statement:alter OR statement:create OR statement:delete OR statement:drop OR statement:exec OR statement:insert OR statement:select OR statement:update | eval "Date - Time"=strftime(_time,"%F - %T") | rex "object_name:(?<TableID>.*)" | rex "statement:(?<AlertType>.+?)[\r]" | rex "database_name:(?<DBN>.*)" 

Data Sample

Message=Audit event: event_time:2017-06-14 12:58:53.7376885
        sequence_number:1
        action_id:SL  
        succeeded:true
        permission_bitmask:1
        is_column_permission:true
        session_id:79
        server_principal_id:2
        database_principal_id:0
        target_server_principal_id:0
        target_database_principal_id:0
        object_id:1201906778
        class_type:U 
    session_server_principal_name:DOMAIN\USER1234
    server_principal_name:DOMAIN\USER1234
    server_principal_sid:010500000000000567890093a2a241345661e2f43170a32821c0000
    database_principal_name:public
    target_server_principal_name:
    target_server_principal_sid:
    target_database_principal_name:
    server_instance_name:SERVER12\DBMSSQL
    database_name:DB_NAME1
    schema_name:db19
    object_name:table_1
    statement:select top 100 a.* , b.*
    from tableClosed a,
         table_1 b
    where a.flngAccountKey = b.flngAccountKey 
    and a.fstrCaseType = 'TRPHD'

[woodcock · Accepted solution]

I always do ALL of my thresholding logic inside of my search and the use Number of results and is greater than 0 for the alert threshold. So you would just add something like this to the end of your search:

| search (object_name:table1="*" OR object_name:table2="*") AND (statement="alter" OR statement="create" OR statement="delete")

[rob_gibson]

That's close enough for an "Accept" woodcock!

My search didn't like the "*" you added, and I'm unclear of the function.

What I did was;

| search (object_name:table1 OR object_name:table2) AND (statement:alter OR statement:create OR statement:delete OR statement:drop OR statement:exec OR statement:insert OR statement:select OR statement:update)

The resulting table included only the hits, which is exactly what I wanted. It created a conditional alert. Bravo!

Many thanks

[horsefez]

Hi,

your usage of the rex command is faulty.
The right syntax for the rex command is:
| rex field=<fieldname> "<regular-expression>"

in your case for example:
| rex field=object_name "(?<TableID>.*)"

Additionally try this command

| rex field=Message "(?:database_name:)(?<database_name>.+)(?:[\r\n\S\s]+?)(?:object_name\:)(?<object_name>.+)(?:[\r\n\S\s]+?)(?:statement:)(?<statement>.+)"

https://regex101.com/r/9GE7Iu/2

[rob_gibson]

Thank you for your reply.

The rex command I am running does return the value I want, so I'm not sure why you say it is faulty. What I'm unclear on is how to use it as a condition.

I did not get any results using the example you provided, sorry.

[gcusello]

HI rob.gibson,
I'm not sure to have understood your request: do you need to create an alert when there is object_name:table1 or there is object_name:table2 or is present the string "statement value is alter or create or delete"?

try with

index=SQLAUDIT ComputerName=SERVER12 
| search Message statement:alter OR statement:create OR statement:delete OR statement:drop OR statement:exec OR statement:insert OR statement:select OR statement:update 
| eval "Date - Time"=strftime(_time,"%F - %T") 
| rex "object_name:(?<TableID>.*)" 
| rex "statement:(?<AlertType>.+?)[\r]" 
| rex "database_name:(?<DBN>.*)" 
| search object_name:table1=* or object_name:table2=*

then put the alert condition to result > 0
that means that there are results to your search

Bye.
Giuseppe

[rob_gibson]

My laptop has been out of commission for a while, apologies for the delay in responding.

To clarify, I need an alert that is triggered for ANY of those statement: conditions, but only where object_name:table1 or table2.

I tried your suggestions but got zero results (where I know that 4 exist within the search time frame.)