Device management - MEM/AAD/AD/SCCM




I'm looking for some advice to see if we can use our new Microsoft Sentinel to identify devices which have not been online for x number of days eg. 30 day.


We have a co-managed and hybrid setup so we have multiple data sources likeMEM, AAD, AD, SCCM.


Where possible I would like to cross reference to see if we have non-active devices or maybe devices which have been removed from the network but not done correctly means. For example we could have on-premise computers accounts enabled. Or the other way where the on-premise account has been deleted but the cloud side has not. 


Would Sentinel be the right solution for this? We could then use the playbooks to call LogicApps/Azure Automation (including hybrid runbook workers) to address the issues.





3 Replies



This would be a query that you can adapt and test, it assumes you have Heartbeat info and 90days (more of that below).  You would have to setup an Alert rule using this for the different Tables your services are in (or amend to Union the data sources - personally I'd have a rule for each to keep it clean and aid which playbooks run.).  It compares computers seen in the 90-30days period against ones in the past 30days, therefore any not seen are reported.   

//left table
 let olderDevices = Heartbeat
 // look back on older devices
 | where TimeGenerated between ( startofday(ago(90d)) .. endofday(ago(30d)) )
 | summarize count() by Computer, TimeGenerated;
 //right table
 let recentDevices = Heartbeat
 // lookup more recent ones 
 | where TimeGenerated between ( startofday(ago(29d)) .. now() )
 | summarize count() by Computer,  TimeGenerated;
 // exclude devices 
 | join kind=rightanti olderDevices on Computer
 | summarize arg_min(TimeGenerated,*) by Computer
 | order by TimeGenerated asc


The challenge, is that Analytics Rules only look back 14days, so you will have to adapt this KQL to maybe look back 14days, and 7days - this might increase your false positive rate as a machine might easily be offline legitimately for a week.   


There is a technique to increase the 14days using aggregation, but with some added cost  

Tiander did a great webcast here: – watch it all :smiling_face_with_smiling_eyes:, but “14days use case” starts at 42min.

hopefully this is a start....

@Clive_Watson Thank-you for the advice and I'll watch the video shortly. What would be the best way to get the data into Sentinel's log analytics space?





There is an example in the video - if I remember correctly