SOLVED

KQL Syntax question

%3CLINGO-SUB%20id%3D%22lingo-sub-1138906%22%20slang%3D%22en-US%22%3EKQL%20Syntax%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1138906%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHow%20do%20I%20rename%20the%20duration%20value%20from%20dependencies%20to%20seperate%20it%20from%20duration%20from%20requests.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EQuery%20is%20as%20follows%3A%3CBR%20%2F%3Elet%20Client%20%3D%20union%20requests%2C%20dependencies%3CBR%20%2F%3E%7C%20where%20cloud_RoleName%20contains%20'EUWPGTP018WAP04'%20or%20target%20contains%20'client'%3B%3CBR%20%2F%3EClient%3CBR%20%2F%3E%7C%20project%20operation_Name%2C%20operation_ParentId%2C%20operation_Id%2C%20duration%3CBR%20%2F%3E%7C%20join%20(Client%20%7C%20where%20operation_ParentId%20contains%20operation_Id)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20on%20operation_Id%20%3C%2FSPAN%3E%3CFONT%3E%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1138906%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-1141757%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Syntax%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1141757%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F538211%22%20target%3D%22_blank%22%3E%40Vincent20%3C%2FA%3E%2C%20I%20am%20not%20sure%20I%20understood%20your%20question%20nor%20the%20goal%20of%20your%20query.%20But%20if%20you%20want%20to%20rename%20a%20column%2C%20you%20have%20to%20use%20%3CSTRONG%3Eextend%2Bproject-away%3C%2FSTRONG%3E%20or%20simply%20%3CSTRONG%3Eproject-rename%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3Edependencies%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20dependencyDuration%20%3D%20duration%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject-away%3C%2FSPAN%3E%3CSPAN%3E%20duration%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3Eor%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3Edependencies%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject-rename%3C%2FSPAN%3E%3CSPAN%3E%20dependencyDuration%20%3D%20duration%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1141820%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Syntax%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1141820%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20this.%20So%20the%20scenario%20is%20as%20follows%3A%20I%20have%20two%20tables%20Dependencies%20and%20Requests.%20Each%20of%20these%20have%20a%20column%20called%20Duration.%20So%20the%20query%20I%20have%20above%20merges%20columns%20in%20table%201(Dependencies)%20and%20table%202(requests)%20and%20combines%20all%20common%20columns%20in%20both%20as%20one.%20The%20problem%20is%20that%20I%20want%20to%20show%20all%20columns%20whether%20it%20is%20common%20or%20not%2C%20then%20rename%20the%20common%20column%20called%20duration%20so%20that%20I%20can%20Identify%20duration%20from%20dependencies%20and%20duration%20from%20requests.%20I%20look%20forward%20to%20your%20kind%20response%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142276%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Syntax%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142276%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F538211%22%20target%3D%22_blank%22%3E%40Vincent20%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20this%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Eunion%20isfuzzy%3Dtrue%20%0A(Dependencies%20%0A%7C%20extend%20DurationA%20%3D%20Duration)%2C%20%0A(Requests%0A%7C%20extend%20DurationB%20%3D%20Duration)%0A%7C%20summarize%20%20by%20DurationA%2C%20DurationB%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExample%20using%20demo%20Tables%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAAyvNy8zPU8gsTiutqqq0LSkqTVXg5dJwLUvNKwEyahRSK0pS81IUXEqLEkuAKh0VbBXAkp4uINmCovys1OQShDQvl6YOyIDg1OTSosySSrBaLOY44TfHSRMkWlyam5tYlFmVqqCQnF%25252BaV6IBtwZoCaqIkyYXAHYV7%25252FfKAAAA%2Ftimespan%2FP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%20%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Eunion%20isfuzzy%3Dtrue%20%0A(Event%20%0A%7C%20extend%20DurationA%20%3D%20EventID%0A%7C%20project%20DurationA%0A)%2C%20%0A(SecurityEvent%0A%7C%20extend%20DurationB%20%3D%20EventID%0A%7C%20project%20DurationB)%0A%7C%20summarize%20%20count(DurationA)%2C%20count(DurationB)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3Ecount_DurationA%3C%2FTH%3E%0A%3CTH%3Ecount_DurationB%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E601533%3C%2FTD%3E%0A%3CTD%3E601533%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1142350%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Syntax%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1142350%22%20slang%3D%22en-US%22%3EThis%20is%20great.%20Exactly%20what%20I%20want%20to%20achieve.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

How do I rename the duration value from dependencies to seperate it from duration from requests.

 

Query is as follows:
let Client = union requests, dependencies
| where cloud_RoleName contains 'EUWPGTP018WAP04' or target contains 'client';
Client
| project operation_Name, operation_ParentId, operation_Id, duration
| join (Client | where operation_ParentId contains operation_Id)
    on operation_Id

4 Replies
Highlighted

@Vincent20, I am not sure I understood your question nor the goal of your query. But if you want to rename a column, you have to use extend+project-away or simply project-rename.

 

For example

 

dependencies
| extend dependencyDuration = duration
| project-away duration
 
or 
 
dependencies
| project-rename dependencyDuration = duration
Highlighted

Thank you for this. So the scenario is as follows: I have two tables Dependencies and Requests. Each of these have a column called Duration. So the query I have above merges columns in table 1(Dependencies) and table 2(requests) and combines all common columns in both as one. The problem is that I want to show all columns whether it is common or not, then rename the common column called duration so that I can Identify duration from dependencies and duration from requests. I look forward to your kind response@hspinto 

Highlighted
Solution

@Vincent20 

 

Like this?

union isfuzzy=true 
(Dependencies 
| extend DurationA = Duration), 
(Requests
| extend DurationB = Duration)
| summarize  by DurationA, DurationB

 

 

Example using demo Tables 

Go to Log Analytics and run query

union isfuzzy=true 
(Event 
| extend DurationA = EventID
| project DurationA
), 
(SecurityEvent
| extend DurationB = EventID
| project DurationB)
| summarize  count(DurationA), count(DurationB)
count_DurationA count_DurationB
601533 601533

 

Highlighted
This is great. Exactly what I want to achieve.