Forum Discussion

omrip's avatar
omrip
Copper Contributor
Oct 10, 2019

list of reporting sourcetypes

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

KQL

7 Replies

Sort By
  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Oct 11, 2019

    omrip 

     

    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

     

    • omrip's avatar
      omrip
      Copper Contributor
      Mar 04, 2020

       

      CliveWatson 

      in regards with log source that stopped sending data

      can we perform a statistical count of decrease in 50% comparison from the last hour?

       

      • pemontto's avatar
        pemontto
        Brass Contributor
        Mar 10, 2020

        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)

         

    • omrip's avatar
      omrip
      Copper Contributor
      Oct 12, 2019

      CliveWatson 

      Yes i am referring to the tables that reside due to the connectors

      this doesnot show me all of my tables (office365, aws ...etc)

       

      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Oct 13, 2019

        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

         

Resources