Hi @Daniel Lucas ,
Yes you can do this, however it's hard to come up with an example query without knowing your naming convention. Here is an example I've made up based on some fake naming convention using the extract() function
let Threshold = 0.5;
let startTime = ago(10m);
let endTime = now();
// Count of Unique Computer Names
let ComputerCount = toscalar( Heartbeat
| where TimeGenerated between (startTime .. endTime)
| distinct Computer
| count);
// Expected Number of heartbeats in the time range
let heartbeatPerAgent = array_length(range(startTime, endTime, 1m)) -1;
// Number of Agents per computer. There can be more than one with the AMA and MMA
let agentsPerComputer = Heartbeat
| where TimeGenerated between (startTime .. endTime)
| distinct Computer, SourceComputerId
| summarize NumberOfAgents = count() by Computer;
// Gather Heartbeats
Heartbeat
| where TimeGenerated between (startTime .. endTime)
| summarize Count = count() by Computer
| join kind=innerunique (
// Join the number of agents to the summarized heartbeat data
agentsPerComputer
) on Computer
| extend CountofHeartbeats = Count / NumberOfAgents
| extend ExpectedHeartbeats = heartbeatPerAgent
| order by Computer
//Used to simulate Computer naming convention based on my heartbeat data
| extend RenamedComputer = case ( row_number() < (ComputerCount /2),
strcat("SiteA", strrep("0", (4-strlen(tostring(row_number())))), row_number()),
strcat("SiteB", strrep("0", (4-strlen(tostring(row_number())))), row_number())
)
| project-away Computer, Computer1, NumberOfAgents
// Using Extract to identify the Site Code
| extend Site = extract(@"(Site)(\w)(\d+)",2, RenamedComputer)
| summarize TotalHeartbeats = sum(CountofHeartbeats), ExpectedHeartbeats = sum(ExpectedHeartbeats) by Site
| where TotalHeartbeats < (ExpectedHeartbeats * Threshold)