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.