Oct 10 2019
11:59 AM
- last edited on
Dec 23 2021
10:02 AM
by
TechCommunityAP
Oct 10 2019
11:59 AM
- last edited on
Dec 23 2021
10:02 AM
by
TechCommunityAP
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
Go to Log Analytics and Run Query
SolutionName | maxTimeGenerated | AggregatedValue |
---|---|---|
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 12 2019 02:00 AM
Yes i am referring to the tables that reside due to the connectors
this doesnot show me all of my tables (office365, aws ...etc)
Oct 13 2019 10:49 AM
Hello @omrip
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 02:02 AM
i think this needs to be done the other way around as i want to get alert on a a source type that stopped emitting logs.
any suggestions?
Oct 15 2019 08:36 AM
Hello @omrip
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?
Thanks Clive
Mar 04 2020 01:30 AM
in regards with log source that stopped sending data
can we perform a statistical count of decrease in 50% comparison from the last hour?
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)