Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- # Billing-Subscriptions runbook
- # Version 0.7
- 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"
- }
- 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"
- (New-TimeSpan -Start $BigBang -End $job.EndTime).TotalSeconds
- $timestamp = (Get-Date).ToFileTime()
- $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
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement