Monitoring missing logs and log anomalies

%3CLINGO-SUB%20id%3D%22lingo-sub-1976478%22%20slang%3D%22en-US%22%3EMonitoring%20missing%20logs%20and%20log%20anomalies%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1976478%22%20slang%3D%22en-US%22%3E%3CP%3EShort%20of%20writing%20some%20code%20that%20queries%20the%20API%20directly%2C%20does%20anyone%20have%20suggestions%20for%20monitoring%20tables%20for%20missing%20logs%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20used%20to%20use%20%3CSTRONG%3Eunion%20*%3C%2FSTRONG%3E%20across%20tables%20to%20look%20for%20tables%20that%20were%20missing%20logs%2C%20however%20we're%20no%20longer%20able%20to%20create%20those%20scheduled%20alert%20rules%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fissues%2F1437%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fissues%2F1437%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EFailed%20to%20save%20analytics%20rule%20'Sentinel%20table%20missing%20logs'.%20Invalid%20data%20model.%20%5BProperties.Query%3A%20Scheduled%20alert%20rule%20query%20should%20not%20contain%20'search'%20or%20'union%20*'%5D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%2F%2F%20Find%20tables%20that%20previously%20had%20logs%20that%20are%20now%20missing%20logs%0Alet%20lookback%20%3D%202d%3B%0Alet%20IgnoreTables%20%3D%20dynamic(%5B'AzureMetrics'%2C%20'BehaviorAnalytics'%2C%20'ProtectionStatus'%2C%20'SecurityAlert'%2C%20'SecurityBaseline'%2C%20'Update'%2C%20'UpdateSummary'%2C%20'Usage'%2C%20'UserAccessAnalytics'%2C%20'UserPeerAnalytics'%5D)%3B%0Alet%20AllTables%20%3D%20union%20withsource%3Dtbl%20*%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(lookback)%0A%7C%20where%20tbl%20!in%20(IgnoreTables)%3B%0AAllTables%0A%7C%20where%20TimeGenerated%20%26lt%3B%20ago(lookback%2F2)%0A%7C%20summarize%20Previous%20%3D%20count()%20by%20tbl%0A%7C%20join%20kind%3Dleftanti%20(%0A%20%20%20%20AllTables%0A%20%20%20%20%7C%20where%20TimeGenerated%20%26gt%3B%20ago(lookback%2F2)%0A%20%20%20%20%2F%2F%20Ignore%20weekends%0A%20%20%20%20%7C%20where%20dayofweek(TimeGenerated)%2F1d%20between%20(1%20..%205)%0A%20%20%20%20%7C%20summarize%20Current%20%3D%20count()%20by%20tbl%0A)%20on%20tbl%0A%7C%20extend%0A%20%20%20%20timestamp%20%3D%20now()%2C%0A%20%20%20%20HostCustomEntity%20%3D%20tbl%2C%0A%20%20%20%20Current%20%3D%200%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1976528%22%20slang%3D%22en-US%22%3ERe%3A%20Monitoring%20missing%20logs%20and%20log%20anomalies%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1976528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F456796%22%20target%3D%22_blank%22%3E%40pemontto%3C%2FA%3E%26nbsp%3BCan%20you%20use%20the%20query%20in%20a%20workbook%20%2Cor%20look%20at%20the%20Data%20Collection%20Health%20Monitoring%20workbook%20to%20see%20if%20that%20works%3F%26nbsp%3B%20Granted%20you%20won't%20get%20the%20incident%20generated%20or%20any%20playbooks%20kicked%20off%20but%20you%20can%20check%20it%20daily.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Short of writing some code that queries the API directly, does anyone have suggestions for monitoring tables for missing logs?

 

We used to use union * across tables to look for tables that were missing logs, however we're no longer able to create those scheduled alert rules - https://github.com/Azure/Azure-Sentinel/issues/1437

 

Failed to save analytics rule 'Sentinel table missing logs'. Invalid data model. [Properties.Query: Scheduled alert rule query should not contain 'search' or 'union *']

 

// Find tables that previously had logs that are now missing logs
let lookback = 2d;
let IgnoreTables = dynamic(['AzureMetrics', 'BehaviorAnalytics', 'ProtectionStatus', 'SecurityAlert', 'SecurityBaseline', 'Update', 'UpdateSummary', 'Usage', 'UserAccessAnalytics', 'UserPeerAnalytics']);
let AllTables = union withsource=tbl *
| where TimeGenerated > ago(lookback)
| where tbl !in (IgnoreTables);
AllTables
| where TimeGenerated < ago(lookback/2)
| summarize Previous = count() by tbl
| join kind=leftanti (
    AllTables
    | where TimeGenerated > ago(lookback/2)
    // Ignore weekends
    | where dayofweek(TimeGenerated)/1d between (1 .. 5)
    | summarize Current = count() by tbl
) on tbl
| extend
    timestamp = now(),
    HostCustomEntity = tbl,
    Current = 0

 

1 Reply

@pemontto Can you use the query in a workbook ,or look at the Data Collection Health Monitoring workbook to see if that works?  Granted you won't get the incident generated or any playbooks kicked off but you can check it daily.