topic Cortex XDR Parsing rule / SQL query in Cortex XDR Discussions

Cortex XDR Parsing rule / SQL query

DannyMulheran — Mon, 03 Apr 2023 07:27:33 GMT

Hi All

I am currently creating some parsing rules and I am using split to seperate the _raw_log field into its individual fields. After my initiail split I have one a field that starts with a comma, or multple commas, depending on the log. Does anyone know if it is possible to trim either:

A single comma from the start, or

A single character from the start?

I have tried using "ltrim" but it removes all the commas at the start of the string.

Thanks

Danny

Re: Cortex XDR Parsing rule / SQL query

anlynch — Tue, 04 Apr 2023 22:25:49 GMT

Hi @DannyMulheran,

Thanks for reaching out on LIVEcommunity!

Is it possible that you an post what you have so far so I can get a look and get a better understanding of what you're trying to parse?

Re: Cortex XDR Parsing rule / SQL query

DannyMulheran — Wed, 05 Apr 2023 01:21:55 GMT

Hi Anlynch

Thanks for responding. Before you review the info below, I have realised whilst putting this info together that the solution is to simply take the extra field, caused by the extra comma, into account when I refence the index of the value I want. However, I am including the detail of what I am doing as I would still be interested to know if it is possible to just remove a single comma, or a single character (this would be the same in this case) from the start or end of a value.

It is a NGFW TRAFFIC log I am parsing. I use split to separate the fields using comma as the delimiter, but the 'Application Characteristic' field can have multiple entries that are also comma separated.

To deal with this I use a filter to identify if there is a " in the log (so far I have only seen " in the app characteristic field, when this field has multiple values). If this is the case I first split the log into 3 separate sections using the " as the delimiter. The second section delivers the app characteristic field perfectly. The first and third set of fields cover all the remaining fields, but it leaves an additional comma at the end of the first and start of the third set of field values.

At the start of the third set of fields sometimes it starts with a single comma (example: ,dns,dns-base,no,no,0), and sometimes with multiple commas (example: ,,ssl,no,no,0), depending on whether the field following the app characteristic field has a value or not. I wanted to remove the first comma only, but the ltrim removes all the leading commas. I would like to just remove a single comma, or a single character (this would be the same in this case).

Query example I am playing around with:

dataset = panw20_ngfw20_raw

| filter _raw_log contains "TRAFFIC" and _raw_log contains "\""

| alter temp1 = split(_raw_log, "\"")

| alter group1 = rtrim(arrayindex(temp1, 0), ",")

| alter group2 = arrayindex(temp1, 1)

| alter group3 = arrayindex(temp1, 2) // I was using ltrim on this line

| limit

Re: Cortex XDR Parsing rule / SQL query

fmoixsante — Wed, 05 Apr 2023 20:50:50 GMT

Hi @DannyMulheran,

Have you looked at arrayindex and regextract?

I reckon that you could use something like,

dataset = panw20_ngfw20_raw

| filter _raw_log contains "TRAFFIC" and _raw_log contains "\""

| alter temp1 = split(_raw_log, "\"")

| alter group1 = rtrim(arrayindex(temp1, 0), ",")

| alter group2 = arrayindex(temp1, 1)

//| alter group3 = arrayindex(temp1, 2) // I was using ltrim on this line

| alter group3 = arrayindex(regextract(temp1, "^,|,(\s)"),0) //I am not 100% sure about the regex itself and if 0 is the correct position in the arrayindex.

| limit 10

Re: Cortex XDR Parsing rule / SQL query

DannyMulheran — Thu, 06 Apr 2023 06:45:56 GMT

I am refreshing myself on regex and happy this will met the requirement. Thanks.

Danny