Forum Discussion

GouravIN's avatar
GouravIN
Brass Contributor
Nov 15, 2018
Solved

Parse ; value from output

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    BackupItemUniq...
azure monitor
Query Language
  • Stanislav_Zhelyazkov's avatar
    Stanislav_Zhelyazkov
    Nov 16, 2018

    You 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.

GouravIN's avatar
GouravIN
Brass Contributor
Nov 16, 2018

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

Stanislav_Zhelyazkov's avatar
Stanislav_Zhelyazkov
MVP
Nov 16, 2018

You 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.

  • GouravIN's avatar
    GouravIN
    Brass Contributor
    Nov 22, 2018

    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. 

     

     

     

      

Resources