SOLVED

Parse ; value from output

%3CLINGO-SUB%20id%3D%22lingo-sub-287009%22%20slang%3D%22en-US%22%3EParse%20%3B%20value%20from%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287009%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20creating%20a%20backup%20report%20using%20Log%20Analytics.%20But%20unfortunately%20I%20am%20not%20able%20to%20parse%26nbsp%3Btwo%20column%20for%20my%20report%20and%20need%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20original%20column%26nbsp%3B%20looks%20like%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBackupItemUniqueId_s%3A%20eastus%3B6XXXXXXXXXXXX481%3Biaasvmcontainerv2%3Bprd-grb-0279-test-rg%3B%3CSTRONG%3Eservername%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EResourceId%3A%20%2FSUBSCRIPTIONS%2FB02896-94675-497R-B4CF-A7RTD6RDH7D%2FRESOURCEGROUPS%2FPRD-GRB-0279-TEST-RG%2FPROVIDERS%2FMICROSOFT.RECOVERYSERVICES%2FVAULTS%2F%3CSTRONG%3EVaultname%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20parse%20these%20lines%20output%20with%20only%20server%20and%20vault%20name%20(which%20are%20in%20bold)%2C%20How%20can%20i%20do%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20is%20the%20query%20which%20I%20am%20using%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAzureDiagnostics%3C%2FP%3E%3CP%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%3CBR%20%2F%3E%7C%20where%20Category%20%3D%3D%20%22AzureBackupReport%22%3C%2FP%3E%3CP%3E%7C%20where%20OperationName%20%3D%3D%20%22Job%22%3C%2FP%3E%3CP%3E%7C%20project%20TimeGenerated%2C%26nbsp%3B%20BackupItemUniqueId_s%2C%20ResourceId%2C%20ResourceGroup%2C%20Level%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-287009%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-292065%22%20slang%3D%22en-US%22%3ERe%3A%20Parse%20%3B%20value%20from%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-292065%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gourav%2C%3C%2FP%3E%0A%3CP%3EPlease%20have%20a%20look%20at%20my%20example%20and%20notice%20that%20I%20am%20first%20using%20extend%20to%20add%20a%20new%20Column%20that%20concatenates%20two%20values%20from%20other%20two%20columns%20into%20single%20string.%20Than%20I%20use%20that%20new%20column%20to%20summarize%20upon%20with.%20Also%20when%20using%20summarize%20you%20need%20to%20provide%20operator%20for%20sumamarization.%20You%20can%20see%20all%20such%20operators%20here%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fsummarizeoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fsummarizeoperator%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EMy%20example%20is%20with%20avg().%20I%20do%20not%20know%20what%20is%20your%20exact%20case%20but%20you%20will%20need%20to%20fix%20those%20things.%20As%20you%20have%20provided%20just%20the%20end%20of%20your%20query%20I%20do%20not%20know%20if%20you%20have%20done%20the%20extend%20thing.%20You%20can%20also%20use%20extend%20and%20concatenate%20values%20from%20more%20than%202%20columns%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290817%22%20slang%3D%22en-US%22%3ERe%3A%20Parse%20%3B%20value%20from%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290817%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%3EStanislav%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EI%20tried%20to%20summarize%20it%20but%20no%20luck%2C%20I%20found%20hard%20to%20get%20familiar%20with%20summarize%20command%20yet.%20Could%20you%20please%20help%20to%20get%20this%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20be%20very%20thankful%20to%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3EValue%20%3D%20(DataTransferedGB%2C%20JobDurationHour)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3EServer_Name%2C%20bin(TimeGenerated%2C%20%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3Em)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290273%22%20slang%3D%22en-US%22%3ERe%3A%20Parse%20%3B%20value%20from%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290273%22%20slang%3D%22en-US%22%3E%3CP%3EDashboards%20does%20not%20display%20summarization%20on%20more%20than%20one%20column.%20You%20can%20use%20this%20method%20that%20I've%20used%20for%20alerts%20for%20the%20dashboards%20as%20well%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fcloudadministrator.net%2F2018%2F06%2F08%2Faggregate-on-more-than-one-column-for-azure-log-search-alerts%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcloudadministrator.net%2F2018%2F06%2F08%2Faggregate-on-more-than-one-column-for-azure-log-search-alerts%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290212%22%20slang%3D%22en-US%22%3ERe%3A%20Parse%20%3B%20value%20from%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290212%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20final%20query%20is%20working%20totally%20awesome%20with%20all%20the%20required%20field.%20But%20now%20i%20want%20create%20a%20dashboard%20with%20server%20name%2C%20time%20taken%20in%20hour%20and%20transferred%20GB%2C%20I%20have%20all%20the%20information%20available%20in%20dashabord%20but%20server%20name%20is%20not%20available%2C%20not%20sure%20why%20I%20do%20not%20have%20this%20specific%20field%20in%20output.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20query%20is%20as%20follows%20%3A%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EAzureDiagnostics%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%3CBR%20%2F%3E%7C%20where%20Category%20%3D%3D%20%22AzureBackupReport%22%20and%20OperationName%20%3D%3D%20%22Job%22%3CBR%20%2F%3E%7C%20where%20todouble(DataTransferredInMB_s)%26gt%3B1%3CBR%20%2F%3E%7C%20extend%20Report_Running_Time_UTC%3D%20TimeGenerated%3CBR%20%2F%3E%7C%20extend%20Backup_Job_Start_Time%20%3D%20JobStartDateTime_s%3CBR%20%2F%3E%7C%20extend%20DataTransferedGB%20%3D%20todouble(DataTransferredInMB_s)%2F1024%3CBR%20%2F%3E%7C%20extend%20JobDurationHour%20%3D%20todouble(JobDurationInSecs_s)%2F3600%3CBR%20%2F%3E%7C%20extend%20Vault_Name%20%3D%20split(ResourceId%2C%20'%2F')%5B-1%5D%3CBR%20%2F%3E%7C%20extend%20Server_Name%20%3D%20split(BackupItemUniqueId_s%2C%20'%3B')%5B-1%5D%3CBR%20%2F%3E%7C%20project%20Report_Running_Time_UTC%2C%20Backup_Job_Start_Time%2C%20SubscriptionId%2C%20JobOperation_s%2C%20JobStatus_s%2C%20DataTransferedGB%2C%20JobDurationHour%2C%20ResourceGroup%2C%20Server_Name%2C%20Vault_Name%2C%20Level%3CBR%20%2F%3E%7C%20render%20timechart%3C%2FPRE%3E%3CP%3EAnd%20dashboard%20looks%20like%20below%20picture%3A%20-%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAbout%20Dashboard%20%3A%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHorizontal%20(x)%20is%20JobDurationHour%20and%20Vertical%20(y)%20is%20DataTransferredGB%20I%20also%20tried%20to%26nbsp%3Bclick%20on%20any%20scatter%20point%20still%20do%20not%26nbsp%3Bfind%26nbsp%3Bserver%20name%20there.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20828px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60525i5E5FFA55402D21F5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22backup_dashboard.JPG%22%20title%3D%22backup_dashboard.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287883%22%20slang%3D%22en-US%22%3ERe%3A%20Parse%20%3B%20value%20from%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287883%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20also%20use%20split%20in%20that%20situation%3A%3C%2FP%3E%0A%3CPRE%3EAzureDiagnostics%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1d)%0A%7C%20where%20Category%20%3D%3D%20%22AzureBackupReport%22%0A%7C%20where%20OperationName%20%3D%3D%20%22Job%22%0A%7C%20extend%20x%20%3D%20split(ResourceId%2C%20'%2F')%5B-1%5D%0A%7C%20extend%20y%20%3D%20split(BackupItemUniqueId_s%2C%20'%3B')%5B-1%5D%0A%7C%20project%20TimeGenerated%2C%20%20BackupItemUniqueId_s%2C%20ResourceId%2C%20ResourceGroup%2C%20Level%2C%20x%2C%20y%3C%2FPRE%3E%0A%3CP%3EThis%20will%20split%20the%20fields%20by%20character%20and%20return%20the%20last%20value%20of%20the%20split.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287556%22%20slang%3D%22en-US%22%3ERe%3A%20Parse%20%3B%20value%20from%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287556%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Noa%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20swift%20revert%20%2C%20I%20was%20able%20to%20parse%20vault-name%20with%20the%20help%20of%20given%20suggestion%20however%20confused%20to%20parse%20to%20server%20name%20from%26nbsp%3B%3CSPAN%3EBackupItemUniqueId_s.%20As%20we%20could%20see%20below%20this%20have%20%3B%20everywhere%20as%20well%20as%20we%20do%20not%20have%20standard%20resource%20group%20naming%20convention.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help%20me%20to%20parse%20server%20name%20from%20below%20line%20of%20output.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EBackupItemUniqueId_s%3A%20eastus%3B6XXXXXXXXXXXX481%3Biaasvmcontainerv2%3Bprd-grb-0279-test-rg%3B%3C%2FSPAN%3E%3CSTRONG%3Eservername%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287311%22%20slang%3D%22en-US%22%3ERe%3A%20Parse%20%3B%20value%20from%20output%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287311%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EThe%20easiest%20way%20is%20to%20use%20the%20%60parse%60%20operator%2C%20as%20in%20%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA1WOTQuCQBRF90L%252F4TGrCkFqb2AGYpQDpeuYxodO5cw0HwnRjy%252FbWLt7uYfDTZ7e4EawRirrBLeT4AV9iwahFB1mKNEwhzWsgDVquli2MxgRqodVKFmwDiGOgWzVmQy7NuqC3P1LQoA141evc4ddJcXdY16fbAgHtMob%252FmljzozyOoQdPvD2NTJj8YeEXrgW5kCipCrpPilzWiRpSquiPEYEGOfKSzccC971Mfmc5AAAAA%253D%253D%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ethis%20query%3C%2FA%3E%3A%3C%2FP%3E%0A%3CPRE%3EAzureDiagnostics%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(12h)%20%0A%7C%20where%20OperationName%20%3D%3D%20%22Job%22%0A%7C%20project%20TimeGenerated%2C%20%20BackupItemUniqueId_s%2C%20ResourceId%2C%20ResourceGroup%2C%20Level%0A%7C%20parse%20ResourceId%20with%20*%20%22%2FAUTOMATIONACCOUNTS%2F%22%20accountName%3C%2FPRE%3E%0A%3CP%3Ein%20your%20case%2C%20you'd%20write%20something%20like%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E...%20%7C%20parse%20ResourceId%20with%20*%20%22%2FVAULTS%2F%22%20vaultName%3C%2FPRE%3E%0A%3CP%3Eand%20the%20results%20would%20include%20a%20column%20named%20vaultName%20with%20the%20proper%20value.%20The%20same%20logic%20applies%20to%20servername%2C%20only%20the%20pattern%20is%20a%20bit%20different.%3C%2FP%3E%0A%3CP%3EMore%20details%20on%20the%20%60parse%60%20operator%20are%20available%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fparseoperator%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3ENoa%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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 :)

 

7 Replies

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

 

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

Best Response confirmed by Stanislav Zhelyazkov (MVP)
Solution

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.

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. 

backup_dashboard.JPG

 

 

 

  

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:

https://cloudadministrator.net/2018/06/08/aggregate-on-more-than-one-column-for-azure-log-search-ale...

 

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!

 

| summarize Value = (DataTransferedGB, JobDurationHour) by Server_Name, bin(TimeGenerated, 5m)

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.