How to create a table with some column swith WinEventLog:Application sort by event 33205?

[atyshke1]

Hello,
Our SQL upload SQL audit data in App log of windows. But I can't find solution how can I create a table with some columns.
App log is:

07/30/2018 10:45:06 AM
LogName=Application
SourceName=MSSQLSERVER
EventCode=33205
EventType=0
Type=Information
ComputerName=xxx02065.xxxx.xxxx.com
TaskCategory=None
OpCode=None
RecordNumber=8975014
Keywords=Audit Success, Classic
Message=Audit event: audit_schema_version:1
event_time:2018-07-30 07:45:05.2846868
sequence_number:1
action_id:CR  
succeeded:true
is_column_permission:false
session_id:65
server_principal_id:303
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:0
user_defined_event_id:0
class_type:DB
permission_bitmask:00000000000000000000000000000000
sequence_group_id:18A26E68-7F51-4E14-AD09-4C3610B28C7C
session_server_principal_name:XX1\ADMIN
server_principal_name:XX1\ADMIN
server_principal_sid:0105000000000005150000007a280b4f8541136d9540f20e48f01600
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:xxx02065
database_name:master
schema_name:
object_name:
statement:RESTORE LABELONLY FROM DISK=@P1
additional_information:
user_defined_information:

I create a table use a code:

source="WinEventLog:App*" index="wineventlog" "eventcode=33205" | eval Date=strftime(_time, "%Y/%m/%d %H:%M:%S") | eval Stat=statement | eval Account=server_principal_name | rename host as Host | stats Count by Date, Host | table Host, Date, Stat, Account, Count

But columns Stat and Account is empty 😞
What I do is wrong?

[atyshke1]

I use this one:
source="WinEventLog:App*" index="wineventlog" host=$EventHost$ EventCode=$EventCode$ | bin _time span=1d | rex "server_principal_name:(?<UserName>\S+)" | eval Account=coalesce(principal_name,UserName) | rex "statement:(?<Stat>\S+.\S+.\S+.\S+)" | eval Statement=coalesce(St,Stat) | eval Date=strftime(_time, "%Y/%m/%d") | rename host as Host | stats count as Count by Host, Date, Statement, Account

It seems works good.

But your code will be better. Thank you!

[niketn]

@atyshke1 if it has helped, do accept the answer to mark this question as answered and up vote the comments that helped 🙂

[niketn]

@atyshke1, you accepted your own answer instead of mine. Please correct. Do up vote the comments that helped as well!

[atyshke1]

I am working with that, may be eval can help me extract exact fields:

source="WinEventLog:App*" index="wineventlog" "eventcode=33205" | rex "\sserver_principal_name:(?\S+)" | eval Account=coalesce(principal_name,UserName) | eval Date=strftime(_time, "%Y/%m/%d %H:%M:%S") | rename host as Host | stats count as Count by Date, Host | table Host, Date, Stat, Account, Count

What do you think?

[niketn]

@atyshke1, as per your previous query I was under impression that fields server_principle_name and statement are already extracted. You can try with the rex command to see if it works and create your own Field Extraction after testing the regular expression.

Following is a run anywhere search based on sample event:

| makeresults 
| eval _raw="07/30/2018 10:45:06 AM
LogName=Application
SourceName=MSSQLSERVER
EventCode=33205
EventType=0
Type=Information
ComputerName=xxx02065.xxxx.xxxx.com
TaskCategory=None
OpCode=None
RecordNumber=8975014
Keywords=Audit Success, Classic
Message=Audit event: audit_schema_version:1
event_time:2018-07-30 07:45:05.2846868
sequence_number:1
action_id:CR 
succeeded:true
is_column_permission:false
session_id:65
server_principal_id:303
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:0
user_defined_event_id:0
class_type:DB
permission_bitmask:00000000000000000000000000000000
sequence_group_id:18A26E68-7F51-4E14-AD09-4C3610B28C7C
session_server_principal_name:XX1\ADMIN
server_principal_name:XX1\ADMIN
server_principal_sid:0105000000000005150000007a280b4f8541136d9540f20e48f01600
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:xxx02065
database_name:master
schema_name:
object_name:
statement:RESTORE LABELONLY FROM DISK=@P1
additional_information:
user_defined_information:" 
| eval host="test"
| KV 
| bin span=1d _time
| rex "server_principal_name:(?<server_principal_name>.*)" 
| rex "statement:(?<statement>.*)" 
| stats count as Count by _time, host, statement, server_principal_name 
| fieldformat _time=strftime(_time, "%Y/%m/%d") 
| rename statement as Stat, server_principal_name as Account, host as Host

PS: Pipes from makeresults till KV are used to generate dummy data as per your question.

[atyshke1]

If I try source="WinEventLog:App*" index="wineventlog" "eventcode=33205" | stats count as Count by _time, host
Works fine and show some data in a search. If I insert statement or server_principal_name search doesn't find anything...

[atyshke1]

It doesn't work. Searching doesn't show any information

[niketn · Accepted solution]

[UPDATED ANSWER] Added regular expression extraction for server_principal_name and statement as needed in the query.

| rex "server_principal_name:(?<server_principal_name>.*)"
| rex "statement:(?<statement>.*)"

---

@atyshke1, since you plan to group count of events by Host, Stats Account and Date, you would need to choose the span of time to bucket Date like hourly or daily etc (otherwise only if multiple events occur at the exact same time then they will be counted as aggregate). Following is an example where I have chosen span=1s or 1 second as your time format had precision up to seconds.

source="WinEventLog:App*" index="wineventlog" "eventcode=33205" 
| bin _time span=1s
| rex "server_principal_name:(?<server_principal_name>.*)"
| rex "statement:(?<statement>.*)"
| stats count as Count by _time, host, statement, server_principal_name
| fieldformat _time=strftime(_time, "%Y/%m/%d %H:%M:%S")
| rename statement as Stat, server_principal_name  as Account, host as Host 

The above uses fields extracted during search time field discovery in stats command and then uses rename and fieldformat to format the field names and time to be displayed in user friendly manner.

Please try out and confirm.
You can try the following if you need daily count. You can try other options like hourly etc on similar lines.

source="WinEventLog:App*" index="wineventlog" "eventcode=33205" 
| bin _time span=1d
| rex "server_principal_name:(?<server_principal_name>.*)"
| rex "statement:(?<statement>.*)"
| stats count as Count by _time, host, statement, server_principal_name
| fieldformat _time=strftime(_time, "%Y/%m/%d")
| rename statement as Stat, server_principal_name  as Account, host as Host