SOLVED
Home

Incidents from Potential malicious events and Data source anomalies

%3CLINGO-SUB%20id%3D%22lingo-sub-950539%22%20slang%3D%22en-US%22%3EIncidents%20from%20Potential%20malicious%20events%20and%20Data%20source%20anomalies%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-950539%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EWe%20are%20envisioning%20managing%20Sentinel%20mainly%20from%20Incidents%2C%20rather%20than%20manually%20watching%20the%20Sentinel%20console%20in%20the%20Azure%20portal.%20We%20would%20like%20incidents%20auto-created%20for%20serious%20Potential%20malicious%20events%20(those%20of%20the%20%22large%20orange%20dot%22%20and%20higher%2C%20not%20the%20'small%20orange%20dots'%20however).%20Also%20when%20there%20are%20significant%20spikes%20in%20any%20data%20source%20anomaly.%20How%20can%20we%20get%20visibility%20into%20these%20two%20Sentinel%20Overview%20page%20controls%20without%20watching%20the%20portal%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%2C%20John%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-951940%22%20slang%3D%22en-US%22%3ERe%3A%20Incidents%20from%20Potential%20malicious%20events%20and%20Data%20source%20anomalies%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-951940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379245%22%20target%3D%22_blank%22%3E%40John_Joyner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20click%20on%20either%20and%20you%20will%20be%20taken%20to%20the%20Logs%20blade%2C%20then%20press%20%22%2B%20New%20Alert%20Rule%22.%26nbsp%3B%20Name%20the%20alert%20and%20set%20your%20Thresholds%20etc...%20then%20trigger%20a%20%22send%20email%22%20Playbook%20(or%20send%20to%20any%20system%20supported%20by%20Logic%20Apps)%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20Playbook%20looking%20like%20this%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F149555i72177C4A629E058F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-956165%22%20slang%3D%22en-US%22%3ERe%3A%20Incidents%20from%20Potential%20malicious%20events%20and%20Data%20source%20anomalies%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-956165%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%3BThank%20you%20Clive%20for%20the%20tip%2C%20and%20we%20had%20considered%20that%20method%2C%20however%2C%20the%20resulting%20queries%20don't%20achieve%20the%20goals.%3C%2FP%3E%3CUL%3E%3CLI%3EIn%20the%20case%20of%20Potential%20Malicious%20Events%2C%20the%20query%20is%20specific%20to%20the%20geographic%20spot%20the%20event%20occurred%20in.%20To%20be%20useful%20as%20a%20rule%2C%20the%20geography%20should%20be%20neutral.%20If%20you%20remove%20the%20line%20from%20the%20query%20%22%7C%20%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Latitude%20%3D%3D%20__.__%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20Longitude%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%3CSPAN%3E___.__%22%20then%20you%20just%20get%20a%20big%20list%20of%20every%20event%20where%20%22%3C%2FSPAN%3E%3C%2FSPAN%3Eisnotempty(MaliciousIP)%22.%20That%20does%20not%20meet%20the%20desire%20where%20any%20large%20orange%20or%20any%20red%20hit%20that%20would%20appear%20on%20the%20map%20creates%20an%20incident%20where%20that%20geographic%20site%20is%20the%20only%20site%20in%20the%20alert.%20We%20were%20hoping%20for%20insight%20into%20the%20logic%20that%20determines%20there%20is%20a%20high%20number%20of%20hits%20from%20a%20specific%20geo%20and%20alert%20on%20that.%20(the%20same%20logic%20that%20is%20creating%20the%20map)%3C%2FLI%3E%3CLI%3EFor%20Data%20Source%20Anomalies%2C%20the%20query%20is%20specific%20to%20a%20time%20period%20for%20that%20specific%20data%20type.%20If%20you%20remove%20the%20custom%20time%20from%20the%20query%2C%20for%20example%20%22datetime%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E2019%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3E10%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3E25%3C%2FSPAN%3E%3CSPAN%3ET22%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E27%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3E47.031%3C%2FSPAN%3E%3CFONT%20face%3D%22inherit%22%3EZ)%22%20the%20query%20doesn't%20run.%26nbsp%3BWe%20were%20hoping%20for%20insight%20into%20the%20logic%20that%20determines%20which%20data%20type%20%3C%2FFONT%3Eexperiences%3CFONT%20face%3D%22inherit%22%3E%26nbsp%3Bthe%20highest%20%3C%2FFONT%3Eanomalies%3CFONT%20face%3D%22inherit%22%3E%26nbsp%3Band%20alert%20on%20that.%20(the%20same%20logic%20that%20is%20deciding%20which%20two%20data%20type%20to%20display%20on%20the%20overview%20page).%3C%2FFONT%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EJohn%20Joyner%3C%2FP%3E%3CP%3EMicrosoft%20MVP%20CDM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-965889%22%20slang%3D%22en-US%22%3ERe%3A%20Incidents%20from%20Potential%20malicious%20events%20and%20Data%20source%20anomalies%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-965889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379245%22%20target%3D%22_blank%22%3E%40John_Joyner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EQ1%20-%20How%20about%20(updated%2C%20to%20make%20it%20more%20Alert%20friendly)%3F%3C%2FP%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%2FH4sIAAAAAAAAA7WUUWvCMBDH34V%25252Bh5tPFgo69uxgWAeCzjGde47ttYa1F0mTuco%25252B%25252FBK1a7U6UbbH3D%25252B5%25252B%25252F3vjmjigoBnkV6v866SGgHAabTe7nqDwWQoYqfxBfipkEKYShZFPPC5xEDZZ11oDmguNIVj%25252BUrvJFbU9KBnAkrm3RdMhcLB8%25252B7swZAprnSIP0oRMJKg%25252BEArIq5neXzK%25252Bh9IKrueB04CQakdQaqqJZTtkSnsM8V%25252BR%25252BJR1CrPN4ZxrNUGsukdwy1l9z96Cbv5cgrFKns0YCuWJOct9ESaauIBs7F9P5P%25252Bk3%25252BBlxFLeMCFzo7ZqYjHHFXluinLKGiCgZZc5WeX94ytK8b019ZsZop4iBRgd7qQyJSPWSD50lJ6UDnU5W1fTFtmI5OFts5sR1YLlAjTfGlW2%25252Fisys1Ld1lcu8unNvn0HpcDL03wjIySLlXeqvTPBWb4K9quWC1eFKsLe7UynaZM8jXCQxxLjJnCcMYSbb%25252BGwKZuuTDPC4NOo90u%25252BA7vwz3cdjqbnEIq%25252B6h2IzQTdBrfjeog6JgFAAA%25253D%2Ftimespan%2FP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Eunion%20isfuzzy%3Dtrue%20%20%20%0A(W3CIISLog%0A%7C%20extend%20TrafficDirection%20%3D%20%22InboundOrUnknown%22%2C%20Country%3DRemoteIPCountry%2C%20Latitude%3DRemoteIPLatitude%2C%20Longitude%3DRemoteIPLongitude)%2C%20%0A(DnsEvents%0A%7C%20extend%20TrafficDirection%20%3D%20%22InboundOrUnknown%22%2C%20Country%3D%20RemoteIPCountry%2C%20Latitude%20%3D%20RemoteIPLatitude%2C%20Longitude%20%3D%20RemoteIPLongitude)%2C%0A(WireData%0A%7C%20extend%20TrafficDirection%20%3D%20iff(Direction%20!%3D%20%22Outbound%22%2C%22InboundOrUnknown%22%2C%20%22Outbound%22)%2C%20Country%3DRemoteIPCountry%2C%20Latitude%3DRemoteIPLatitude%2C%20Longitude%3DRemoteIPLongitude)%2C%20%20%20%20%20%0A(WindowsFirewall%0A%7C%20extend%20TrafficDirection%20%3D%20iff(CommunicationDirection%20!%3D%20%22SEND%22%2C%22InboundOrUnknown%22%2C%20%22Outbound%22)%2C%20Country%3DMaliciousIPCountry%2C%20Latitude%3DMaliciousIPLatitude%2C%20Longitude%3DMaliciousIPLongitude)%2C%20%20%20%20%20%0A(CommonSecurityLog%0A%7C%20extend%20TrafficDirection%20%3D%20iff(CommunicationDirection%20!%3D%20%22Outbound%22%2C%22InboundOrUnknown%22%2C%20%22Outbound%22)%2C%20Country%3DMaliciousIPCountry%2C%20Latitude%3DMaliciousIPLatitude%2C%20Longitude%3DMaliciousIPLongitude%2C%20Confidence%3DThreatDescription%2C%20Description%3DThreatDescription)%2C%20%20%20%20%0A(VMConnection%0A%7C%20where%20Type%20%3D%3D%20%22VMConnection%22%0A%7C%20extend%20TrafficDirection%20%3D%20iff(Direction%20!%3D%20%22outbound%22%2C%22InboundOrUnknown%22%2C%20%22Outbound%22)%2C%20Country%3DRemoteCountry%2C%20Latitude%3DRemoteLatitude%2C%20Longitude%3DRemoteLongitude)%0A%7C%20where%20isnotempty(MaliciousIP)%20and%20isnotempty(Country)%20and%20isnotempty(Latitude)%20and%20isnotempty(Longitude)%0A%7C%20summarize%20AggregatedValue%20%3D%20count()%20by%20Country%0A%2F%2F%7C%20where%20AggregatedValue%20%20%26gt%3B%20100%0A%7C%20sort%20by%20AggregatedValue%20%20desc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20have%20this%20as%20a%20Table%20or%20Chart%3F%26nbsp%3B%20Or%20filter%20out%20Countries%20with%20more%20than%20100%20events%20for%20example%20(see%20the%20commented%20out%20example)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F151984i87D3E1E62033957C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EQ2%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20adapt%20the%20query%20e.g.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EDnsEvents%0A%7C%20summarize%20Count%3Dcount()%20by%20Type%2C%20bin_at(TimeGenerated%2C%201h%20%2C%20startofday(ago(7d))%20)%0A%7C%20order%20by%20TimeGenerated%20asc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%20see%20across%20all%20Tables%20with%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20daystoSearch%20%3D%207d%3B%20%2F%2F%20Please%20enter%20how%20many%20days%20worth%20of%20data%20to%20look%20at%3F%0Aunion%20withsource%20%3D%20tt%20*%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(daystoSearch))%20and%20TimeGenerated%20%26lt%3B%20startofday(now())%0A%7C%20summarize%20Count%3Dcount()%20by%20Type%2C%20bin_at(TimeGenerated%2C%201h%20%2C%20startofday(ago(7d))%20)%0A%2F%2F%20ignore%20Perf%20table%20or%20other%20noisy%20tables%20%0A%7C%20where%20Type%20!in%20(%22Perf%22%2C%22NetworkMonitoring%22)%0A%2F%2F%20ignore%20event%20counts%20under%2010k%0A%7C%20where%20%20Count%20%26gt%3B%2010000%0A%7C%20order%20by%20TimeGenerated%20asc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%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%2FH4sIAAAAAAAAA12QMW%25252FCQAyFdyT%25252Bw2umpIoETAwt7dChUyuksleGGHIisas7p1Gq%25252Fvg6MBS4wdJZ9uf3XsOGioZk%25252BsEUdzVWWFYPmM2wbpgSg8U4otYeLclwmkWv0Wro3n9GMEWjegTZ83TSSVBBH6xO2sUdO88M99PJL%25252FqaI2MTWn5l4UjGFZ6QjKLp3rk5HTS%25252F1FIUIKluNh4vN0T7vChGeOralmL4YbxoJ7bajTUvsB2wGb64xDbIJ1l%25252BBSuxqFHeSlhWftihnkE4iLrmNcc9jLYNQyPU3AhEQxrOzYQLe34Md0GQZ%25252BNWVmbvbJ7X8U0lmMYgh%25252ByKzd%25252BeME5yEzqpHL2YH%25252F%25252BBZz8e1GLub%25252BxrHIdGY1fBUNr9AeZ73Y7NAQAA%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-975380%22%20slang%3D%22en-US%22%3ERe%3A%20Incidents%20from%20Potential%20malicious%20events%20and%20Data%20source%20anomalies%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-975380%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%3Byou%20are%20amazing%20sir!%20We%20were%20able%20to%20craft%20alert%20rules%20for%20both%20geographic%20and%20data%20source%20anomalies%20based%20on%20your%20reply.%20This%20means%20so%20much%20to%20us%2C%20thank%20you.%3C%2FP%3E%3CP%3EJohn%20Joyner%3C%2FP%3E%3CP%3EMicrosoft%20MVP%20Cloud%20%26amp%3B%20Datacenter%20Management%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-975423%22%20slang%3D%22en-US%22%3ERe%3A%20Incidents%20from%20Potential%20malicious%20events%20and%20Data%20source%20anomalies%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-975423%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379245%22%20target%3D%22_blank%22%3E%40John_Joyner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20notice%20my%20blog%20post%20that%20would%20look%20familiar%20%3A)%3C%2Fimg%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Sentinel%2FHow-to-use-Azure-Monitor-Workbooks-to-map-Sentinel-data%2Fba-p%2F971818%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Sentinel%2FHow-to-use-Azure-Monitor-Workbooks-to-map-Sentinel-data%2Fba-p%2F971818%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
John_Joyner
Occasional Contributor

We are envisioning managing Sentinel mainly from Incidents, rather than manually watching the Sentinel console in the Azure portal. We would like incidents auto-created for serious Potential malicious events (those of the "large orange dot" and higher, not the 'small orange dots' however). Also when there are significant spikes in any data source anomaly. How can we get visibility into these two Sentinel Overview page controls without watching the portal?
Thanks, John

5 Replies

@John_Joyner 

 

Just click on either and you will be taken to the Logs blade, then press "+ New Alert Rule".  Name the alert and set your Thresholds etc... then trigger a "send email" Playbook (or send to any system supported by Logic Apps) 

A Playbook looking like this 

clipboard_image_0.png

@Clive Watson Thank you Clive for the tip, and we had considered that method, however, the resulting queries don't achieve the goals.

  • In the case of Potential Malicious Events, the query is specific to the geographic spot the event occurred in. To be useful as a rule, the geography should be neutral. If you remove the line from the query "| where Latitude == __.__ and Longitude == ___.__" then you just get a big list of every event where "isnotempty(MaliciousIP)". That does not meet the desire where any large orange or any red hit that would appear on the map creates an incident where that geographic site is the only site in the alert. We were hoping for insight into the logic that determines there is a high number of hits from a specific geo and alert on that. (the same logic that is creating the map)
  • For Data Source Anomalies, the query is specific to a time period for that specific data type. If you remove the custom time from the query, for example "datetime(2019-10-25T22:27:47.031Z)" the query doesn't run. We were hoping for insight into the logic that determines which data type experiences the highest anomalies and alert on that. (the same logic that is deciding which two data type to display on the overview page).

Thanks,

John Joyner

Microsoft MVP CDM

Solution

@John_Joyner 

 

Q1 - How about (updated, to make it more Alert friendly)?

Go to Log Analytics and Run Query

union isfuzzy=true   
(W3CIISLog
| extend TrafficDirection = "InboundOrUnknown", Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude), 
(DnsEvents
| extend TrafficDirection = "InboundOrUnknown", Country= RemoteIPCountry, Latitude = RemoteIPLatitude, Longitude = RemoteIPLongitude),
(WireData
| extend TrafficDirection = iff(Direction != "Outbound","InboundOrUnknown", "Outbound"), Country=RemoteIPCountry, Latitude=RemoteIPLatitude, Longitude=RemoteIPLongitude),     
(WindowsFirewall
| extend TrafficDirection = iff(CommunicationDirection != "SEND","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude),     
(CommonSecurityLog
| extend TrafficDirection = iff(CommunicationDirection != "Outbound","InboundOrUnknown", "Outbound"), Country=MaliciousIPCountry, Latitude=MaliciousIPLatitude, Longitude=MaliciousIPLongitude, Confidence=ThreatDescription, Description=ThreatDescription),    
(VMConnection
| where Type == "VMConnection"
| extend TrafficDirection = iff(Direction != "outbound","InboundOrUnknown", "Outbound"), Country=RemoteCountry, Latitude=RemoteLatitude, Longitude=RemoteLongitude)
| where isnotempty(MaliciousIP) and isnotempty(Country) and isnotempty(Latitude) and isnotempty(Longitude)
| summarize AggregatedValue = count() by Country
//| where AggregatedValue  > 100
| sort by AggregatedValue  desc

 

You can have this as a Table or Chart?  Or filter out Countries with more than 100 events for example (see the commented out example)

 

clipboard_image_0.png

 

Q2: 

 

You can adapt the query e.g.

 

 

DnsEvents
| summarize Count=count() by Type, bin_at(TimeGenerated, 1h , startofday(ago(7d)) )
| order by TimeGenerated asc

 

 

or see across all Tables with

 

 

 

let daystoSearch = 7d; // Please enter how many days worth of data to look at?
union withsource = tt *
| where TimeGenerated > startofday(ago(daystoSearch)) and TimeGenerated < startofday(now())
| summarize Count=count() by Type, bin_at(TimeGenerated, 1h , startofday(ago(7d)) )
// ignore Perf table or other noisy tables 
| where Type !in ("Perf","NetworkMonitoring")
// ignore event counts under 10k
| where  Count > 10000
| order by TimeGenerated asc

 

 

Go to Log Analytics and Run Query

 

@Clive Watson you are amazing sir! We were able to craft alert rules for both geographic and data source anomalies based on your reply. This means so much to us, thank you.

John Joyner

Microsoft MVP Cloud & Datacenter Management 

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
flashing a white screen while open new tab
Deleted in Discussions on
14 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