JiMetrics

Here is a PowerShell script to gather Windows host disk info such as disk label, drive letter, total size, free size. Please create a SQL Server Agent job for this. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get.

Disk usage changes fairly frequently, therefore scheduling this to run daily would be my suggestion. You can always manually kick off this job if needed.

Remember to change “sql1” to your host instance name

insertStorage.ps1

$HostList = Invoke-Sqlcmd -Query "EXEC Windows.Host_Select_HostID_HostName" -ServerInstance "sql1" -Database "JiMetrics"
$HostList | ForEach-Object {
    $HostName = $_.HostName
    $HostID = $_.HostID
#Note: DriveType 5 is CD/DVD, DriveType 2 is removable disk therefore we don't care. We only care about LocalDisk, which is DriveType 3
    try {
            $WmiResults = get-wmiobject -computername $hostName Win32_volume | where { $_.DriveType -eq 3}
        }
    catch [Exception] {
        continue
    }
    $WmiResults | foreach {
        $DiskPath = $_.Name
        if (-not ($DiskPath.StartsWith("\\"))) {
            $DiskSizegB = ($_.Capacity / 1gb) + 1
            $DiskFreeGB = ($_.FreeSpace / 1gb) + 1
            $DiskFormat, $DiskLabel = $_.FileSystem, $_.Label
            $sql = "EXEC Windows.Storage_Insert $HostID, '$DiskPath', '$DiskFormat', '$DiskLabel', $DiskSizeGB, $DiskFreeGB"
            Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics"
    }}}