Oct 10 2019 11:59 AM
Oct 10 2019 11:59 AM
how can i create a list ot reporting sourcetypes and create an alert if one of the sourcetypes is not reporting.
i am separating the sourcetype from connector as the connector can be done with the Heartbeat table
Oct 11 2019 01:07 AM
When you say sourcetypes are you referring to the connectors and the Tables they provide?
e.g. Syslog and the Syslog Table, CEF and CommonSecurityLog etc...
If so, this would show Tables that haven't reported in the past 24hrs. Remember some tables may not report that often (if they are lightly used), so you could exclude those or handle them differently?
union withsource = tt * | where TimeGenerated < now() | where isnotempty(Type) | summarize maxTimeGenerated=max(TimeGenerated) by Type | where maxTimeGenerated < ago(24h) | extend SolutionName = strcat(Type, ': LatestData: ', maxTimeGenerated) | summarize AggregatedValue = count() by SolutionName, maxTimeGenerated
|WorkloadMonitoringPerf: LatestDate: 2018-10-30T15:50:20.4430000Z||2018-10-30T15:50:20.443Z||1|
|ServiceDesk_CL: LatestDate: 2018-12-21T20:28:44.9590000Z||2018-12-21T20:28:44.959Z||1|
|KubeServices_CL: LatestDate: 2019-01-22T01:06:56.0000000Z||2019-01-22T01:06:56Z||1|
|KubeEvents_CL: LatestDate: 2019-04-16T22:44:11.3060000Z||2019-04-16T22:44:11.306Z||1|
|KubePodInventory_CL: LatestDate: 2019-04-16T22:44:11.5090000Z||2019-04-16T22:44:11.509Z||1|
Oct 13 2019 10:49 AM
The above example query, was provided to show you only those Tables that haven't processed data in the last 24hrs, you could swap
| where maxTimeGenerated < ago(24h)
To 5m or 10m or whatever you are happy with. I did this so you don't alert on too much data.
To just list (all) available tables and their last sent TimeGenerated info, please try:
union withsource = tt * | where TimeGenerated < now() | where isnotempty(Type) | summarize maxTimeGenerated=max(TimeGenerated) by Type | sort by maxTimeGenerated asc
Oct 15 2019 08:36 AM
I thought my first reply addressed that scenario, I only showed tables that hadn't sent logs within the past 24hrs (we would have no idea if they will never send them again); so maybe set the duration to 3 or 7 days?
Are you trying to look at a particular Table (if so which one) or all possible Tables?
Mar 10 2020 06:58 AM
@omrip we do something similar, though this won't produce results for newly added log sources i.e. where current > previous and any excluded log types. Any feedback welcomed:
// Log sources that haven't reported in the last <graceperiod> time let graceperiod = 1h; let ExcludeTypes = dynamic(["SecurityAlert"]); union withsource = tt * | where Type !in (ExcludeTypes) | where TimeGenerated > ago(max_of(24h, graceperiod*2)) | where isnotempty(Type) | extend period = iff(TimeGenerated > ago(graceperiod), "Current", "Previous") | summarize previous = countif(period == "Previous"), current = countif(period == "Current"), LatestEvent = max(TimeGenerated) by Type | where previous > 0 and (isempty(current) or current == 0)