Hi,
I need to extract with rex the two first words of one event but sometimes they are only one word.
For example, with these data :
command:RESTORE LABELONLY FROM DISK=@P1
command:RESTORE VERIFYONLY FROM DISK = 'i:\toto.sql'
command:RESTORE VERIFYONLY FROM DISK = 'i:\tata.sql'
command:RESTORE LABELONLY FROM DISK=@P1
command:sp_addlinkedsrvlogin
command:RESTORE LABELONLY FROM DISK=@P1
I need to have set the field command with these value from these data :
RESTORE LABELONLY
RESTORE VERIFYONLY
RESTORE VERIFYONLY
RESTORE LABELONLY
sp_addlinkedsrvlogin
RESTORE LABELONLY
I will apreciate some help to have the correct syntax for rex.
Regards
Assuming there is a space between the words, and not a new line character, try this
| rex "statement:(?<firstwords>\w+( \w+)?)"
Thank you for your answer, unfortunately i tested your answer on my real data that are quiet complex than the one I gave.
I need to work on statement included in a xml field from sys event log.
Some statements have only one word and other have more than two words.
Statement is delimited by a carriage return in the event.
So the search | rex ".*statement:(?<statement>\w+(\s\w+)?)"
In the event below the statement field returned is
sp_addlinkedsrvlogin
additional_information
The word after sp_addlinkedsrvlogin is on the next line, so it's not what i expect. In this case, i just want sp_addlinkedsrvlogin
Please find the complete event above.
Regards,
Tchounga
<Event xmlns='http://schemas.microsoft.com/win/2004/08/events/event'><System><Provider Name='MSSQL$MWPBZAS1$AUDIT'/><EventID Qualifiers='16384'>33205</EventID><Level>0</Level><Task>3</Task><Keywords>0x80a0000000000000</Keywords><TimeCreated SystemTime='2023-08-31T04:30:01.964529800Z'/><EventRecordID>134063208</EventRecordID><Channel>Security</Channel><Computer>swpcfrbza354.cib.net</Computer><Security UserID='S-1-5-21-2847098101-2387550839-3588296759-1127899'/></System><EventData><Data>audit_schema_version:1
event_time:2023-08-31 04:30:00.9332742
sequence_number:1
action_id:CR
succeeded:true
is_column_permission:false
session_id:53
server_principal_id:272
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:0
user_defined_event_id:0
transaction_id:5417128
class_type:SL
duration_milliseconds:0
response_rows:0
affected_rows:0
client_ip:100.83.120.237
permission_bitmask:00000000000000000000000000000000
sequence_group_id:93E8A6AF-640E-4EC2-B401-76F0ED6957A9
session_server_principal_name:CIB\ipcb3proc-sqlag-bd4
server_principal_name:CIB\ipcb3proc-sqlag-bd4
server_principal_sid:010500000000000515000000f544b3a977224f8e3710e1d5dc351100
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:SWPCFRBZA354\MWPBZAS1
database_name:master
schema_name:
object_name:LSuser
statement:sp_addlinkedsrvlogin
additional_information:<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><server_name><![CDATA[SWPDFRSQLADM1\MWPADM01]]></server_name></action_info>
user_defined_information:
application_name:SQLAgent - TSQL JobStep (Job 0x451A71BE3BB91D4DBF2A1A6C12446006 : Step 1)
</Data></EventData></Event>
Assuming there is a space between the words, and not a new line character, try this
| rex "statement:(?<firstwords>\w+( \w+)?)"
Thank you so much, it's working.
In other event, the field statement can be :
RESTORE VERIFYONLY FROM DISK = 'I:\toto.bak'
In this case, in need to get statement=RESTORE VERIFYONLY
(the first 2 words)
| rex "command:(?<firstwords>\w+(\s\w+)?)"
Hi,
It seems that it doesn't work for line sp_addlinkedsrvlogin, i get this word and the word of the next line.
If there is only one word one the line, i need to get only these word.
Regards
Tchounga
Try it in Splunk not regex101.com
| makeresults
| fields - _time
| eval _raw="command:RESTORE LABELONLY FROM DISK=@P1
command:RESTORE VERIFYONLY FROM DISK = 'i:\toto.sql'
command:RESTORE VERIFYONLY FROM DISK = 'i:\tata.sql'
command:RESTORE LABELONLY FROM DISK=@P1
command:sp_addlinkedsrvlogin
command:RESTORE LABELONLY FROM DISK=@P1"
| multikv noheader=t
| fields _raw
| rex "command:(?<firstwords>\w+(\s\w+)?)"
Thanks for your response, but I'm sorry I wasn't quite precise.
In fact, i extract data from syslog from windows event, and the structure of the data is more complex than the sample i gave.
The command field is contained in an xml field like these below.
The search :
index=app_bisql host=TRUC
| rex ".*action_id:(?<action>\S+)*"
| where action = "CR"
| rex ".*command:(?<command>\w+(\s\w+)?)"
| fields command
command = sp_addlinkedsrvlogin additional_information
I was expecting :
command = sp_addlinkedsrvlogin
The field concerned in an xml event.
Regards