This article is contributed. See the original author and article here.

More and more Azure 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.

As the Azure Sentinel ingestion price includes 90 days of retention for free, the option of keeping the logs for longer periods in Azure Data Explorer is preferred by many (see Using Azure Data Explorer for long term retention of Azure Sentinel logs – Microsoft Tech Community). 

 

Even though the Azure 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 Azure 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:

 

ADXvsLA Workbook

 

When you open the workbook, you can select the following parameters:

• the ADX cluster and database


• the Azure 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.

 

 

Raw Tables

When you ingest logs from Azure 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 Azure 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 Azure 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.

Please read the accompanying readme.md file on GitHub to set it up.

 

We hope you find these tools useful! If you have any suggestions for improving this content or any questions, please leave us a comment.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.