Sentinel data Connector Health Status -email notification

[cyberHardik]

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.

[#2]

@cyberHardik 

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!

[#3]

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.

[#4]

@cyberHardik 

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?

https://techcommunity.microsoft.com/t5/azure-sentinel/introducing-azure-sentinel-solutions/ba-p/2347...

[#5]

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

[#6]

@cyberHardik

To compare Syslog and CEF, you could join the past 2days with the previous 14days and compare them, this is an example

union 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 Type1

Maybe 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

[#7]

The MTC Community is great and helpful!
Thank you very much

[#8]

@CliveWatson,
Thank you for response and please allow me some time so that I can test and see whether its meet our client expectation or not. Moreover , i would like to know is there any way to populate connector name corresponding to data type?

waiting for your reply.

[#9]

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.

[#10]

Not currently but this is being looked at. For now you have the Solution name.

[#11]

Not currently, for now, you could use a IIF to create you own status column, much like this example

| extend status_= iff(last_log <=48,"Connected","Not Connected, or no data sent in time period")

[#12]

@CliveWatson 

Thankx alot for such a swift reply, I did tried to fetch 2 days logs and added solution name column but It is not getting populated against all data types. below is the sniff for better understanding :

is Solution name currently available for some data types only as I am inhabiting all data types ?

Moreover , extending new status column suffice my requirement.

[#13]

The Usage Table is designed for questions like this, I suspect you are using "union *" and it wont guarantee in the query that each type/row maps to a solution, hence the missing solution field.

Usage
| summarize make_set(DataType), dcount(DataType) by Solution

[#14]

You got me@Clive Watson
Absolutely bang on Genius!
Yups, I am using Union *. Thanx alot for your help , All sorted now except one thing as logs are pulled over a given time frame so if there is no logs in that time frame then Data Type will be not present in the projected table and all the hardwork will go in vain. what do you suggest in that case ?

[#15]

@CliveWatson
I am still waiting for your response

[#16]

You can Join the returned result with the Usage Table, the new last line would be something like. It would be helpful to share your query (DM me if necessary).

| join (Usage | distinct Solution, DataType) on $left.Type == $right.DataType