Home

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%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
omrip
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

5 Replies

@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

 

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

 

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

 

@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 

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies