IP-Logging-FirstStart

$VmmServerName = "VMM01"
$SQLserver = "VMMDB"
$SQLDatabase = "IploggingDB"
$SQLuser = "MyUser"
$SQLPassword = "SecretPWD"


# Import VMM module.
Import-Module virtualmachinemanager
       
# Connect to VMM server.
Get-SCVMMServer -ComputerName $VmmServerName -ForOnBehalfOf 
        
$Operation = "Create"
$date = get-date     
$Date = '{0:s}' -f $date
$Name = "Manual"        
               
### MsSQL CONNECTION
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "Server = '$SQLServer';database='$SQLDatabase'; User ID = '$SQLuser'; `
Password = '$SQLPassword';trusted_connection=true; Integrated Security=false"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
###

$VMnetworks = Get-SCVMNetwork

foreach ($VMnetwork in $VMnetworks) {

    $owner = $VMnetwork.Owner

    if ($owner) {

        $Gateways = Get-SCVMNetworkGateway -VMNetwork $VMnetwork

        foreach ($Gateway in $Gateways) {

            $IPs = ((Get-SCNATConnection -VMNetworkGateway $Gateway).Rules | ? ExternalPort -eq 0).Name

                foreach ($IP in $IPs) {

                    $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()