Log Analytics - KQL - Log Quiet

As businesses increasingly rely on Azure for their cloud solutions, maintaining an up-to-date and secure environment becomes crucial. One critical aspect is ensuring that log data is consistently received and analyzed. In this blog post, we'll dive into using Azure's Kusto Query Language (KQL) to identify tables that have not received logs in the last day. By pinpointing these gaps, you can proactively address potential issues in your logging infrastructure and ensure comprehensive monitoring of your cloud resources. Let's explore how KQL can help you maintain a robust and reliable logging system.

union withsource = _TableName *
| where TimeGenerated > ago(10d)
| summarize last_log = datetime_diff("day", now(), max(TimeGenerated))  by _TableName
| where last_log > 1
| project ['Table Name'] = _TableName, ['Last Record Received'] = last_log
| order by ['Last Record Received']  desc

1.

union withsource = _TableName *

This part of the query combines all tables in the database into a single result set. The withsource = _TableName clause adds a column named _TableName to the result, which contains the name of the table each record came from.

2.

| where TimeGenerated > ago(10d)

This filters the combined result set to include only records where the TimeGenerated field is within the last 10 days. The ago(10d) function returns the date and time 10 days ago from the current time.

3.

| summarize last_log = datetime_diff("day", now(), max(TimeGenerated))  by _TableName

Here, the query summarises the data by table name:

• max(TimeGenerated) finds the most recent TimeGenerated value for each table.
• datetime_diff("day", now(), max(TimeGenerated)) calculates the difference in days between the current date and the most recent TimeGenerated value.
• last_log is the name given to this calculated difference.

4.

| where last_log > 1

This filters the summarised results to include only those tables where the difference in days (last_log) is greater than 1. This means it filters out tables that have received records within the last day.

5.

| project ['Table Name'] = _TableName, ['Last Record Received'] = last_log

This part of the query renames the columns for better readability:

• _TableName is renamed to Table Name.
• last_log is renamed to Last Record Received.

6.

| order by ['Last Record Received']  desc

Finally, the query orders the results by the Last Record Received column in descending order, so tables with the most days since their last record are listed first.

Summary

This query identifies tables in the database that have not received any new records in the last day. It lists these tables along with the number of days since their last record, sorted in descending order.

Example Output

This output indicates that Table1 has not received any new records for 5 days, Table2 for 3 days, and Table3 for 2 days.

It's a simple tried and tested method which won't fail you.

Either run it manually or implement it into a recurring logic app to send the message to you by any means such as directly to Teams, Email (don't recommend) or Service Now as a ticket.