Exporting Cache Drive Usage Data Using ControlUp and PowerBI/Excel

A user asked how to export cache drive usage data to PowerBI/Excel for a customer who wants historical data. A scheduled trigger can be used with a specific query to retrieve the information and export it to a CSV file. Another user shared a tip on using wildcards in the query to retrieve data from specific folders.

Read the entire ‘Exporting Cache Drive Usage Data Using ControlUp and PowerBI/Excel’ thread below:

REQ: Export cache drive usage every 5 minutes and feed it into PowerBI/Excel

Customer wants to have historical data for cache drive (Citrix PVS) filling up.

So I think I can go with a scheduled trigger firing the following and putting it into CSV file:

$result = (Invoke-CUQuery -Table LogicalDisks -Fields FolderPath,Computer,DiskName,Capacity,FreeSpace,FreeSpacePercentage -where "DiskName = ‘D:\’").Data

$result | Export-CSV -path cachedrive.csv -Append

Nothing fancy…

BUT if I want to get "just" the information from specific folders like recursively all below "ControlUp OrgName > EUC Environments Sync > SiteName > Delivery groups"?

Something like this doesnt work:

(Invoke-CUQuery -Table LogicalDisks -Fields FolderPath,Computer,DiskName,Capacity,FreeSpace,FreeSpacePercentage -where {$.DiskName = ‘D:\’ -AND $.FolderPath -like ‘delivery groups’}).Data

(Invoke-CUQuery -Table LogicalDisks -Fields FolderPath,Computer,DiskName,Capacity,FreeSpace,FreeSpacePercentage -where {$.DiskName -eq ‘D:\’ -AND $.FolderPath -like ‘delivery groups’}).Data

Sure this isnt related to ControlUp but more to PowerShell 😄

-where doesn’t accept a script block. It expects SQL-like filters.

Invoke-CUQuery -Table LogicalDisks -Fields FolderPath,Computer,DiskName,Capacity,FreeSpace,FreeSpacePercentage -where "DiskName = ‘D:\’ AND FolderPath LIKE ‘delivery groups’"

Also if you want to use a wildcard for the delivery group clause, use this.

-where "DiskName = ‘D:\’ AND FolderPath LIKE ‘%delivery groups%’"

Works a treat!

Thanks Dennis!

Continue reading and comment on the thread ‘Exporting Cache Drive Usage Data Using ControlUp and PowerBI/Excel’.  Not a member? Join Here!