wap-ip-logging

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

}