[REX] : How to extract 2 words OR one if the line contains only one with rex

[tchounga]

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

[tchounga]

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:&lt;action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"&gt;&lt;server_name&gt;&lt;![CDATA[SWPDFRSQLADM1\MWPADM01]]&gt;&lt;/server_name&gt;&lt;/action_info&gt;
user_defined_information:
application_name:SQLAgent - TSQL JobStep (Job 0x451A71BE3BB91D4DBF2A1A6C12446006 : Step 1)
</Data></EventData></Event>

[ITWhisperer · Accepted solution]

Assuming there is a space between the words, and not a new line character, try this

| rex "statement:(?<firstwords>\w+( \w+)?)"

[tchounga]

Thank you so much, it's working.

[tchounga]

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)

[ITWhisperer]

| rex "command:(?<firstwords>\w+(\s\w+)?)"

[tchounga]

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

[tchounga]

[ITWhisperer]

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+)?)"

[tchounga]

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.

<Event xmlns='http://schemas.microsoft.com/win/2004/08/events/event'><System><Provider Name='TRUC$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>truc.net</Computer><Security UserID='secret'/></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.255.120.234 
permission_bitmask:00000000000000000000000000000000
sequence_group_id:93E8C63F-640E-4EC4-B401-76F0ED6947A9
session_server_principal_name:truc 
server_principal_name:truc 
server_principal_sid:truc
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:truc 
database_name:master
schema_name:
object_name:LSuser
command:sp_addlinkedsrvlogin
additional_information:&lt;action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"&gt;&lt;server_name&gt;&lt;![CDATA[SWPDFRSQLADM1\MWPADM01]]&gt;&lt;/server_name&gt;&lt;/action_info&gt; user_defined_information: application_name:SQLAgent - TSQL JobStep (Job 0x451A71BE3BB91D4DBF2A1A6C12446006 : Step 1) </Data></EventData></Event>

Regards