Apparently the contents from the screenshots taken is not easy to read and some zoom-in is required. Layer 8 issue.
Table of Contents
You/your company has just signed up for a SIEM/SOAR solution where data from multiple, different external systems/platforms is aggregated, analyzed and (worst case) processed into Security Incidents?
Your platform is Microsoft Sentinel where you have one landing page/entry point for security alerts/security incidents from multiple other security products and the option to add custom detection rules (Analytic rules, hunting queries) to query event data from other platforms?
Continue reading and congratulations, you are all set to query big data sets originating from multiple products/platforms to hunt for anomalies and malicious/suspicious behavior – plus you are able to enrich security incidents with this data sets!
The challenge - SecOps transparency
One topic you might not have resolved within your Microsoft Sentinel platform is transparency when it comes to security incident operations.
Below I’ve added a (freestyle) visualization to introduce to the key elements and outline the intention of this blog post.
Microsoft Sentinel can collect raw event data and have already aggregated security alerts ingested from external security solutions. Ingestion and retention of raw event data costs money, but your security operations people can make great use of it:
Your raw event data is queries by the Microsoft Sentinel detection logic (Analytic rules, Hunting queries). Ensure that you make use of the data ingested in your Microsoft Sentinel’s log analytic workspace (the box where the data resides in and is accessible to MS Sentinel).
Best case is that one of your detection logic items detect an attack at its earliest possible state and you are able to mitigate/enable countermeasures to respond to the attack.
You don’t ingest data just for fun.
AI, Machine Learning, ChatGPT, …. is great stuff but have their limits/boundaries. When it comes to a real judgment; there’s your security operation’s organizational unit. Be it one or many or an external SOC provider. They mainly work with Security Incidents in Microsoft Sentinel but have the knowledge to connect the dots and further analyze security incidents generated in your environment plus evaluate the possible impact of a security incident on business processes and other business units.
The ones paying the bills, your salary, acts as sponsors for a (security) project, owns the risk and is accountable for a proper risk management. Stakeholders have questions – and that’s where I try to focus in this blog post.
What are the options to report security incident numbers and figures from your Microsoft Sentinel SIEM/SOAR solution for the management/sponsors/other stakeholders?
How to address the challenge
In this chapter I outline the idea on how to report some interesting numbers around the security incidents processed by the Security operations team. I won’t go into details when it comes to the query used or configuration done within the workbooks (leave a comment and I’ll dig into this topic in a dedicated blog post).
Azure workbooks are a great way to visualize large sets of data across multiple tables. Azure workbooks are not exclusive to Microsoft Sentinel. They are also available for other Azure platforms (e.g. MS Defender for Cloud, Azure Monitor to name a few)
Usually, Workbook templates can be found directly in Azure in the respective template repository or as code in the public repository from Microsoft on GitHub Azure-Sentinel/Workbooks at master · Azure/Azure-Sentinel (github.com).
New to KQL?
Must Learn KQL Part 1: Tools and Resources – Azure Cloud & AI Domain Blog (azurecloudai.blog)
Looking for multiple KQL detection/query use cases?
reprise99/Sentinel-Queries: Collection of KQL queries (github.com)
Wanna challenge the experts in a live session?
Welcome to KQL Cafe | [“KQL Cafe”]
KQL is used to query for the data sets within the workbook. The super nice fact is that KQL (Kusto Query Language) can be used literally every time in Microsoft Sentinel when it comes to search in large data sets, be it to detect anomalies, malicious patterns (detection logic) or for the intention to visualize data sets!
General Workbook structure
I decided to build my Workbook in a simple but flexible way.
The whole workbook content is controlled by parameters. Based on the parameters set, specific groups are hidden/shown within the Workbook. This allows to have tabs available.
Furthermore, parameters control the Log analytic workspaces queried. This was relevant for my scenario (SOC MSSP) as we work with multiple Microsoft Sentinel workspaces.
Parameter values are available within the KQL queries.
Reporting use case - simple numbers
The first use case covered simply aggregates the number of generated security incidents within a defined time period.
As you can see in above’s screenshot the workbook can be set for one or multiple Microsoft Sentinel workspaces. The KQL query transparently handles the selection made.
Workbook groups are conditionally shown based on the selected tabs.
The KQL query to gather the data is quite basic but delivers the results expected. It’s possible to export the results to a Microsoft Excel spreadsheet or run the query in the Log Analytic workspace (options on the right)
In the picture below we can easily visualize the security incidents generated during the past month, grouped by severity level. This lets us see where we had peaks and further investigate the root cause.
We can go a step ahead and report the security incident volume compared over the past couple of months grouped by severity level.
Reporting use case - assignments
You have a SOC from an external service provider? You might be interested in the assignments of security incidents to your employees compared to the assignments to the external SOC (where you pay for).
Let’s be even more curious and check which person has the most security incidents assigned!
Reporting use case - Security incident resolution time
We can go a bit more into micro management and report the time it took the security analysts to resolve a security incident (timestamp incident closed – timestamp incident created) – grouped by severity levels.
This query helps to identify long running security incidents. Compared to Service Request (orders) in the IT Service Management area, security incidents should be resolved within hours or a few days if end-users/external suppliers must be involved.
Reporting use case - top security incident providers
In case you have multiple external security platforms connected to Microsoft Sentinel, it would be interesting to see how many security incidents originate from these platforms. The query below visualizes the Security Alert provider of the security incidents.
And yes, Microsoft Sentinel is still labeled as Azure Sentinel. Apparently some products still have their old names assigned in log analytics.
Reporting use case - compare security incident volume against a contract
The last use case I’d like to showcase in this blog post is the comparison of the security incidents generated per month and compared to a baseline value. The baseline value might be an evaluated/estimated/projected security incident volume.
The query below visualizes the deviation from a defined baseline volume to the real values.
Workbooks are a great way to visualize available data stored in the log analytics workspace. As of the writing of this blog post (20.02.2023), some limitations are around when it comes to the sizing of the visualized elements and the sizing of the labels displayed below any bar chart (and other visualization types).
Nevertheless it’s a good alternative to any other (possibly expensive) products, plus it uses a query language (KQL) which can be used for big data analysis and aggregation jobs around Azure/Microsoft Sentinel.
If you are interested in more details of how the data has been queried (KQL) and what to consider when querying the security incident’s table in Log Analytics let me know by dropping a comment here. Happy to write a dedicated post about this topic.
- Sentinel Automation use case – custom Alerting with LogicApps - March 6, 2023
- Microsoft Sentinel Security Incident statistics with Workbooks - February 20, 2023
- Sentinel Incident Automation – Playbook dependencies - January 16, 2023
cibermanchego · March 9, 2023 at 11:54
Hi Christoph, very nice post.
Would it be possible to share the queries you used for those visualizations?
Or have you published the workbook itself?
Thank you and great work!
Christoph Düggeli · March 9, 2023 at 16:42
Thanks for your valuable feedback! Apparently I am not allowed to provide you with the exact query content. What I can and will do is a dedicated post focused on the anatomy of the queries behind the visualizations shown in this blog post.