Skip to content

Instantly share code, notes, and snippets.

@Cyreex
Last active September 7, 2016 14:11
Show Gist options
  • Save Cyreex/b97a713d0a86e049c06e7926ea1ca67b to your computer and use it in GitHub Desktop.
Save Cyreex/b97a713d0a86e049c06e7926ea1ca67b to your computer and use it in GitHub Desktop.
# Billing-Subscriptions runbook
# Version 0.7.2
workflow Billing-Subscriptions
{
param
(
[Parameter(Mandatory=$false)]
[String] $SubscriptionID,
[Parameter(Mandatory=$true)]
[String] $VMMJOBID
)
# Connection to access VMM server.
$VmmConnection = Get-AutomationConnection -Name 'VmmConnection'
$VmmServerName = $VmmConnection.ComputerName
$SecurePassword = ConvertTo-SecureString -AsPlainText -String $VmmConnection.Password -Force
$VmmCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $VmmConnection.Username, $SecurePassword
# Connection data to access MsSQL server.
$MsSQLCred = Get-AutomationPSCredential -Name 'MsSQL-BillingDB'
[string] $MsSQLLogin = $MsSQLCred.Username
$MsSQLPassword = $MsSQLCred.Password
[string] $MsSQLDatabase = Get-AutomationVariable -Name 'MsSQL-Billing-Database'
[string] $MsSQLServer = Get-AutomationVariable -Name 'MsSQL-Billing-Server'
inlinescript {
Write-output "Start Inline"
# Import VMM module.
Import-Module virtualmachinemanager
# Connect to VMM server.
Get-SCVMMServer -ComputerName $Using:VmmServerName -ForOnBehalfOf
### VARS DB Settings
$SQLserver = $USING:MsSQLServer
$SQLDatabase = $USING:MsSQLDatabase
$SQLuser = $USING:MsSQLLogin
$SQLSecurePassword = $USING:MsSQLPassword
# We need unsecure password to connect DB
$SQLBSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($SQLSecurePassword)
$SQLUnsecurePassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($SQLBSTR)
### MsSQL CONNECTION
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "Server = '$SQLServer';database='$SQLDatabase'; User ID = '$SQLuser'; Password = '$SQLUnsecurePassword';trusted_connection=true; Integrated Security=false"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$dataTable = New-Object -TypeName System.Data.DataTable
###
# if vmmjobid eq 0 - it sheduled task
if ($USING:vmmjobid -ne "0") {
$job = Get-SCJob -ID $USING:vmmjobid
## Check Job Status
$JobStatus=$Job.Status
#Wait Until Task Completed
while ($JobStatus -eq "Running") {
write-output "Start Sleep"
start-sleep 5
$JobStatus=$Job.Status
}
#Test Job Result
if ($JobStatus -eq "Failed") {
write-output "Job Failed!"
write-output JOBid:$job.ID
break
}
$Owner = $Job.Owner
write-output $Owner
} else {
$owner = $USUNG:SubscriptionID #if $SubscriptionID eq $null - $owner eq $null too
$TaskType = "Sheduled"
}
if (!$TaskType) {
$TaskType = "Event"
}
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
}
function WriteDataToDB() {
$Command.CommandText = "INSERT INTO Subscriptions (timestamp, SubscriptionID, Name, CPU, RAM, VHDStd, VHDFast, VHDUltra, WindowsVM, IPAddress, TaskType, Date) `
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')" `
-f $timestamp, $Role.ID, $Role.Name, $SummRes.SummCPU, $SummRes.SummRAM, $SummRes.SummVHDStd, $SummRes.SummVHDFast, $SummRes.SummVHDUltra, $SummRes.SummOSWindows, $SummRes.SummExtAddr, $TaskType, $Date
$Command.ExecuteNonQuery() | out-null
#Debug
#write-Output $Role.ID $Role.Name $SummRes.SummCPU $SummRes.SummRAM $SummRes.SummVHDStd $SummRes.SummVHDFast $SummRes.SummVHDUltra $SummRes.SummOSWindows $SummRes.SummExtAddr $TaskType
}
#Start task for correcting subscriprion data (if VM stopped from OS)
if ($TaskType -eq "Sheduled") {
$jobs = Get-ScJob -Newest 1| ? Name -eq "Refresh Virtual Machine State" #Get all update Jobs Lash hour
foreach ($job in $jobs) {
$fulljob = Get-job -job $job -full
if (($fulljob.AuditRecords.new).Value -eq "Stopped") { # If VM Stopped
$VMid = ($fulljob.AuditRecords.objectdata).ID
$VM = Get-SCVirtualMachine -ID $VMid
if ($VM.UserRole.ToString() -ne "Administrator") { #It not Administrator VMs
$Role = Get-ScUserRole -ID $VM.UserRoleID
$SummRes = "" | SELECT SummCPU, SummRAM, SummVHDStd, SummVHDFast, SummVHDUltra, SummOSWindows, SummExtAddr
#If user stopeed VM - CPU and RAM usage == 0, so we should show it in billing
$SummRes.SummCPU = $VM.CPUCount*(-1)
$SummRes.SummRAM = $VM.Memory*(-1)
$TaskType = "Correcting"
#Job end time to Unix time
$BigBang = Get-Date -Date "01/01/1970"
$timestamp = (New-TimeSpan -Start $BigBang -End $job.EndTime).TotalSeconds
$date = '{0:s}' -f $job.EndTime
WriteDataToDB
}
}
}
$Role = $NULL
$TaskType = "Sheduled"
}
#Get all owner subscriptions
$Roles = Get-SCUserRole | ? {$_.Name -like "$owner*" -AND $_.Name -ne "Administrator"}
foreach ($Role in $Roles) {
$VMs = Get-SCVirtualMachine | ? UserRoleID -eq $Role.ID #Get VMs for Subscription
###$Userrole = Get-SCUserRole -ID $Role.ID -VMMServer $SCVMMSERVER
###$VMs =Get-SCVirtualMachine -OnBehalfOfUserRole $Userrole -OnBehalfOfUser $Userrole.Name -VMMServer $SCVMMSERVER #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.VHD_STD_SIZE
$SummRes.SummVHDFast += $VHD_ARR.VHD_FAST_Size
$SummRes.SummVHDUltra += $VHD_ARR.VHD_Ultra_Size
#
#$HDD_ARR #Debug
# If VM Status eq Stopeed - set CPU and RAM usage = "0"
if ($vm.Status -eq "Stopped" -OR $vm.Status -eq "PowerOff") {
$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
}
}
$timestamp = Get-Date -UFormat %s
$date = Get-Date -Format s
WriteDataToDB
}
}
#CLOSE DB Connection
$Connection.Close()
} -PSComputerName $VmmServerName -PSCredential $VmmCredential
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment