Cortex XDR Parsing rule / SQL query

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Cortex XDR Parsing rule / SQL query

L2 Linker

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

 

1 accepted solution

Accepted Solutions

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

View solution in original post

4 REPLIES 4

L4 Transporter

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?

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

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

HI

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

Danny

  • 1 accepted solution
  • 2169 Views
  • 4 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!