Forum Discussion
Sentinel data Connector Health Status -email notification
Hey guys,
I have created a playbook for monitoring sentinel data connectors health and an email notification is setup if there is no logs received for any connector in last 48 hrs . It is fully functional and I able to fetch last event time and data type associated with connectors. Below snap show the data I am populating over the email in tabular format.
I am using query based playbook and it is worth mentioning the query here which I am using to populate the data through logic app.
union withsource=TableName1 *
| where TimeGenerated > ago(2d)
| project TimeGenerated, TableName1, DeviceVendor,ProviderName
| summarize last_log = datetime_diff("second", now(), max(TimeGenerated)),last_event_received = max(TimeGenerated) by TableName1, DeviceVendor,ProviderName
| project ['Table Name'] = TableName1, ['Latest Record Created'] = last_log, ['Time'] = last_event_received, DeviceVendor, ProviderName
But, when I am trying to populate list of all the datatypes/datasets . It is not getting populated as it is time frame dependent. So, I am unable to know which datatype is missing if there is no logs generated in that particular time frame. Moreover, if someone don't know how many datatypes have been integrated then its very difficult to know which data type is not receiving logs as multiple device logs can be configured under common security logs or syslog datatypes .
I am facing these two issues :
1. Please help me how I can populate all the datatype irrespective of time frame using KQL.
2. Also, I want to populate the data connector name associated with datatype but I was not lucky enough to create a KQL query for that as I don't know how connector name is mapped with data types.
Any help or suggestion to fix above issues will be appreciated.
cyberHardik Thanks for your question! This Tech Community Discussion space is intended for questions and discussions specifically around the Tech Community website itself. I've moved your question to the Azure Sentinel discussion space - please ask questions about Azure Sentinel there in the future.
- CliveWatson
Microsoft
cyberHardik
To compare Syslog and CEF, you could join the past 2days with the previous 14days and compare them, this is an exampleunion Syslog, CommonSecurityLog | where TimeGenerated between (startofday(ago(14d)) .. endofday(ago(3d))) | summarize dcount(DeviceVendor), make_set(DeviceVendor) by Type | join ( union Syslog, CommonSecurityLog | where TimeGenerated > ago(2d) | project TimeGenerated, Type, DeviceVendor | summarize Twodays=dcount(DeviceVendor), make_set(DeviceVendor) by Type ) on $left.Type == $right.Type | project-rename TwoWeeks = dcount_DeviceVendor | extend weHaveLess = iif(Twodays < TwoWeeks,'We have less Vendors than before','') | project-away Type1Maybe in your reporting (run a new query in the Playbook) to show, the Sources connected over 14days and which are outside of the SLA. The Usage table (whilst having less data) is very fast as its aggregated already. Again and example you can build on, I switched to hours and only sources over 12hrs with no data, there is an SLA column to show those over 48hrs
Usage | where TimeGenerated > startofday(ago(14d)) | summarize last_log = datetime_diff("hour", now(), max(TimeGenerated)),last_event_received = max(TimeGenerated) by TableName=DataType , Solution | extend slaUnder2Days = iff(last_log <=48,"OK","SLA not ok") | where last_log > 12 | order by last_log desc
- I forget to mention that status of datatype also need to be fetched. whether they are connected or not . So I would fetch status of the datatype in tabular form. Please guide me as I am new to information security and less knowledge about KQL although I am enriching my knowledge day by day.
Hello!
I have a request to explain why you put in this group this query!
Do you think this is a general discussion about all Microsoft products?
Please reply!
Hi Andrzejx,
I am newbie on tech community and I do understand it is not a general discussion. It should be on azure sentinel discussion platform. I request you to please guide me how I can delete this from here or move this post to azure sentinel discussion panel.I think there is no need to remove this topic!
Just in the right group, you'll get an answer faster!
Only moderator can move this post!
Do you think we should change the name here eg. "MTC Community Policy"
Very often there are posts about Microsoft products and I'm curious what causes it?
