Can't get this pipeline to work

Graylog Central (peer support)
1

Hey

I think i just hit a wall…

So after some time, i have finally gotten logs from my SQL Server into my GrayLog.
Now i kinda wanna get some of the information from the full message into searchable new fields.

For some reason i can’t get all the info out, and into new fields.
As you are going to be able to see, my “Table” field is “working”.
And i don’t know why that one work and the other 3 aren’t…

I have generated my Regex with this site.
Regxr.com

I hope someone could help me point out why.
so i could learn from this.

This is myPipeline Rule

rule "Better Overview"
when
  contains(
  value: to_string($message."EventID"),
  search: "33205",
  ignore_case: false
)
then
  let regex_pattern = "/\bserver_principal_name:[A-Za-z]+\\[A-Za-z]+/g";
let regex_results = regex(regex_pattern, to_string($message."full_message"));
let gl2_fragment_extractor_1701769338830 = regex_results["0"];
set_field("SQLServerPrincipalName", gl2_fragment_extractor_1701769338830);
  let output_1 = gl2_fragment_extractor_1701769338830;
  let regex_pattern = "/database_name:[A-Za-z]+/g";
let regex_results = regex(regex_pattern, to_string($message."full_message"));
let gl2_fragment_extractor_1701769338830 = regex_results["0"];
set_field("database_name", gl2_fragment_extractor_1701769338830);
  let output_2 = gl2_fragment_extractor_1701769338830;
  let regex_pattern = "/object_name:([A-Za-z]+(_[A-Za-z]+)+)/g";
let regex_results = regex(regex_pattern, to_string($message."full_message"));
let gl2_fragment_extractor_1701769338830 = regex_results["0"];
set_field("Table", gl2_fragment_extractor_1701769338830);
  let output_3 = gl2_fragment_extractor_1701769338830;
  let regex_pattern = "/action_id:[A-Za-z]+/g";
let regex_results = regex(regex_pattern, to_string($message."full_message"));
let gl2_fragment_extractor_1701769338830 = regex_results["0"];
set_field("ActionID", gl2_fragment_extractor_1701769338830);
  let output_4 = gl2_fragment_extractor_1701769338830;
end

The full_message

{
  "Task": 3,
  "Keywords": -9178336040581071000,
  "EventType": "AUDIT_SUCCESS",
  "gl2_remote_ip": "XX.XX.XX.XX",
  "gl2_remote_port": XXXX,
  "source": "Hostname.Domain",
  "gl2_source_input": "653a1442ebd2511f7385218e",
  "SeverityValue": 2,
  "Version": 0,
  "UserID": "S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003",
  "gl2_source_node": "953c7e62-38d1-4aea-ba69-c61e5caaeacc",
  "ProcessID": 872,
  "timestamp": "2023-12-06T12:41:22.000Z",
  "gl2_accounted_message_size": 2260,
  "OpcodeValue": 0,
  "Table": "Table",
  "SourceModuleType": "im_msvistalog",
  "level": 6,
  "ActivityID": "{906FB34C-1640-0001-ABB4-6F904016DA01}",
  "Channel": "Security",
  "streams": [
    "6544f12cebd2511f739d9961"
  ],
  "gl2_message_id": "01HGZJBAAG0005MA5DY5918J4D",
  "SourceName": "MSSQLSERVER$AUDIT",
  "Severity": "INFO",
  "message": "Audit event: audit_schema_version:1\
nevent_time:2023-12-06 12:41:",
  "AccountType": "Well Known Group",
  "EventReceivedTime": "2023-12-06 13:41:24",
  "SourceModuleName": "ExtendedLog",
  "full_message": "
Audit event: audit_schema_version:1\nevent_time:2023-12-06 12:41:21.9959931\nsequence_number:1\naction_id:SL  \nsucceeded:true\nis_column_permission:true\nsession_id:102\nserver_principal_id:267\ndatabase_principal_id:1\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:565577053\nuser_defined_event_id:0\ntransaction_id:193712296\nclass_type:U \nduration_milliseconds:0\nresponse_rows:0\naffected_rows:0\nclient_tls_version:0\ndatabase_transaction_id:0\nledger_start_sequence_number:0\nclient_ip:local machine\npermission_bitmask:00000000000000000000000000000001\nsequence_group_id:66482E51-5AC4-4593-9088-044B4CC43A7E\nsession_server_principal_name:Domain\\Username\nserver_principal_name:Domain\\User\nserver_principal_sid:010500000000000515000000a4fb35772457366a6ca9633a18f40300\ndatabase_principal_name:dbo\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:Hostname\ndatabase_name:DB\nschema_name:dbo\nobject_name:Table\nstatement:SELECT TOP (1000) [Username]\r\n      ,[Cpr]\r\n      ,[FullName]\r\n      ,[AssignedEID]\r\n      ,[AssignedNemLogin]\r\n      ,[Created]\r\n      ,[Last_Changed]\r\n      ,[Errors]\r\n      ,[InDataSet]\r\n  FROM [DB].[dbo].[Table]\r\n  where Cpr like 'XXXXXXXXXX'\nadditional_information:\nuser_defined_information:\napplication_name:Microsoft SQL Server Management Studio - Query\nconnection_id:C779199A-DF78-4540-B37E-C08DEF0D0CA1\ndata_sensitivity_information:\nhost_name:SKMSSQL01\nsession_context:\nclient_tls_version_name:\nexternal_policy_permissions_checked:\n.",
  "ThreadID": 2228,
  "EventID": 33205,
  "_id": "c6fe5130-9434-11ee-a4b2-005056939539",
  "Domain": "NT SERVICE",
  "RecordNumber": 759371,
  "AccountName": "MSSQLSERVER"
}
2

That seems like a lot of difficult RegEx - wow!

Have you tried the key/value function instead?

Also, are you sure your when statement is working correctly? You might try commenting out all that regex and just adding a static field to test it: set_field("test","true");

rule "Better Overview"
when
  contains(
  value: to_string($message."EventID"),
  search: "33205",
  ignore_case: false
)
then
    set_fields(
        fields:key_value(
            value:to_string($message.message),
            trim_value_chars:"\",\'",
            trim_key_chars:"\",\'"
            )
        );
end

Edit - here’s the documentation for key_value: Functions Descriptions

1 Like
3

Maybe, I’m using the new Rule builder for it.
And i made the RegEx part with Regexr and then confirmed with Regex101…
But something that have bugged me, the site automatically set the / in the start and /g in the end. Not sure what’s up with it.

No have not tried the Key/Value function.
Could give it a try.

If I’m understanding this builder correctly.
Then the Conditions Output eq True, it means it’s ok?

image
image3086×1056 181 KB

Also, have trimmed it a bit down, so it’s easier to look at.

So now the Rule is:

rule "Better Overview"
when
  contains(
  value: to_string($message."EventID"),
  search: "33205",
  ignore_case: false
)
then
  let regex_pattern = "/\bserver_principal_name:[A-Za-z]+\\[A-Za-z]+/g";
let regex_results = regex(regex_pattern, to_string($message."full_message"));
let gl2_fragment_extractor_1701769338830 = regex_results["0"];
set_field("SQLServerPrincipalName", gl2_fragment_extractor_1701769338830);
  let output_1 = gl2_fragment_extractor_1701769338830;
end

And the log message is this:

 "EventID": 33205,
"full_message": action_id:SL\nserver_principal_name:Domain\\UserName\ndatabase_name:DBName\nschema_name:dbo\nobject_name:Table\nhost_name:HostName
4

I have not used the rule builder - maybe someone else will chime in with experience in that.

5

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.