KQL Search

Query Details

HomeAI ToolsDevice Query

Multiple Tables No Ingest

Query

//Reporting on Multiple tables that are not ingesting data.
//Replace the tables with the tables you want to monitor.
//Want to add more tables? Just copy, paste, and modify the let block to your heart's content.

let table1= OfficeActivity
| where isnotempty(Type)
| summarize maxTimeGenerated=max(TimeGenerated) by Type
| where maxTimeGenerated  < ago(48h);
let table2= SecurityAlert
| where isnotempty(Type)
| summarize maxTimeGenerated=max(TimeGenerated) by Type
| where maxTimeGenerated  < ago(72h);
let table3= DeviceInfo
| where isnotempty(Type)
| summarize maxTimeGenerated=max(TimeGenerated) by Type
| where maxTimeGenerated  < ago(72h);
let table4= SigninLogs
| where isnotempty(Type)
| summarize maxTimeGenerated=max(TimeGenerated) by Type
| where maxTimeGenerated  < ago(14d);
union isfuzzy=true 
table1,table2,table3,table4

Explanation

This query is reporting on multiple tables that are not ingesting data. It retrieves the maximum time generated for each table and filters out any records that are older than a certain time period. The tables being monitored are OfficeActivity, SecurityAlert, DeviceInfo, and SigninLogs. If you want to monitor additional tables, you can simply copy, paste, and modify the "let" block. The query then combines the results from all tables using the "union" operator.

Details

Rod Trent profile picture

Rod Trent

Released: October 2, 2023

Tables

OfficeActivitySecurityAlertDeviceInfoSigninLogs

Keywords

Devices,Intune,User

Operators

let|whereisnotemptysummarizemaxby<agounionisfuzzy

Actions