Query per la tabella Heartbeat

Articolo
11/05/2024

Per informazioni sull'uso di queste query nella portale di Azure, vedere Esercitazione su Log Analytics. Per l'API REST, vedere Query.

Conteggio heartbeat

Conteggia tutti gli heartbeat dei computer dell'ultima ora.

// Count computers heartbeats in the last hour. 
// Normally, agents on VMs generate Heartbeat event every minute.
Heartbeat
| where TimeGenerated > ago(1h)
| summarize count() by Computer

Last heartbeat of each computer

Mostra l'ultimo heartbeat inviato da ogni computer.

// Last heartbeat of each computer 
// Show the last heartbeat sent by each computer. 
Heartbeat
| summarize arg_max(TimeGenerated, *) by Computer

Picchi di latenza di inserimento (end-to-end) - Tabella heartbeat

Verificare la presenza di picchi di latenza nell'inserimento di heartbeat nelle ultime 24 ore.

// Ingestion latency (end-to-end) spikes - Heartbeat table 
// Check for latency spikes in the ingestion of Heartbeats in the last 24 hour. 
// This query calculates ingestion duration every 10 minutes, and looks for spikes
let StartTime = ago(24h);
let EndTime = now();
let MinRSquare = 0.9; // Tune the sensitivity of the detection sensor. Higher numbers make the detector more sensitive
Heartbeat
| where TimeGenerated between (StartTime .. EndTime)
// calculate ingestion duration in seconds
| extend IngestionDurationSeconds = (ingestion_time()-TimeGenerated)/1s
// Create a time series
| make-series RatioSeries=avg(IngestionDurationSeconds) default=0 on TimeGenerated in range(StartTime , EndTime,10m)
// Apply a 2-line regression to the time series
| extend (RSquare2, SplitIdx, Variance2, RVariance2, LineFit2) = series_fit_2lines(RatioSeries)
// Find out if our 2-line is trending up or down
|extend (Slope, Interception, RSquare, Variance, RVariance, LineFit) = series_fit_line(LineFit2)
// Check whether the line fit reaches the threshold, and if the spike represents an increase (rather than a decrease)
| project PatternMatch = iff(RSquare2 > MinRSquare and Slope>0, "Spike detected", "No spike")

Picchi di latenza dell'agente - Tabella heartbeat

Verificare la presenza di picchi di latenza dell'agente nell'inserimento di heartbeat nelle ultime 24 ore.

// Agent latency spikes - Heartbeat table 
// Check for agent latency spikes in the ingestion of Heartbeats in the last 24 hour. 
// This query calculates ingestion duration every 10 minutes, and looks for spikes
let StartTime = ago(24h);
let EndTime = now();
let MinRSquare = 0.9; // Tune the sensitivity of the detection sensor. Higher numbers make the detector more sensitive
Heartbeat
| where TimeGenerated between (StartTime .. EndTime)
// calculate ingestion duration in seconds
| extend AgentLatencySeconds = (_TimeReceived-TimeGenerated)/1s
// Create a time series
| make-series RatioSeries=avg(AgentLatencySeconds) default=0 on TimeGenerated in range(StartTime , EndTime,10m)
// Apply a 2-line regression to the time series
| extend (RSquare2, SplitIdx, Variance2, RVariance2, LineFit2) = series_fit_2lines(RatioSeries)
// Find out if our 2-line is trending up or down
|extend (Slope, Interception, RSquare, Variance, RVariance, LineFit) = series_fit_line(LineFit2)
// Check whether the line fit reaches the threshold, and if the spike represents an increase (rather than a decrease)
| project PatternMatch = iff(RSquare2 > MinRSquare and Slope>0, "Spike detected", "No spike")

Heartbeat arrestati di recente - Tabella heartbeat

Elenca le risorse che hanno interrotto l'invio di heartbeat negli ultimi 15 minuti.

// Resources, which stopped sending heartbeats in last 15 minutes
Heartbeat
| summarize LastReported=now()-max(TimeGenerated) by ResourceGroup, Resource, ResourceType 
// Assuming that heartbeats are sent at least every minute we are looking at 1-15 minute interval
| where LastReported between(1m..15m)

Computers availability today

Grafico del numero di computer che inviano i log, ogni ora.

Heartbeat
| summarize dcount(ComputerIP) by bin(TimeGenerated, 1h)
| render timechart

Unavailable computers

Elencare tutti i computer noti che non hanno inviato un heartbeat nelle ultime 5 ore.

Heartbeat
| summarize LastHeartbeat=max(TimeGenerated) by Computer
| where LastHeartbeat < ago(5h)

Availability rate

Calcolare la frequenza di disponibilità di ogni computer connesso.

Heartbeat
// bin_at is used to set the time grain to 1 hour, starting exactly 24 hours ago
| summarize heartbeatPerHour = count() by bin_at(TimeGenerated, 1h, ago(24h)), Computer
| extend availablePerHour = iff(heartbeatPerHour > 0, true, false)
| summarize totalAvailableHours = countif(availablePerHour == true) by Computer
| extend availabilityRate = totalAvailableHours*100.0/24

Mancata creazione di report di macchine virtuali

Macchine virtuali che non hanno segnalato un heartbeat negli ultimi 5 minuti.

// To create an alert for this query, click '+ New alert rule'
Heartbeat 
| where TimeGenerated > ago(24h)
| summarize LastCall = max(TimeGenerated) by Computer, _ResourceId
| where LastCall < ago(5m)

Elenco dei computer

Elenco di computer con Gestione aggiornamenti di Azure distribuito.

Heartbeat
| where TimeGenerated>ago(12h) and OSType=="Linux" and notempty(Computer)
| summarize arg_max(TimeGenerated, Solutions, Computer, ResourceId, ComputerEnvironment, VMUUID) by SourceComputerId
| where Solutions has "updates"
| extend vmuuId=VMUUID, azureResourceId=ResourceId, osType=1, environment=iff(ComputerEnvironment=~"Azure", 1, 2), scopedToUpdatesSolution=true, lastUpdateAgentSeenTime=""
| join kind=leftouter
(
   Update
    | where TimeGenerated>ago(5h) and OSType=="Linux" and SourceComputerId in ((Heartbeat
    | where TimeGenerated>ago(12h) and OSType=="Linux" and notempty(Computer)
    | summarize arg_max(TimeGenerated, Solutions) by SourceComputerId
    | where Solutions has "updates"
    | distinct SourceComputerId))
    | summarize hint.strategy=partitioned arg_max(TimeGenerated, UpdateState, Classification, Product, Computer, ComputerEnvironment) by SourceComputerId, Product, ProductArch
    | summarize Computer=any(Computer), ComputerEnvironment=any(ComputerEnvironment), missingCriticalUpdatesCount=countif(Classification has "Critical" and UpdateState=~"Needed"), missingSecurityUpdatesCount=countif(Classification has "Security" and UpdateState=~"Needed"), missingOtherUpdatesCount=countif(Classification !has "Critical" and Classification !has "Security" and UpdateState=~"Needed"), lastAssessedTime=max(TimeGenerated), lastUpdateAgentSeenTime="" by SourceComputerId
    | extend compliance=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0, 2, 1)
    | extend ComplianceOrder=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0 or missingOtherUpdatesCount > 0, 1, 3)
)
on SourceComputerId
| project id=SourceComputerId, displayName=Computer, sourceComputerId=SourceComputerId, scopedToUpdatesSolution=true, missingCriticalUpdatesCount=coalesce(missingCriticalUpdatesCount, -1), missingSecurityUpdatesCount=coalesce(missingSecurityUpdatesCount, -1), missingOtherUpdatesCount=coalesce(missingOtherUpdatesCount, -1), compliance=coalesce(compliance, 4), lastAssessedTime, lastUpdateAgentSeenTime, osType=1, environment=iff(ComputerEnvironment=~"Azure", 1, 2), ComplianceOrder=coalesce(ComplianceOrder, 2)
| union(Heartbeat
| where TimeGenerated>ago(12h) and OSType=~"Windows" and notempty(Computer)
| summarize arg_max(TimeGenerated, Solutions, Computer, ResourceId, ComputerEnvironment, VMUUID) by SourceComputerId
| where Solutions has "updates"
| extend vmuuId=VMUUID, azureResourceId=ResourceId, osType=2, environment=iff(ComputerEnvironment=~"Azure", 1, 2), scopedToUpdatesSolution=true, lastUpdateAgentSeenTime=""
| join kind=leftouter
(
    Update
    | where TimeGenerated>ago(14h) and OSType!="Linux" and SourceComputerId in ((Heartbeat
    | where TimeGenerated>ago(12h) and OSType=~"Windows" and notempty(Computer)
    | summarize arg_max(TimeGenerated, Solutions) by SourceComputerId
    | where Solutions has "updates"
    | distinct SourceComputerId))
    | summarize hint.strategy=partitioned arg_max(TimeGenerated, UpdateState, Classification, Title, Optional, Approved, Computer, ComputerEnvironment) by Computer, SourceComputerId, UpdateID
    | summarize Computer=any(Computer), ComputerEnvironment=any(ComputerEnvironment), missingCriticalUpdatesCount=countif(Classification has "Critical" and UpdateState=~"Needed" and Approved!=false), missingSecurityUpdatesCount=countif(Classification has "Security" and UpdateState=~"Needed" and Approved!=false), missingOtherUpdatesCount=countif(Classification !has "Critical" and Classification !has "Security" and UpdateState=~"Needed" and Optional==false and Approved!=false), lastAssessedTime=max(TimeGenerated), lastUpdateAgentSeenTime="" by SourceComputerId
    | extend compliance=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0, 2, 1)
    | extend ComplianceOrder=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0 or missingOtherUpdatesCount > 0, 1, 3)
)
on SourceComputerId
| project id=SourceComputerId, displayName=Computer, sourceComputerId=SourceComputerId, scopedToUpdatesSolution=true, missingCriticalUpdatesCount=coalesce(missingCriticalUpdatesCount, -1), missingSecurityUpdatesCount=coalesce(missingSecurityUpdatesCount, -1), missingOtherUpdatesCount=coalesce(missingOtherUpdatesCount, -1), compliance=coalesce(compliance, 4), lastAssessedTime, lastUpdateAgentSeenTime, osType=2, environment=iff(ComputerEnvironment=~"Azure", 1, 2), ComplianceOrder=coalesce(ComplianceOrder, 2))
| order by ComplianceOrder asc, missingCriticalUpdatesCount desc, missingSecurityUpdatesCount desc, missingOtherUpdatesCount desc, displayName asc
| project-away ComplianceOrder

Trova in heartbeat

Trovare in Heartbeat per cercare un valore specifico nella tabella Heartbeat./nNote che questa query richiede l'aggiornamento del <parametro SeachValue> per produrre risultati

// This query requires a parameter to run. Enter value in SearchValue to find in table.
let SearchValue =  "<SearchValue>";//Please update term you would like to find in the table.
Heartbeat
| where * contains tostring(SearchValue)
| take 1000

Condividi tramite