Skip to content

Instantly share code, notes, and snippets.

@Cyreex
Created May 6, 2017 18:29
Show Gist options
  • Save Cyreex/41ff14a48188da94f937adb63f300fc4 to your computer and use it in GitHub Desktop.
Save Cyreex/41ff14a48188da94f937adb63f300fc4 to your computer and use it in GitHub Desktop.
workflow IP-Logging
{
param
(
[Parameter(Mandatory=$true)]
[String] $VMMJOBID,
[Parameter(Mandatory=$true)]
[object] $NAME,
[Parameter(Mandatory=$true)]
[object] $OPERATION
)
# 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 to access MsSQL server.
$MsSQLCred = Get-AutomationPSCredential -Name 'MsSQL-iploggingDB'
[string] $MsSQLLogin = $MsSQLCred.Username
$MsSQLPassword = $MsSQLCred.Password
[string] $MsSQLDatabase = Get-AutomationVariable -Name 'MsSQL-iplogging-Database'
[string] $MsSQLServer = Get-AutomationVariable -Name 'MsSQL-iplogging-Server'
inlinescript {
# Import VMM module.
Import-Module virtualmachinemanager
# Connect to VMM server.
Get-SCVMMServer -ComputerName $Using:VmmServerName -ForOnBehalfOf
$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 3
$JobStatus=$Job.Status
}
# Break if Job failed
if ($JobStatus -eq "Failed") {
write-output "Job Failed!"
write-output JOBid:$job.ID
break
}
#Get IP
$jobfull = Get-SCJob -Job $job -full
if ($Using:Operation -eq "Delete") {
$IP = (($jobfull.AuditRecords).Previous | ? key -eq "IP address" | Select -First 1).Value
} else {
$IP = (($jobfull.AuditRecords).New | ? key -eq "IP address" | Select -First 1).Value
}
### Get data
$Owner = $job.Owner
$Operation = $Using:Operation
$Date = '{0:s}' -f $job.EndTime
$Name = $Using:name
### Write to Database
### 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
###
$Command.CommandText = "INSERT INTO iplog (Owner, Operation, IP, Date, Name) `
VALUES ('{0}','{1}','{2}','{3}','{4}')" `
-f $Owner, $Operation, $IP, $Date, $Name
$Command.ExecuteNonQuery() | out-null
$Connection.Close()
} -PSComputerName $VmmServerName -PSCredential $VmmCredential
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment