KQL Search

This KQL query is designed to identify tables in a database that have not received any new data in the past three days or more. Here's a simple breakdown of what the query does:

1. union withsource=BadTable *: This part combines all tables in the database into a single dataset, while keeping track of which table each piece of data came from by labeling it as BadTable.

2. where TimeGenerated > ago(3d): It filters the data to include only entries that were generated in the last three days.

3. summarize Entries = count(), last_log = datetime_diff("second",now(), max(TimeGenerated)), estimate = sumif(_BilledSize, _IsBillable==true) by BadTable: This section aggregates the data by each table (BadTable). It calculates:

   • Entries: The total number of entries for each table.
   • last_log: The time difference in seconds between now and the most recent data entry for each table.
   • estimate: The sum of the billed size for entries that are billable.

4. where last_log >= 259200: It filters the results to include only tables where the most recent data entry was 259,200 seconds (or 3 days) ago or more.

5. project BadTable: Finally, it selects and displays only the names of the tables that meet the criteria.

In summary, this query identifies and lists the names of tables that haven't had any new data entries in the last three days or more.