Azure Security Center Recommendations Log Analytics Query syntax

%3CLINGO-SUB%20id%3D%22lingo-sub-325577%22%20slang%3D%22en-US%22%3EAzure%20Security%20Center%20Recommendations%20Log%20Analytics%20Query%20syntax%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325577%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMy%20customer%20wants%20to%20write%20%3CSTRONG%3Ecustom%20Log%20Search%20Queries%3C%2FSTRONG%3E%20(in%20Log%20Analytics)%20for%20Azure%20SQL%20for%20the%20following%20Scenarios%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%E2%80%A2%20Log%20failures%2C%20manual%20logging%20shut%20down%20and%20attempts%20to%20purge%3CBR%20%2F%3E%E2%80%A2%20Attempts%20to%20access%20OS%20functionality%20via%20the%20database%3CBR%20%2F%3E%E2%80%A2%20Known%20attack%20profiles%2C%20such%20as%20Buffer%20overflow%2C%20Denial%20of%20Service%2C%20SQL%20inject%3CBR%20%2F%3E%E2%80%A2%20Use%20of%20the%20Application%20ID%20(ApplID)%20from%20a%20source%20other%20than%20the%20defined%20owner%20Application%20location%20(based%20on%20host%20name%20or%20IP%20address%20of%20App%20%2F%20Reporting%20Server)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CU%3E%3CSTRONG%3EPlease%20Note%3A%3C%2FSTRONG%3E%3C%2FU%3E%20I%20know%20%3CSTRONG%3EAdvanced%20Threat%20Protection%3C%2FSTRONG%3E%20covers%20some%20of%20the%20scenarios%20mentioned%20here%20e.g.%20detecting%20SQL%20Injections%2C%20etc%E2%80%A6%20But%20the%20customer%20wants%20custom%20queries%20for%20all%20of%20these%20scenarios.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20the%20following%20Questions%3A%3CBR%20%2F%3E%E2%80%A2%20Which%20%3CSTRONG%3EAUDIT%20GROUPS%3C%2FSTRONG%3E%20should%20I%20enable%20to%20capture%20more%20Logs(apart%20from%20the%203%20that%20are%20enabled%20by%20default)%20so%20that%20I%20can%20write%20queries%20for%20the%20above%20use%20cases%20using%20KQL%20on%20the%20logs%20collected%20%3F%3CBR%20%2F%3E%E2%80%A2%20If%20we%20%3CSTRONG%3Ekeep%20ATP%20aside%20and%20assume%20that%20SQL%20Server%20is%20running%20on%20a%20VM%20in%20Azure%3C%2FSTRONG%3E%2C%20how%20would%20we%20achieve%20the%20above%20use%20cases%20based%20on%20the%20logs%20collected%20via%20the%20MMA%20agent%20installed%20on%20the%20VM%20%3F%3CBR%20%2F%3E%E2%80%A2%20The%20customer%20is%20using%20these%20custom%20queries%20to%20get%20appropriate%20result%20set%20and%20in%20turn%20to%20create%20PowerBI%20Dashboards%20which%20they%20want%20to%20share%20with%20their%20customers%2C%20%3CSTRONG%3Ehow%20can%20I%20get%20ATP%20data%2F%20recommendation%20outside%20the%20Azure%20Portal%20so%20that%20customer%20can%20create%20visualizations%20on%20top%20it%20and%20share%20with%20it%E2%80%99s%20customers%3C%2FSTRONG%3E.%20%3CBR%20%2F%3E%E2%80%A2%20%3CU%3E%3CSTRONG%3EPlease%20Note%3A%3C%2FSTRONG%3E%3C%2FU%3E%20I%20have%20seen%20%3CSTRONG%3EAzure%20Security%20Centre%20REST%20API%20Documentation%3C%2FSTRONG%3E%20and%20I%20know%20%3CSTRONG%3EI%20can%20pull%20Recommendations%20and%20Tasks%20using%20these%20APIs%3C%2FSTRONG%3E%2C%20but%20that%E2%80%99s%20not%20what%20the%20customer%20is%20looking%20for.%20Customer%20wants%20the%20underlying%20data%20and%20a%20custom%20query%20on%20top%20it%20which%20detects%20the%20security%20incident.%20I%20know%20these%20incidents%20are%20generated%20by%20complex%20ML%20algorithm%20running%20under%20the%20hood%2C%20but%20I%20hope%20I%20was%20able%20to%20put%20across%20the%20customer%E2%80%99s%20expectation%20clearly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20let%20me%20know%20your%20inputs%20on%20what%E2%80%99s%20possible%20and%20pointers%20on%20how%20to%20achieve%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-325577%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Hi

 

My customer wants to write custom Log Search Queries (in Log Analytics) for Azure SQL for the following Scenarios:

 

• Log failures, manual logging shut down and attempts to purge
• Attempts to access OS functionality via the database
• Known attack profiles, such as Buffer overflow, Denial of Service, SQL inject
• Use of the Application ID (ApplID) from a source other than the defined owner Application location (based on host name or IP address of App / Reporting Server)

 

Please Note: I know Advanced Threat Protection covers some of the scenarios mentioned here e.g. detecting SQL Injections, etc… But the customer wants custom queries for all of these scenarios.

 

I have the following Questions:
• Which AUDIT GROUPS should I enable to capture more Logs(apart from the 3 that are enabled by default) so that I can write queries for the above use cases using KQL on the logs collected ?
• If we keep ATP aside and assume that SQL Server is running on a VM in Azure, how would we achieve the above use cases based on the logs collected via the MMA agent installed on the VM ?
• The customer is using these custom queries to get appropriate result set and in turn to create PowerBI Dashboards which they want to share with their customers, how can I get ATP data/ recommendation outside the Azure Portal so that customer can create visualizations on top it and share with it’s customers.
Please Note: I have seen Azure Security Centre REST API Documentation and I know I can pull Recommendations and Tasks using these APIs, but that’s not what the customer is looking for. Customer wants the underlying data and a custom query on top it which detects the security incident. I know these incidents are generated by complex ML algorithm running under the hood, but I hope I was able to put across the customer’s expectation clearly.

 

Please let me know your inputs on what’s possible and pointers on how to achieve it.

0 Replies