More and more Microsoft Sentinel customers are opting for long-term retention of their logs in Azure Data Explorer (ADX), either due to compliance regulations, or because they still want to be able to perform investigations on their archived logs in the event of a security incident.
Even though the Microsoft Sentinel + ADX solution requires little to no maintenance, we wanted to provide a solution for our customers to keep an eye on the number of events and overall status of their ADX clusters and databases. For this reason, we have created two tools: the ADXvsLA workbook and the ADX Health Playbook. The workbook will allow you to have a look at the number of logs on Microsoft Sentinel & ADX and the overall health of your ADX cluster. The playbook will send you a warning if an unexpected delay in the ingestion of ADX is detected.
Below, we will describe both in more detail:
When you open the workbook, you can select the following parameters:
the ADX cluster and database
the Microsoft Sentinel workspace from which the logs are exported to the aforementioned ADX cluster,
as well as the time range for which you want to see data
Use the Show Help toggle to see a detailed explanation of each section.
When you ingest logs from Microsoft Sentinel to ADX, the logs are first ingested into an intermediate table with raw data. This raw data is updated by a function with an update policy and is saved to its destination table with the correct mapping. Afterwards, the data is deleted, which is why you will typically see that these raw tables are empty. The retention policy should also be set for 0 days.
Final ADX Tables
In this section, you will see information about the final ADX tables, which have the right schema and can be queried from Microsoft Sentinel. You will find information regarding the row count, size, retention policy and hot cache size etc.
Select one of the table names to generate the comparison section. This is where you can see the differences between the table on ADX and on your Log Analytics workspace. Then, select the time range for which you want to see the comparison.
In the table you will find:
The number of entries in ADX, in Log Analytics, and the difference in number of logs between them.
How long it has been since the last log was received
The timestamp of the last logs.
The number of new logs received in Log Analytics since the last log in ADX was received
Notice the New in Log Analytics column
In the screenshot, you can see there are 52 logs in the "New in Log Analytics" column. This means that, at the time we compared the tables, there were 52 entries that had not reached ADX yet. If this happens, you should compare the timestamp and the difference for the last log that was received. In this case, it is around 15 minutes. Delays of 30 minutes or less are expected, so this means your tables are working as expected.
It is also possible that you see a negative number in the New in Log Analytics column. This could happen if, due to the lag in ADX, there were Log Analytics logs from the previous period that were received in ADX during the current period. Let's suppose that you ingested 1000 logs in Log Analytics on the previous 24h window, but only 990 reached ADX in that period; and then you ingested 1000 logs again on the current 24h window, and all those logs, plus the 10 logs from the previous day, reached ADX. In this case, you will see that the "New in Log Analytics" column would say -10. In these cases, you only need to look at the LastTM difference. If it is around 30 minutes or less, then it will be fine.
Finally, at the bottom of the workbook you will see metrics regarding events received, events dropped, received data, volume and other metrics.
ADX Health Playbook
The ADX Health Playbook compares the number of logs in your Microsoft Sentinel tables and ADX tables periodically (every 24h by default) and sends you a warning via email if it detects a difference in the number of logs that may require your attention (that is, in the "New in Log Analytics" column mentioned previously). As it takes logs a few minutes to reach ADX after having been ingested into Log Analytics, the query in the playbook by default looks back at the period between the last 25h and last 30min.