SOLVED

Assistance with Log Analytics Disk Query

%3CLINGO-SUB%20id%3D%22lingo-sub-2593561%22%20slang%3D%22en-US%22%3EAssistance%20with%20Log%20Analytics%20Disk%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2593561%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20hoping%20to%20get%20some%20help%20with%20log%20analytics%2C%20I'm%20trying%20to%20write%20a%20simple%20query%20that%20returns%20the%20percentage%20of%20used%20disk%20space%20for%20both%20Windows%20and%20Linux%20VMs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Linux%20VMs%20the%20following%20works%20great%20and%20display%20exactly%20what%20I%20am%20looking%20for.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Hairy_Zeus_0-1627452534668.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F298865i0E4D702A538E5A84%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Hairy_Zeus_0-1627452534668.png%22%20alt%3D%22Hairy_Zeus_0-1627452534668.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBut%20if%20I%20change%20%22Logical%20Disk%22%20to%20%22LogicalDisk%22%20for%20Windows%20VMs%20it%20doesn't%20return%20any%20records%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Hairy_Zeus_2-1627452680438.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F298867iFFEB71FB6C90E316%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Hairy_Zeus_2-1627452680438.png%22%20alt%3D%22Hairy_Zeus_2-1627452680438.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20can%20query%20free%20disk%20space%20on%20Windows%20VMs%20using%20this%20code%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54923%22%20target%3D%22_blank%22%3E%40Noa%20Kuperberg%3C%2FA%3E%26nbsp%3Bbut%20I'm%20looking%20for%20used%20space%2C%20not%20free%20space.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerf%3CBR%20%2F%3E%7C%20where%20ObjectName%20%3D%3D%20%22LogicalDisk%22%20or%20%2F%2F%20the%20object%20name%20used%20in%20Windows%20records%3CBR%20%2F%3EObjectName%20%3D%3D%20%22Logical%20Disk%22%20%2F%2F%20the%20object%20name%20used%20in%20Linux%20records%3CBR%20%2F%3E%7C%20where%20CounterName%20%3D%3D%20%22Free%20Megabytes%22%3CBR%20%2F%3E%7C%20summarize%20avg_free_disk_MB%3Davg(CounterValue)%20by%20Computer%2C%20InstanceName%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2595440%22%20slang%3D%22en-US%22%3ERe%3A%20Assistance%20with%20Log%20Analytics%20Disk%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2595440%22%20slang%3D%22en-US%22%3EThis%20is%20exactly%20what%20I%20was%20looking%20for%2C%20you're%20a%20life%20saver.%20Thank%20you%20very%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2595220%22%20slang%3D%22en-US%22%3ERe%3A%20Assistance%20with%20Log%20Analytics%20Disk%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2595220%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1114300%22%20target%3D%22_blank%22%3E%40Hairy_Zeus%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMaybe%20this%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EPerf%0A%2F%2F%7C%20where%20Computer%20!startswith%20%22A%22%20%2F%2For%20Computer%20startswith%20%22J%22%20%2F%2Ftesting%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(1d))%0A%7C%20where%20CounterName%20in%20(%20%22%25%20Free%20Space%22%20%2C%20%22%25%20Used%20Space%22%2C%20%22Free%20Megabytes%22)%0A%7C%20where%20InstanceName%20!contains%20'Harddisk'%20and%20InstanceName%20!%3D%20'_Total'%0A%7C%20summarize%20PctFree%3Davgif(CounterValue%2C%20CounterName%20%3D%3D%20%22%25%20Free%20Space%22%20)%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20Linux%20%20%3Davgif(CounterValue%2C%20CounterName%20%3D%3D%20%22%25%20Used%20Space%22)%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20MbFree%20%3Davgif(CounterValue%2C%20CounterName%20%3D%3D%20%22Free%20Megabytes%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20arg_max(TimeGenerated%2C%20Computer)%20by%20Computer%2C%20InstanceName%20%0A%7C%20extend%20PctFree%20%3D%20iif(isnan(PctFree)%2CLinux%2CPctFree)%0A%7C%20project-away%20Linux%2C%20Computer1%0A%7C%20project%20TotalSizeGB%3Dround(MbFree*100%2FPctFree%2F1024%2C0)%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20round(PctFree%2C2)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20round(MbFree%2C2)%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20Computer%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20InstanceName%0A%7C%20summarize%20FreePCT%3Davg(PctFree)%20by%20Computer%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20InstanceName%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20TotalSizeGB%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20FreeGB%20%3D%20round(MbFree%20%2F%201024%2C2)%0A%7C%20sort%20by%20Computer%20asc%2C%20InstanceName%20asc%0A%7C%20project%20Computer%2C%20InstanceName%2C%20TotalSizeGB%2C%20FreeGB%2C%20%20GBinUse%20%3D%20TotalSizeGB%20-%20FreeGB%2C%20FreePCT%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594959%22%20slang%3D%22en-US%22%3ERe%3A%20Assistance%20with%20Log%20Analytics%20Disk%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3Bthanks%20for%20the%20reply%2C%20much%20appreciated.%20This%20is%20perfect%20for%20Windows%20servers%20but%20it%20doesn't%20look%20like%20it's%20pulling%20any%20data%20for%20Linux%20servers%2C%20any%20idea%20how%20I%20can%20pull%20the%20same%20data%20in%20the%20same%20query%20for%20Linux%20servers%20also%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2594848%22%20slang%3D%22en-US%22%3ERe%3A%20Assistance%20with%20Log%20Analytics%20Disk%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2594848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1114300%22%20target%3D%22_blank%22%3E%40Hairy_Zeus%3C%2FA%3E%26nbsp%3BYou%20need%20two%20Perf%20counters%2C%20this%20is%20from%20a%20few%20years%20ago%2C%20so%20could%20be%20improved%20on%20I%20think%2C%20but%20it%20works%20or%20at%20least%20can%20give%20you%20an%20idea.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%2F%2F%0A%2F%2F%20combine%20%25%20free%20and%20Free%20space%20to%20get%20volume%20size%20as%20well%20as%20%25free%0A%2F%2F%0A%0APerf%0A%7C%20where%20Computer%20startswith%20%22RDS%22%20%0A%2F%2F%20add%20other%20computers%20here%0A%7C%20where%20CounterName%20%3D%3D%20%22Free%20Megabytes%22%0A%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(1d))%0A%7C%20where%20InstanceName%20has%20%22%3A%22%20and%20strlen(InstanceName)%20%3D%3D2%20%2F%2F%20only%20look%20at%20drive%20letters%0A%7C%20summarize%20MbFree%3Davg(CounterValue)%20by%20Computer%2CInstanceName%2Cbin(TimeGenerated%2C%205m)%0A%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%20by%20Computer%2CInstanceName%0A%7Cjoin%20kind%3D%20inner%0A(%0A%20%20%20%20Perf%0A%20%20%20%20%7C%20where%20CounterName%20%3D%3D%20%22%25%20Free%20Space%22%0A%20%20%20%20%7C%20where%20TimeGenerated%20%26gt%3B%20startofday(ago(1d))%0A%20%20%20%20%7C%20where%20InstanceName%20has%20%22%3A%22%20and%20strlen(InstanceName)%20%3D%3D2%20%2F%2F%20only%20look%20at%20drive%20letters%0A%20%20%20%20%7C%20summarize%20PctFree%3Davg(CounterValue)%20by%20Computer%2CInstanceName%2Cbin(TimeGenerated%2C%205m)%0A%20%20%20%20%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%20by%20Computer%2CInstanceName%0A)%0Aon%20Computer%20%2C%20InstanceName%20%0A%7C%20project%20%20%20TotalSizeGB%3Dround(MbFree*100%2FPctFree%2F1024%2C0)%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20round(PctFree%2C2)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20round(MbFree%2C2)%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20Computer%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20InstanceName%0A%7C%20summarize%20FreePCT%3Davg(PctFree)%20by%20Computer%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20DriveLetter%20%3D%20InstanceName%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20TotalSizeGB%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20FreeGB%20%3D%20round(MbFree%20%2F%201024%2C2)%0A%7C%20sort%20by%20DriveLetter%20%20asc%0A%7C%20project%20Computer%2C%20DriveLetter%2C%20TotalSizeGB%2C%20FreeGB%2C%20FreePCT%2C%20Inuse%20%3D%20TotalSizeGB%20-%20FreeGB%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Good Morning all,

 

I'm hoping to get some help with log analytics, I'm trying to write a simple query that returns the percentage of used disk space for both Windows and Linux VMs.

 

For Linux VMs the following works great and display exactly what I am looking for.

Hairy_Zeus_0-1627452534668.png

But if I change "Logical Disk" to "LogicalDisk" for Windows VMs it doesn't return any records

Hairy_Zeus_2-1627452680438.png

I can query free disk space on Windows VMs using this code from @Noa Kuperberg but I'm looking for used space, not free space.

 

Perf
| where ObjectName == "LogicalDisk" or // the object name used in Windows records
ObjectName == "Logical Disk" // the object name used in Linux records
| where CounterName == "Free Megabytes"
| summarize avg_free_disk_MB=avg(CounterValue) by Computer, InstanceName

 

Any help much appreciated.

 

Thanks!

 

4 Replies

@Hairy_Zeus You need two Perf counters, this is from a few years ago, so could be improved on I think, but it works or at least can give you an idea.

 

//
// combine % free and Free space to get volume size as well as %free
//

Perf
| where Computer startswith "RDS" 
// add other computers here
| where CounterName == "Free Megabytes"
| where TimeGenerated > startofday(ago(1d))
| where InstanceName has ":" and strlen(InstanceName) ==2 // only look at drive letters
| summarize MbFree=avg(CounterValue) by Computer,InstanceName,bin(TimeGenerated, 5m)
| summarize arg_max(TimeGenerated, *) by Computer,InstanceName
|join kind= inner
(
    Perf
    | where CounterName == "% Free Space"
    | where TimeGenerated > startofday(ago(1d))
    | where InstanceName has ":" and strlen(InstanceName) ==2 // only look at drive letters
    | summarize PctFree=avg(CounterValue) by Computer,InstanceName,bin(TimeGenerated, 5m)
    | summarize arg_max(TimeGenerated, *) by Computer,InstanceName
)
on Computer , InstanceName 
| project   TotalSizeGB=round(MbFree*100/PctFree/1024,0), 
            round(PctFree,2),
            round(MbFree,2), 
            Computer, 
            InstanceName
| summarize FreePCT=avg(PctFree) by Computer,
            DriveLetter = InstanceName,
            TotalSizeGB,
            FreeGB = round(MbFree / 1024,2)
| sort by DriveLetter  asc
| project Computer, DriveLetter, TotalSizeGB, FreeGB, FreePCT, Inuse = TotalSizeGB - FreeGB

 

@Clive Watson thanks for the reply, much appreciated. This is perfect for Windows servers but it doesn't look like it's pulling any data for Linux servers, any idea how I can pull the same data in the same query for Linux servers also?

best response confirmed by Hairy_Zeus (New Contributor)
Solution

@Hairy_Zeus

 

Maybe this?

 

Perf
//| where Computer !startswith "A" //or Computer startswith "J" //testing
| where TimeGenerated > startofday(ago(1d))
| where CounterName in ( "% Free Space" , "% Used Space", "Free Megabytes")
| where InstanceName !contains 'Harddisk' and InstanceName != '_Total'
| summarize PctFree=avgif(CounterValue, CounterName == "% Free Space" ), 
            Linux  =avgif(CounterValue, CounterName == "% Used Space"), 
            MbFree =avgif(CounterValue, CounterName == "Free Megabytes"),
            arg_max(TimeGenerated, Computer) by Computer, InstanceName 
| extend PctFree = iif(isnan(PctFree),Linux,PctFree)
| project-away Linux, Computer1
| project TotalSizeGB=round(MbFree*100/PctFree/1024,0), 
            round(PctFree,2),
            round(MbFree,2), 
            Computer, 
            InstanceName
| summarize FreePCT=avg(PctFree) by Computer,
            InstanceName,
            TotalSizeGB,
            FreeGB = round(MbFree / 1024,2)
| sort by Computer asc, InstanceName asc
| project Computer, InstanceName, TotalSizeGB, FreeGB,  GBinUse = TotalSizeGB - FreeGB, FreePCT

 

This is exactly what I was looking for, you're a life saver. Thank you very much!