Skip to content

Instantly share code, notes, and snippets.

@Cyreex
Last active August 24, 2016 21:08
Show Gist options
  • Save Cyreex/2be161f1039d329b345f469813092266 to your computer and use it in GitHub Desktop.
Save Cyreex/2be161f1039d329b345f469813092266 to your computer and use it in GitHub Desktop.
#Billing script v.0.7.1
#SCVMM
$cloudName="LinByCloud"
$vmm_cluster = "clvmm"
$TaskType="Hourly"
### DB CONNECTION
$server = "SCVMMDB"
$Database = "BillingDB"
$DisksTableName = "Disks"
$BillingTableName = "Subscriptions"
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';Integrated Security=SSPI"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$dataTable = New-Object -TypeName System.Data.DataTable
$cloud = Get-SCCloud $cloudName
$timestamp = (Get-Date -UFormat %s) -replace ','
$SCVMMSERVER = Get-SCVMMServer -ComputerName $vmm_cluster -ForOnBehalfOf
#Collect Data of VM VHD Size and Type
function GetVmHdd(){
$VHDs=Get-SCVirtualHardDisk -VM $vm
#OBJ When we will return (Size in MB)
$VHD_Prop = @{'VHD_Count'=0;'VHD_STD_SIZE'=0;'VHD_FAST_Size'=0;'VHD_Ultra_Size'=0}
$VHD_Return = New-Object –TypeName PSObject -Property $VHD_Prop
#OBJ Internal
$VHD_Work = "" | SELECT VHD_STD_List,VHD_FAST_List,VHD_ULTRA_List
$VHD_Return.VHD_Count = ($VHDs).Count
# Looking for disk in SQL
foreach ($VHD in $VHDs) {
[string]$StorageType = $NULL #Clearing var
### Get Disk Storage Type
$SqlQuery = "SELECT StorageType FROM Disks WHERE DiskID like '"+$VHD.ID+"'"
$Command.CommandText = $SqlQuery
$result = $command.ExecuteReader()
$dataTable.Load($result)
$StorageType = $dataTable.StorageType
$datatable.Clear()
$result.Close()
###
### Calculating Summary Storage Usage
if ($StorageType -eq "Standard" -OR $StorageType -eq "" -OR $StorageType -eq "System") {
$VHD_Return.VHD_STD_SIZE+=($VHD.MaximumSize/1048576) #Add Standard Disk Size in MB
}
if ($StorageType -eq "Fast") {
$VHD_Return.VHD_FAST_SIZE+=($VHD.MaximumSize/1048576) #Add Fast Disk Size in MB
}
if ($StorageType -eq "Ultra") {
$VHD_Return.VHD_ULTRA_SIZE+=($VHD.MaximumSize/1048576) #Add Ultra Disk Size in MB
}
}
return $VHD_Return
}
if ($TaskType -eq "Hourly") {
## Get all Azure Pack user roles
$Roles = Get-SCUserRole
#Get resources for Subscriptions
foreach ($Role in $Roles) {
$VMs = Get-SCVirtualMachine -cloud $cloud | ? UserRoleID -eq $Role.ID #Get VMs for Subscription
if ($VMs) {
$SummRes = "" | SELECT SummCPU, SummRAM, SummVHDStd, SummVHDFast, SummVHDUltra, SummOSWindows, SummExtAddr
Foreach ($VM in $VMs) {
#Get HDD_Count, HDD_Std, HDD_Fast, HDD_Ultra and SUMM
$VHD_ARR = GetVMHdd
$SummRes.SummVHDStd += $VHD_ARR.HDD_STD_SIZE
$SummRes.SummVHDFast += $VHD_ARR.HDD_FAST_Size
$SummRes.SummVHDUltra += $VHD_ARR.HDD_Ultra_Size
#
#$HDD_ARR #Debug
# If VM Status eq Stopeed - set CPU and RAM usage = "0"
if ($vm.Status -eq "Stopped") {
$CPUCount = 0
$Memory = 0
} else {
$CPUCount = $VM.CPUCount
$Memory = $VM.Memory
}
#SUMM CPU and RAM Usage
$SummRes.SummCPU += $CPUCount
$SummRes.SummRAM += $Memory
# Summ Windows VMs
if ($VM.OperatingSystem.Name -like "*windows*") {
$SummRes.SummOSWindows += 1
}
}
if (!$SummRes.SummOSWindows) { $SummRes.SummOSWindows = 0 }
# Get External IPs Usage
$SummRes.SummExtAddr = 0
$Networks = Get-SCVMNetwork | ? {$_.UserRoleId -eq $Role.ID -AND $_.VMNetworkGateways -ne $null }
if ($Networks) {
foreach ($Network in $Networks) {
$GW = Get-SCVMNetworkGateway -VMNetwork $Network
$IPs = (Get-SCNATConnection -VMNetworkGateway $GW).Count
$SummRes.SummExtAddr += $IPs
}
}
#Write data in DB
$Command.CommandText = "INSERT INTO Subscriptions (timestamp, SubscriptionID, Name, CPU, RAM, VHDStd, VHDFast, VHDUltra, WindowsVM, IPAddress, TaskType) `
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')" `
-f $timestamp, $Role.ID, $Role.Name, $SummRes.SummCPU, $SummRes.SummRAM, $SummRes.SummVHDStd, $SummRes.SummVHDFast, $SummRes.SummVHDUltra, $SummRes.SummOSWindows, $SummRes.SummExtAddr, $TaskType
$Command.ExecuteNonQuery() | out-null
#Debug
#write-Host $Role.ID $Role.Name $SummRes.SummCPU $SummRes.SummRAM $SummRes.SummVHDStd $SummRes.SummVHDFast $SummRes.SummVHDUltra $SummRes.SummOSWindows $SummRes.SummExtAddr $TaskType
}
}
}
#CLOSE SQL CONNECTION
$Connection.Close()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment