SOLVED

Need help with Azure log queries

%3CLINGO-SUB%20id%3D%22lingo-sub-1739412%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20Azure%20log%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1739412%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20someone%20share%20me%20an%20example%20of%20how%20i%20can%20write%20a%20single%20azure%20log%20query%20for%20the%20disk%20space%20for%20computers%20with%20different%20threshold%20values.%3C%2FP%3E%3CP%3EBelow%20is%20the%20way%20i%20am%20writing%20my%20query%20for%20handling%20the%20computers%20with%20different%20threshold%20values.%20I%20want%20to%20know%20if%20there%20is%20any%20other%20better%20way%20of%20doing%20the%20same%20in%20a%20single%20query%3C%2FP%3E%3CP%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%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3EPerf%0A%7C%20where%20ObjectName%20%3D%3D%20%22LogicalDisk%22%20and%20CounterName%20%3D%3D%20%22%25%20Free%20Space%22%0A%7C%20where%20strlen(InstanceName)%20%3D%3D2%20and%20InstanceName%20contains%20%22%3A%22%0A%7C%20where%20Computer%20!in~%20(%22DUFFVEEAMREPO01%22%2C%22TORFILE01%22)%0A%7C%20extend%20ComputerDrive%3D%20strcat(Computer%2C%20'%20-%20'%2C%20InstanceName)%0A%7C%20summarize%20Free_Space%20%3D%20min(CounterValue)%20by%20ComputerDrive%0A%7C%20sort%20by%20Free_Space%20asc%0A%7C%20where%20Free_Space%26lt%3B%2010)%0A%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%0A%7C%20union%20kind%3Douter%20(Perf%0A%7C%20where%20ObjectName%20%3D%3D%20%22LogicalDisk%22%20and%20CounterName%20%3D%3D%20%22%25%20Free%20Space%22%0A%7C%20where%20Computer%20contains%20%22DUFFVEEAMREPO01%22%20and%20InstanceName%20%3D%3D%20%22K%3A%22%0A%7C%20where%20strlen(InstanceName)%20%3D%3D2%20and%20InstanceName%20contains%20%22%3A%22%0A%7C%20extend%20drive%20%3D%20strcat(Computer%2C%20'%20-%20'%2C%20InstanceName)%0A%7C%20summarize%20Free_Space%20%3D%20min(CounterValue)%20by%20ComputerDrive%0A%7C%20sort%20by%20Free_Space%20asc%0A%7C%20where%20Free_Space%26lt%3B%201)%0A%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%0A%7C%20union%20kind%3Douter%20(Perf%0A%7C%20where%20ObjectName%20%3D%3D%20%22LogicalDisk%22%20and%20CounterName%20%3D%3D%20%22%25%20Free%20Space%22%0A%7C%20where%20Computer%20contains%20%22DUFFVEEAMREPO01%22%20and%20InstanceName%20%3D%3D%20%22I%3A%22%0A%7C%20where%20strlen(InstanceName)%20%3D%3D2%20and%20InstanceName%20contains%20%22%3A%22%0A%7C%20extend%20drive%20%3D%20strcat(Computer%2C%20'%20-%20'%2C%20InstanceName)%0A%7C%20summarize%20Free_Space%20%3D%20min(CounterValue)%20by%20ComputerDrive%0A%7C%20sort%20by%20Free_Space%20asc%0A%7C%20where%20Free_Space%26lt%3B%202.5)%0A%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%0A%7C%20union%20kind%3Douter%20(Perf%0A%7C%20where%20ObjectName%20%3D%3D%20%22LogicalDisk%22%20and%20CounterName%20%3D%3D%20%22%25%20Free%20Space%22%0A%7C%20where%20Computer%20contains%20%22TORFILE01%22%20and%20InstanceName%20%3D%3D%20%22F%3A%22%0A%7C%20where%20strlen(InstanceName)%20%3D%3D2%20and%20InstanceName%20contains%20%22%3A%22%0A%7C%20extend%20drive%20%3D%20strcat(Computer%2C%20'%20-%20'%2C%20InstanceName)%0A%7C%20summarize%20Free_Space%20%3D%20min(CounterValue)%20by%20ComputerDrive%0A%7C%20sort%20by%20Free_Space%20asc%0A%7C%20where%20Free_Space%26lt%3B%202.5)%0A%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%0A%7C%20union%20kind%3Douter%20(Perf%0A%7C%20where%20ObjectName%20%3D%3D%20%22LogicalDisk%22%20and%20CounterName%20%3D%3D%20%22%25%20Free%20Space%22%0A%7C%20where%20Computer%20contains%20%22TORFILE01%22%20and%20InstanceName%20%3D%3D%20%22K%3A%22%0A%7C%20where%20strlen(InstanceName)%20%3D%3D2%20and%20InstanceName%20contains%20%22%3A%22%0A%7C%20extend%20drive%20%3D%20strcat(Computer%2C%20'%20-%20'%2C%20InstanceName)%0A%7C%20summarize%20Free_Space%20%3D%20min(CounterValue)%20by%20ComputerDrive%0A%7C%20sort%20by%20Free_Space%20asc%0A%7C%20where%20Free_Space%26lt%3B%205)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740733%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20Azure%20log%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740733%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54106%22%20target%3D%22_blank%22%3E%40Swapna%20Nethi%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EHi%20Swapna%2C%20could%20you%20please%20tell%20us%20what%20is%20your%20goal%3F%20Is%20the%20query%20you%20provided%20currently%20working%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1740902%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20Azure%20log%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1740902%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F818330%22%20target%3D%22_blank%22%3E%40jpmartinez%3C%2FA%3E%26nbsp%3B%2C%20There%20is%20no%20issue%20with%20query%20above%20but%20my%20goal%20is%20accomplish%20the%20job%20i%20am%20doing%20using%20above%20query%20in%20a%20more%20optimized%20way%20to%20avoid%20the%20repetitive%20portions%20of%20the%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20Goal%20is%20to%20write%20a%20query%20to%20check%20the%20disk%20space%20of%20windows%20computers%20connected%20to%20multiple%20log%20analytics%20workspaces.%20The%20important%20point%20is%20the%20threshold%20%2Ffree%20space%20to%20be%20checked%20is%20different%20for%20few%20set%20of%20computers%20whereas%20it%20is%20usually%2010%25%20for%20most%20of%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%20I%20managed%20to%20write%20the%20query%20as%20in%20my%20code%20snippet%20but%20i%20myself%20felt%20there%20is%20lot%20of%20repetitive%20code%20%2Fcommands%20i%20am%20using.%20I%20want%20to%20know%20if%20there%20is%20any%20other%20way%20like%20using%20OR%20or%20switch%20commands%20this%20can%20be%20achieved%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1743536%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20Azure%20log%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1743536%22%20slang%3D%22en-US%22%3Edon't%20answer%20if%20you%20don't%20know.%20It's%20a%20simple%20problem.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20just%20don't%20use%20Azure.%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20if%20I%20wrote%20it%20and%20was%20as%20overpaid%20as%20an%20Azure%20engineer.%20This%20boilerplate%20would%20have%20been%20pointless%202%20months%20back%2C%20yes%20docs%20are%20related%20to%20my%20solution.%20Though%20I%20picked%20it%20from%20a%20Github%20user.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1747528%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20Azure%20log%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54106%22%20target%3D%22_blank%22%3E%40Swapna%20Nethi%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20query%20is%20aligned%20to%20KQL%20best%20practices%2C%20please%20note%20that%20the%20size%20of%20your%20query%20is%20due%20to%20the%20complexity.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fbest-practices%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fbest-practices%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%2C%20I%20think%20there%20are%20two%20good%20options%20available.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Use%20materialize()%20function%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fmaterializefunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fmaterializefunction%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2.%20Create%20your%20own%20functions%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flog-query%2Ffunctions%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flog-query%2Ffunctions%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBoth%20have%20similar%20goals%20but%20I%20can%20personally%20recommend%20the%20log%20query%20functions%20(option%202)%20since%20I%20have%20used%20it.%20With%20functions%2C%20you%20can%20keep%20your%20queries%20behind%20the%20scenes%20so%20when%20you%20are%20typing%20it%2C%20you%20can%20reduce%20complex%20queries%20to%20very%20few%20lines.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBesides%20that%2C%20since%20you%20are%20using%20various%20unions%20there%2C%20I%20think%20your%20query%20is%20good%20in%20terms%20of%20optimization%20or%20logic.%20You%20might%20want%20to%20use%20functions%20to%20reduce%20the%20query%20size%20though.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749439%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20Azure%20log%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749439%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F818330%22%20target%3D%22_blank%22%3E%40jpmartinez%3C%2FA%3E%26nbsp%3B%2C%20can%20you%20please%20give%20me%20some%20working%20examples%20on%20the%20creation%20and%20usage%20of%20functions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Can someone share me an example of how i can write a single azure log query for the disk space for computers with different threshold values.

Below is the way i am writing my query for handling the computers with different threshold values. I want to know if there is any other better way of doing the same in a single query

---------------------------------------------------------------------------------------------

 

 

 

Perf
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space"
| where strlen(InstanceName) ==2 and InstanceName contains ":"
| where Computer !in~ ("DUFFVEEAMREPO01","TORFILE01")
| extend ComputerDrive= strcat(Computer, ' - ', InstanceName)
| summarize Free_Space = min(CounterValue) by ComputerDrive
| sort by Free_Space asc
| where Free_Space< 10)
//////////////////////////////////////////////
| union kind=outer (Perf
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space"
| where Computer contains "DUFFVEEAMREPO01" and InstanceName == "K:"
| where strlen(InstanceName) ==2 and InstanceName contains ":"
| extend drive = strcat(Computer, ' - ', InstanceName)
| summarize Free_Space = min(CounterValue) by ComputerDrive
| sort by Free_Space asc
| where Free_Space< 1)
//////////////////////////////////////////////
| union kind=outer (Perf
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space"
| where Computer contains "DUFFVEEAMREPO01" and InstanceName == "I:"
| where strlen(InstanceName) ==2 and InstanceName contains ":"
| extend drive = strcat(Computer, ' - ', InstanceName)
| summarize Free_Space = min(CounterValue) by ComputerDrive
| sort by Free_Space asc
| where Free_Space< 2.5)
//////////////////////////////////////////////
| union kind=outer (Perf
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space"
| where Computer contains "TORFILE01" and InstanceName == "F:"
| where strlen(InstanceName) ==2 and InstanceName contains ":"
| extend drive = strcat(Computer, ' - ', InstanceName)
| summarize Free_Space = min(CounterValue) by ComputerDrive
| sort by Free_Space asc
| where Free_Space< 2.5)
//////////////////////////////////////////////
| union kind=outer (Perf
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space"
| where Computer contains "TORFILE01" and InstanceName == "K:"
| where strlen(InstanceName) ==2 and InstanceName contains ":"
| extend drive = strcat(Computer, ' - ', InstanceName)
| summarize Free_Space = min(CounterValue) by ComputerDrive
| sort by Free_Space asc
| where Free_Space< 5)

 

 

 

4 Replies
Highlighted

@Swapna Nethi 
Hi Swapna, could you please tell us what is your goal? Is the query you provided currently working? Is it facing performance issues?

Highlighted

@jpmartinez , There is no issue with query above but my goal is accomplish the job i am doing using above query in a more optimized way to avoid the repetitive portions of the code.

 

My Goal is to write a query to check the disk space of windows computers connected to multiple log analytics workspaces. The important point is the threshold /free space to be checked is different for few set of computers whereas it is usually 10% for most of them.

  I managed to write the query as in my code snippet but i myself felt there is lot of repetitive code /commands i am using. I want to know if there is any other way like using OR or switch commands this can be achieved .

 

 

Highlighted
Best Response confirmed by jpmartinez (Microsoft)
Solution

@Swapna Nethi 

The query is aligned to KQL best practices, please note that the size of your query is due to the complexity. 

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/best-practices

 

But, I think there are two good options available. 

1. Use materialize() function:

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/materializefunction

 

2. Create your own functions:

https://docs.microsoft.com/en-us/azure/azure-monitor/log-query/functions

 

Both have similar goals but I can personally recommend the log query functions (option 2) since I have used it. With functions, you can keep your queries behind the scenes so when you are typing it, you can reduce complex queries to very few lines. 

 

Besides that, since you are using various unions there, I think your query is good in terms of optimization or logic. You might want to use functions to reduce the query size though.  

Highlighted

@jpmartinez , can you please give me some working examples on the creation and usage of functions?