list of reporting sourcetypes

%3CLINGO-SUB%20id%3D%22lingo-sub-906926%22%20slang%3D%22en-US%22%3Elist%20of%20reporting%20sourcetypes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-906926%22%20slang%3D%22en-US%22%3E%3CP%3Ehow%20can%20i%20create%20a%20list%20ot%20reporting%20sourcetypes%20and%20create%20an%20alert%20if%20one%20of%26nbsp%3B%20the%20sourcetypes%20is%20not%20reporting.%3C%2FP%3E%3CP%3Ei%20am%20separating%20the%20sourcetype%20from%20connector%20as%20the%20connector%20can%20be%20done%20with%20the%20Heartbeat%20table%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-906926%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ekql%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESentinel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-908004%22%20slang%3D%22en-US%22%3ERe%3A%20list%20of%20reporting%20sourcetypes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-908004%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423940%22%20target%3D%22_blank%22%3E%40omrip%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20you%20say%20%3CEM%3Esourcetypes%3C%2FEM%3E%20are%20you%20referring%20to%20the%20connectors%20and%20the%20Tables%20they%20provide%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ee.g.%20Syslog%20and%20the%20%3CSTRONG%3ESyslog%3C%2FSTRONG%3E%20Table%2C%20CEF%20and%20%3CSTRONG%3ECommonSecurityLog%3C%2FSTRONG%3E%20etc...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20so%2C%20this%20would%20show%20Tables%20that%20haven't%20reported%20in%20the%20past%20%3CSTRONG%3E24hrs%3C%2FSTRONG%3E.%26nbsp%3B%20Remember%20some%20tables%20may%20not%20report%20that%20often%20(if%20they%20are%20lightly%20used)%2C%20so%20you%20could%20exclude%20those%20or%20handle%20them%20differently%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Eunion%20withsource%20%3D%20tt%20*%0A%7C%20where%20TimeGenerated%20%26lt%3B%20now()%0A%7C%20where%20isnotempty(Type)%0A%7C%20summarize%20maxTimeGenerated%3Dmax(TimeGenerated)%20by%20Type%0A%7C%20where%20maxTimeGenerated%20%20%26lt%3B%20ago(24h)%0A%7C%20extend%20SolutionName%20%3D%20strcat(Type%2C%20'%3A%20LatestData%3A%20'%2C%20maxTimeGenerated)%0A%7C%20summarize%20AggregatedValue%20%3D%20count()%20by%20SolutionName%2C%20maxTimeGenerated%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA2WOPQvCMBCGd8H%25252F8G5GcRJdRAdBcBEXi3usRxtokpJcaCv%25252BeJMsfnS54d57n3uCUdagU1x7G1xJ2IMZi%25252Bnkha4mRyiUphMZcpLpgR2M7cT8EytvLJNueRDF0FJOfNBaOvUkaNn%25252F9PdxIX42c9wHpOYH%25252BV9C%25252FCorK1brOuOpZzIPXG0TOMpfpE7Wnl0pOUssMdviHKuej3FuMVuOoH%25252Bih6pyVKXgJpuQeKUNhkXW%25252B%25252F40JmWQdZwuR%25252BrSlylvlFaMzeQNlzUclG4BAAA%25253D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3ESolutionName%3C%2FTH%3E%0A%3CTH%3EmaxTimeGenerated%3C%2FTH%3E%0A%3CTH%3EAggregatedValue%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EWorkloadMonitoringPerf%3A%20LatestDate%3A%202018-10-30T15%3A50%3A20.4430000Z%3C%2FTD%3E%0A%3CTD%3E2018-10-30T15%3A50%3A20.443Z%3C%2FTD%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EServiceDesk_CL%3A%20LatestDate%3A%202018-12-21T20%3A28%3A44.9590000Z%3C%2FTD%3E%0A%3CTD%3E2018-12-21T20%3A28%3A44.959Z%3C%2FTD%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EKubeServices_CL%3A%20LatestDate%3A%202019-01-22T01%3A06%3A56.0000000Z%3C%2FTD%3E%0A%3CTD%3E2019-01-22T01%3A06%3A56Z%3C%2FTD%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EKubeEvents_CL%3A%20LatestDate%3A%202019-04-16T22%3A44%3A11.3060000Z%3C%2FTD%3E%0A%3CTD%3E2019-04-16T22%3A44%3A11.306Z%3C%2FTD%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EKubePodInventory_CL%3A%20LatestDate%3A%202019-04-16T22%3A44%3A11.5090000Z%3C%2FTD%3E%0A%3CTD%3E2019-04-16T22%3A44%3A11.509Z%3C%2FTD%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-910460%22%20slang%3D%22en-US%22%3ERe%3A%20list%20of%20reporting%20sourcetypes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-910460%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20i%20am%20referring%20to%20the%20tables%20that%20reside%20due%20to%20the%20connectors%3C%2FP%3E%3CP%3Ethis%20doesnot%20show%20me%20all%20of%20my%20tables%20(office365%2C%20aws%20...etc)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-911258%22%20slang%3D%22en-US%22%3ERe%3A%20list%20of%20reporting%20sourcetypes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-911258%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423940%22%20target%3D%22_blank%22%3E%40omrip%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20above%20example%20query%2C%20was%20provided%20to%20show%20you%20only%20those%20Tables%20that%20haven't%20processed%20data%20in%20the%20last%20%3CSTRONG%3E24h%3C%2FSTRONG%3Ers%2C%20you%20could%20swap%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7C%20where%20maxTimeGenerated%20%20%26lt%3B%20ago(24h)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ETo%20%3CSTRONG%3E5m%3C%2FSTRONG%3E%20or%20%3CSTRONG%3E10m%3C%2FSTRONG%3E%20or%20whatever%20you%20are%20happy%20with.%26nbsp%3B%20I%20did%20this%20so%20you%20don't%20alert%20on%20too%20much%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20just%20list%20(all)%20available%20tables%20and%20their%20last%20sent%20TimeGenerated%20info%2C%20please%20try%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Eunion%20withsource%20%3D%20tt%20*%0A%7C%20where%20TimeGenerated%20%26lt%3B%20now()%0A%7C%20where%20isnotempty(Type)%0A%7C%20summarize%20maxTimeGenerated%3Dmax(TimeGenerated)%20by%20Type%0A%7C%20sort%20by%20maxTimeGenerated%20asc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-915017%22%20slang%3D%22en-US%22%3ERe%3A%20list%20of%20reporting%20sourcetypes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915017%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20think%20this%20needs%20to%20be%20done%20the%20other%20way%20around%20as%20i%20want%20to%20get%20alert%20on%20a%20a%20source%20type%20that%20stopped%20emitting%20logs.%3C%2FP%3E%3CP%3Eany%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-915857%22%20slang%3D%22en-US%22%3ERe%3A%20list%20of%20reporting%20sourcetypes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915857%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423940%22%20target%3D%22_blank%22%3E%40omrip%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20thought%20my%20first%20reply%20addressed%20that%20scenario%2C%20I%20only%20showed%20%3CSTRONG%3Etables%3C%2FSTRONG%3E%20that%20hadn't%20sent%20logs%20within%20the%20past%2024hrs%20(we%20would%20have%20no%20idea%20if%20they%20will%20never%20send%20them%20again)%3B%20so%20maybe%20set%20the%20duration%20to%203%20or%207%20days%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAre%20you%20trying%20to%20look%20at%20a%20particular%20Table%20(if%20so%20which%20one)%20or%20all%20possible%20Tables%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20Clive%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1208429%22%20slang%3D%22en-US%22%3ERe%3A%20list%20of%20reporting%20sourcetypes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1208429%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20regards%20with%20log%20source%20that%20stopped%20sending%20data%3C%2FP%3E%3CP%3Ecan%20we%20perform%20a%20statistical%20count%20of%20decrease%20in%2050%25%20comparison%20from%20the%20last%20hour%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1219867%22%20slang%3D%22en-US%22%3ERe%3A%20list%20of%20reporting%20sourcetypes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1219867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F423940%22%20target%3D%22_blank%22%3E%40omrip%3C%2FA%3E%26nbsp%3Bwe%20do%20something%20similar%2C%20though%20this%20won't%20produce%20results%20for%20newly%20added%20log%20sources%20i.e.%20where%20current%20%26gt%3B%20previous%20and%20any%20excluded%20log%20types.%20Any%20feedback%20welcomed%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%2F%2F%20Log%20sources%20that%20haven't%20reported%20in%20the%20last%20%3CGRACEPERIOD%3E%20time%0Alet%20graceperiod%20%3D%201h%3B%0Alet%20ExcludeTypes%20%3D%20dynamic(%5B%22SecurityAlert%22%5D)%3B%0Aunion%20withsource%20%3D%20tt%20*%0A%7C%20where%20Type%20!in%20(ExcludeTypes)%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(max_of(24h%2C%20graceperiod*2))%0A%7C%20where%20isnotempty(Type)%0A%7C%20extend%20period%20%3D%20iff(TimeGenerated%20%26gt%3B%20ago(graceperiod)%2C%20%22Current%22%2C%20%22Previous%22)%0A%7C%20summarize%20previous%20%3D%20countif(period%20%3D%3D%20%22Previous%22)%2C%20current%20%3D%20countif(period%20%3D%3D%20%22Current%22)%2C%20LatestEvent%20%3D%20max(TimeGenerated)%20%20by%20Type%0A%7C%20where%20previous%20%26gt%3B%200%20and%20(isempty(current)%20or%20current%20%3D%3D%200)%3C%2FGRACEPERIOD%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

7 Replies
Highlighted

@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

 

Highlighted

@Clive Watson 

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

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

 

Highlighted

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

 

Highlighted

@Clive Watson 

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?

 

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 

 

 

Highlighted

 

@Clive Watson 

in regards with log source that stopped sending data

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

 

Highlighted

@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)