Log Analytics ASR Report for portected VMs for any number of days

Question

Hi there,

I am trying to generate a report through Log Analytics for ASR protected VMs.

My Scenario:

• 70 VMs that are replicating in ASR A2A

My Requirements:

• Generate a report that shows the replicated VMs and their health status for any number of days (let's assume 5 days)
• I want the reported 70 VMs to show 1 VM per day so a total of 70 VMs per day.
• I also want that those 70 VMs are shown per day across 5 days (so a total of 350 records).

I have tried different queries and syntaxes but I am failing to get what I need.

If I try this:

AzureDiagnostics
| where replicationProviderName_s == "A2A"  
| where isnotempty(name_s) and isnotnull(name_s)  
| summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by name_s  
| project
VirtualMachine = name_s,
Vault = Resource,
ReplicationHealth = replicationHealth_s,
Status = protectionState_s,
RPO_in_seconds = rpoInSeconds_d,
TestFailoverStatus = failoverHealth_s,
AgentVersion = agentVersion_s,
ReplicationError = replicationHealthErrors_s,
SourceLocation = primaryFabricName_s,
TimeGenerated

I only get the VMs with the latest time generated as it's aggregated.

And

If I try the below:

AzureDiagnostics
| where replicationProviderName_s == "A2A"  
| where isnotempty(name_s) and isnotnull(name_s) 
| project
VirtualMachine = name_s,
Vault = Resource,
ReplicationHealth = replicationHealth_s,
Status = protectionState_s,
RPO_in_seconds = rpoInSeconds_d,
TestFailoverStatus = failoverHealth_s,
AgentVersion = agentVersion_s,
ReplicationError = replicationHealthErrors_s,
SourceLocation = primaryFabricName_s,
TimeGenerated

I get over 30,000 results.

I have tried limit, I have tried distinct, I have tried adding the time generation in the query itself, but have not been successful.

So, in summary, how could I limit the reported VMs to:

• Output 70 Vms per day
• Output 350 records for 5 days (70*5) - So each VM will show only 5 times for each day in the past 5 days.

Thanks.

@Stanislav Zhelyazkov - You've been very helpful in a previous post; thought maybe you could also assist here, if it's no bother?

Answer 1

Hi,
I believe you are looking at query like this:

AzureDiagnostics  
| where ResourceProvider == "MICROSOFT.RECOVERYSERVICES"  
    and replicationProviderName_s == "A2A"    
    and Category == "AzureSiteRecoveryReplicatedItems"  
    and TimeGenerated > ago(5d)  
| summarize arg_max(TimeGenerated, *) by name_s , endofday(TimeGenerated)  

A few things that are different. Improved the filtering on the records. Filtered the data in the query for the last 5 days. The query should return the last record of every day for each unique value in name_s. Times are in UTC.

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.