Analytic Rules errors with Front Door Premium WAF - SQLi Detection and Front Door Premium WAF - XSS Detection

[cyb3n3tic]

Describe the bug
It appears that it is not possible to create analytics rules for either Front Door Premium WAF - SQLi Detection or Front Door Premium WAF - XSS Detection. (I assume these can be used with only Application Gateway?)

When defining the rule logic, the pre-populated rule query throws errors as indicated below.

The name 'details_msg_s' does not refer to any known column, table, variable or function.(KS142)
The name 'trackingReference_s' does not refer to any known column, table, variable or function.(KS142)
The name 'details_matches_s' does not refer to any known column, table, variable or function.(KS142)
The column 'trackingReference_s' must exist on both sides of the join.(KS145)

To Reproduce
Steps to reproduce the behavior:

1. Go to Configuration/Analytics
2. Select Rule Templates
3. Search for sqli or xss
4. Click on template
5. Click on Create Rule (Bottom right)
6. Click Next: Set rule logic
7. Rule query will display errors as above

Expected behavior
The rule query generated should be valid with no errors

Screenshots
Screenshots attached

Additional context
Running Application Gateway with WAF, FrontDoor not in use but assume this should work as Data sources listed are Application Gateways and appear valid in that respect, I have not found any application gateway WAF specific templates?

I have used some other templates such as App GW WAF - Path Traversal Attack without problem but not seeing App GW WAF - SQLi or XSS - I assume those templates lised for Front Door Premium should work, or is that not the case?

Appreciate some guidance.

[v-rusraut]

Hi @cyb3n3tic,
Thanks for flagging this issue, we will investigate this issue and get back to you with some updates by 24 May 2024. Thanks!

[cyb3n3tic]

Great, thank you @v-rusraut

[v-rusraut]

Hi @cyb3n3tic,
We are working on investigating the issue, we will update you.
Thanks

[v-sudkharat]

Hey @cyb3n3tic, Based on your provided issue description and screenshots, it looks the required columns does not get created, which defined into the Rule.
Just tried to repro the issue and found that in our workspace the required columns get created and due to that it not getting issue while creation of rule :

So, can you check the AzureDiagnostics table schema or share schema with us, to check those columns are get created or not. Thanks!

[cyb3n3tic]

Thanks @v-sudkharat - I will follow up on this shortly. Not sure why columns would be missing/not created though?
I will review and see what I can make of this.

[v-sudkharat]

@cyb3n3tic, Great. Please let us know once it done. Thanks!

[v-rusraut]

Hi @cyb3n3tic,
We are waiting for your response.
Thanks

[cyb3n3tic]

Sorry @v-rusraut , I am currently away so won't be able to follow up for another two weeks

[cyb3n3tic]

Sorry @v-sudkharat @v-rusraut , finally back and able to look at this again. The suggestion above is showing the process for creating a new NRT rule whereas the process I am trying to follow is using templates, specifically Front Door Premium WAF - SQLi Detection and Front Door Premium WAF - XSS Detection templates.

When using these templates I right click the template to create a new rule

Following this, when I try to setup the Rule Logic I am seeing the errors I mentioned - 'where' operator: Failed to resolve scalar expression named 'details_msg_s'

Attempting to view query results generates the error below:
'where' operator: Failed to resolve scalar expression named 'details_msg_s'
Request id: 8e6893ab-4880-4c49-b826-d090b604fb8d

Should I expect to be able to use these templates with Application Gateway / WAF ? I currently use templates "App GW WAF - Path Traversal Attack" and "App GW WAF - Code Injection" , ideally I want to implement similar templates for SQLi Detection and XSS Detection, I'm not clear whether the Front Door Premium WAF templates should work or I am barking up the wrong tree?

[cyb3n3tic]

Sorry @v-sudkharat @v-rusraut , finally back and able to look at this again. The suggestion above is showing the process for creating a new NRT rule whereas the process I am trying to follow is using templates, specifically Front Door Premium WAF - SQLi Detection and Front Door Premium WAF - XSS Detection templates.

When using these templates I right click the template to create a new rule

Following this, when I try to setup the Rule Logic I am seeing the errors I mentioned - 'where' operator: Failed to resolve scalar expression named 'details_msg_s'

Attempting to view query results generates the error below:
'where' operator: Failed to resolve scalar expression named 'details_msg_s'
Request id: 8e6893ab-4880-4c49-b826-d090b604fb8d

Should I expect to be able to use these templates with Application Gateway / WAF ? I currently use templates "App GW WAF - Path Traversal Attack" and "App GW WAF - Code Injection" , ideally I want to implement similar templates for SQLi Detection and XSS Detection, I'm not clear whether the Front Door Premium WAF templates should work or I am barking up the wrong tree?

[v-rusraut]

Hi @cyb3n3tic,
Before configuring the Analytical rule, data should be flow into respective data source. Please refer below screen shot, in highlighted section, in data source data is available for sources Application Gateways and Front Doors , so we are able to create Analytical Rule. please verify at your end it is greyed out or showing in green color.

There are data connector configuration issue, so data is not flowing into respective data source, please refer below screen shot.
In highlighted section data type showing greyed out due the data not available. Also check your data connector status and refer the data connector instruction page for configuration.

Thanks

[v-sudkharat]

Hi @cyb3n3tic, Did you get a chance to look at above shared solution? We are waiting for your response. Thanks!

[v-sudkharat]

Hi @cyb3n3tic, Gentle Reminder: We are waiting for your response on this issue. If you still need to keep this issue active, please respond to it in the next 2 days. If we don't receive a response by 12-07-2024 date, we will be closing this issue.
Thanks!

[cyb3n3tic]

Hi again. @v-rusraut @v-sudkharat

We don't have front door so it's just Application Gateway that is applicable - is this template usable nonetheless? I know by template name probably not but I'm looking for coverage of SQLi somehow.

[v-sudkharat]

Hi @cyb3n3tic, Sorry for delay in response.
While creating the analytic rule, could you please update the below query with existing one and let us know if it works for you:-
UpdatedQuery.txt

Thanks!

[v-sudkharat]

Hi @cyb3n3tic, Gentle Reminder: We are waiting for your response on this issue. If you still need to keep this issue active, please respond to it in the next 2 days. If we don't receive a response by 07-08-2024 date, we will be closing this issue.
Thanks!

[cyb3n3tic]

Hi @v-sudkharat

So, tried that and get 'project' operator: Failed to resolve scalar expression named 'trackingReference_s'

I think the main issue is the lack of Frontdoor in our case - I will leave this now as not making any headway.

I was hoping that additional App GW templates for WAF beyond the current two would be possible, to align with those available for Front Door Premium to cover SQLi and XSS

Thanks for your input nonetheless

[v-sudkharat]

@cyb3n3tic, thank you for your suggestion on it. So, as nothing of us, closing this issue. If you still need support for this issue, feel free to re-open it any time. Thank you for your co-operation.