Exclude UBS drives from query results for Percentage FreeSpace

%3CLINGO-SUB%20id%3D%22lingo-sub-482401%22%20slang%3D%22en-US%22%3EExclude%20UBS%20drives%20from%20query%20results%20for%20Percentage%20FreeSpace%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482401%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20below%20query%20to%20alert%20on%20Percentage%20FreeSpace%20on%20Drives....we%20want%20to%20exclude%20UBS%20drives%20from%20the%20result%20set.%20Is%20there%20a%20way%20to%20figure%20if%20the%20instance%20is%20a%20UBS%20drive%20or%20not%20and%20exclude%20it%20from%20the%20Query.%20Appreciate%20your%20response%20on%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerf%3CBR%20%2F%3E%7C%20where%20CounterName%20%3D%3D%20'%25%20Free%20Space'%20and%20InstanceName%20!%3D%20'_Total'%3CBR%20%2F%3E%7C%20where%20InstanceName%20!contains%20'HarddiskVolume'%3CBR%20%2F%3E%7Csummarize%20AggregatedValue%3Davg(CounterValue)%20by%20Computer%2CInstanceName%2Cbin(TimeGenerated%2C%205m)%3CBR%20%2F%3E%7C%20where%20AggregatedValue%20%26lt%3B%205%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ERC%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482401%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482484%22%20slang%3D%22en-US%22%3ERe%3A%20Exclude%20UBS%20drives%20from%20query%20results%20for%20Percentage%20FreeSpace%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F272206%22%20target%3D%22_blank%22%3E%40RCDevops777%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20suspect%20you'll%20need%20another%20source%20of%20data%20as%20well%20as%20the%20%3CSTRONG%3EPerf%3C%2FSTRONG%3E%20table.%26nbsp%3B%20Ideas%20could%20be%2C%20a%20custom%20log%20(using%20PowerShell%20%2F%20Logic%20Apps%2C%20one%20%3CA%20href%3D%22https%3A%2F%2Fblog.peterschen.de%2Fexcluding-deallocated-vms-from-availability-alerting-1o3%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eexample%20of%20this)%20t%3C%2FA%3Eo%20find%20this%20data%20and%20upload%20that%20to%20Log%20analytics%2C%20or%20enable%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Fwindows%2Fit-pro%2Fwindows-server-2012-R2-and-2012%2Fjj574128(v%3Dws.11)%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Eauditing%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EHowever%20note%2C%20auditing%20would%20only%20have%20data%20(event%20id%204663)%20for%20new%20drives%20added.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20are%20lucky%20to%20have%20USB%20drives%20of%20a%20certain%20size%20then%20maybe%20exclude%20those%3F%3C%2FP%3E%0A%3CP%3ENote%3A%20you%20need%20the%20extra%20%3CSTRONG%3EPerf%3C%2FSTRONG%3E%20counter%26nbsp%3B%22Free%20Megabytes%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%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%2F%2F%7C%20where%20Computer%20startswith%20%22RDS%22%20%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%20drives%20with%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%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20796px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110233i34C0C32A13293DFC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-04-25%20085324.jpg%22%20title%3D%22Annotation%202019-04-25%20085324.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExample%20PowerShell%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdevblogs.microsoft.com%2Fscripting%2Finventory-drive-types-by-using-powershell%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdevblogs.microsoft.com%2Fscripting%2Finventory-drive-types-by-using-powershell%2F%3C%2FA%3E%26nbsp%3Band%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Frunbook-datacollect%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Frunbook-datacollect%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi,

 

I have the below query to alert on Percentage FreeSpace on Drives....we want to exclude UBS drives from the result set. Is there a way to figure if the instance is a UBS drive or not and exclude it from the Query. Appreciate your response on this.

 

Perf
| where CounterName == '% Free Space' and InstanceName != '_Total'
| where InstanceName !contains 'HarddiskVolume'
|summarize AggregatedValue=avg(CounterValue) by Computer,InstanceName,bin(TimeGenerated, 5m)
| where AggregatedValue < 5

 

Thanks

RC

 

 

1 Reply
Highlighted

@RCDevops777 

 

I suspect you'll need another source of data as well as the Perf table.  Ideas could be, a custom log (using PowerShell / Logic Apps, one example of this) to find this data and upload that to Log analytics, or enable auditing

However note, auditing would only have data (event id 4663) for new drives added.  

 

if you are lucky to have USB drives of a certain size then maybe exclude those?

Note: you need the extra Perf counter "Free Megabytes"

 

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

Perf
//| where Computer startswith "RDS" 
| 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 drives with 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

Annotation 2019-04-25 085324.jpg

 

Example PowerShell: https://devblogs.microsoft.com/scripting/inventory-drive-types-by-using-powershell/ and https://docs.microsoft.com/en-us/azure/azure-monitor/platform/runbook-datacollect