Get SQL Server space usage data for cacti monotoring via WinRM.

SQL_DB_Size.py

#!/usr/bin/env python

import winrm
import sys
import getopt
import json

# set default
host = "localhost"
credencial_file = "/etc/cacti/cactiwinrm.json"
database = "master"

# getopt
opts, args = getopt.getopt(sys.argv[1:], "h:u:d:")
for o, a in opts:
        if o == "-h":
                host = a
        elif o == "-u":
                credencial_file = a
        elif o == "-d":
                database = a
#print host, credencial_file, database

# get credencial data from file
f = open(credencial_file, 'r')
credencial = json.load(f)
f.close()

# script
powershell = """
$targetDB = "{0}"
$result = Invoke-Sqlcmd -Database $targetDB -Query "sp_spaceused"

$DB_size = Invoke-Expression $result.database_size[0].replace(' ', '')
$Unallocated_size = Invoke-Expression $result."unallocated space"[0].replace(' ', '')
$Unallocated_size = [int]$Unallocated_size

$reserved_size = Invoke-Expression $result[1].reserved.Replace(' ', '')
$data_size = Invoke-Expression $result[1].data.Replace(' ', '')
$index_size = Invoke-Expression $result[1].index_size.Replace(' ', '')
$unused_size = Invoke-Expression $result[1].unused.Replace(' ', '')

$log_result = Invoke-Sqlcmd -Database master -Query "DBCC SQLPERF(LOGSPACE)"
$log_used_percentage = ($log_result | ? {{ $_."Database Name" -eq $targetDB }})[2]
$log_used_percentage = [int]$log_used_percentage

echo "DatabaseSize:$DB_size"
echo "UnallocatedSize:$Unallocated_size"
echo "ReservedSize:$reserved_size"
echo "DataSize:$data_size"
echo "IndexSize:$index_size"
echo "UnusedSize:$unused_size"
echo "LogUsedPercentage:$log_used_percentage"
"""

# get data
s = winrm.Session(host, auth=(credencial["username"], credencial["password"]))
r = s.run_ps(powershell.format(database))