Nov 14 2018
10:00 PM
- last edited on
Apr 07 2022
05:32 PM
by
TechCommunityAP
Nov 14 2018
10:00 PM
- last edited on
Apr 07 2022
05:32 PM
by
TechCommunityAP
Hi All,
I am creating a backup report using Log Analytics. But unfortunately I am not able to parse two column for my report and need help.
The original column looks like
BackupItemUniqueId_s: eastus;6XXXXXXXXXXXX481;iaasvmcontainerv2;prd-grb-0279-test-rg;servername
ResourceId: /SUBSCRIPTIONS/B02896-94675-497R-B4CF-A7RTD6RDH7D/RESOURCEGROUPS/PRD-GRB-0279-TEST-RG/PROVIDERS/MICROSOFT.RECOVERYSERVICES/VAULTS/Vaultname
I want to parse these lines output with only server and vault name (which are in bold), How can i do this.
here is the query which I am using
AzureDiagnostics
| where TimeGenerated > ago(1d)
| where Category == "AzureBackupReport"
| where OperationName == "Job"
| project TimeGenerated, BackupItemUniqueId_s, ResourceId, ResourceGroup, Level
Thanks for the help :)
Nov 15 2018 10:03 AM
Hi,
The easiest way is to use the `parse` operator, as in this query:
AzureDiagnostics | where TimeGenerated > ago(12h) | where OperationName == "Job" | project TimeGenerated, BackupItemUniqueId_s, ResourceId, ResourceGroup, Level | parse ResourceId with * "/AUTOMATIONACCOUNTS/" accountName
in your case, you'd write something like
... | parse ResourceId with * "/VAULTS/" vaultName
and the results would include a column named vaultName with the proper value. The same logic applies to servername, only the pattern is a bit different.
More details on the `parse` operator are available here.
HTH,
Noa
Nov 16 2018 12:07 AM
Hi Noa,
Thanks for the swift revert , I was able to parse vault-name with the help of given suggestion however confused to parse to server name from BackupItemUniqueId_s. As we could see below this have ; everywhere as well as we do not have standard resource group naming convention.
Please help me to parse server name from below line of output.
BackupItemUniqueId_s: eastus;6XXXXXXXXXXXX481;iaasvmcontainerv2;prd-grb-0279-test-rg;servername
Nov 16 2018 07:05 AM
SolutionYou can also use split in that situation:
AzureDiagnostics | where TimeGenerated > ago(1d) | where Category == "AzureBackupReport" | where OperationName == "Job" | extend x = split(ResourceId, '/')[-1] | extend y = split(BackupItemUniqueId_s, ';')[-1] | project TimeGenerated, BackupItemUniqueId_s, ResourceId, ResourceGroup, Level, x, y
This will split the fields by character and return the last value of the split.
Nov 22 2018 12:30 AM
Hi All,
Thanks for the reply.
My final query is working totally awesome with all the required field. But now i want create a dashboard with server name, time taken in hour and transferred GB, I have all the information available in dashabord but server name is not available, not sure why I do not have this specific field in output.
My query is as follows : -
AzureDiagnostics
| where TimeGenerated > ago(1d)
| where Category == "AzureBackupReport" and OperationName == "Job"
| where todouble(DataTransferredInMB_s)>1
| extend Report_Running_Time_UTC= TimeGenerated
| extend Backup_Job_Start_Time = JobStartDateTime_s
| extend DataTransferedGB = todouble(DataTransferredInMB_s)/1024
| extend JobDurationHour = todouble(JobDurationInSecs_s)/3600
| extend Vault_Name = split(ResourceId, '/')[-1]
| extend Server_Name = split(BackupItemUniqueId_s, ';')[-1]
| project Report_Running_Time_UTC, Backup_Job_Start_Time, SubscriptionId, JobOperation_s, JobStatus_s, DataTransferedGB, JobDurationHour, ResourceGroup, Server_Name, Vault_Name, Level
| render timechart
And dashboard looks like below picture: -
About Dashboard : -
Horizontal (x) is JobDurationHour and Vertical (y) is DataTransferredGB I also tried to click on any scatter point still do not find server name there.
Nov 22 2018 04:00 AM
Dashboards does not display summarization on more than one column. You can use this method that I've used for alerts for the dashboards as well:
Nov 24 2018 10:24 AM
Hi Stanislav,
I tried to summarize it but no luck, I found hard to get familiar with summarize command yet. Could you please help to get this done.
I will be very thankful to you!
Nov 27 2018 11:44 PM
Hi Gourav,
Please have a look at my example and notice that I am first using extend to add a new Column that concatenates two values from other two columns into single string. Than I use that new column to summarize upon with. Also when using summarize you need to provide operator for sumamarization. You can see all such operators here:
https://docs.microsoft.com/en-us/azure/kusto/query/summarizeoperator
My example is with avg(). I do not know what is your exact case but you will need to fix those things. As you have provided just the end of your query I do not know if you have done the extend thing. You can also use extend and concatenate values from more than 2 columns as well.
Nov 16 2018 07:05 AM
SolutionYou can also use split in that situation:
AzureDiagnostics | where TimeGenerated > ago(1d) | where Category == "AzureBackupReport" | where OperationName == "Job" | extend x = split(ResourceId, '/')[-1] | extend y = split(BackupItemUniqueId_s, ';')[-1] | project TimeGenerated, BackupItemUniqueId_s, ResourceId, ResourceGroup, Level, x, y
This will split the fields by character and return the last value of the split.