Author name: Tim van Kooten Niekerk

About me / Hey I’m Tim. I work as a systems integration specialist for a large educational institution in the Netherlands. A part of my free time I spend making and creating music.

PowerShell Admin Functions (Module)

Some usefull (and less usefull) admin functions…

function fnGetFQDN() {
  param (
	  [switch]$swIsEmail = $false
  )
  $oNETIP = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()
  if ($swIsEmail -eq $true) {
    [string]$Return1 = "noreply@" + $oNETIP.HostName + "." + $oNETIP.DomainName
  } else {
    [string]$Return1 = $oNETIP.HostName + "." + $oNETIP.DomainName
  }  
  return $Return1
}
# Do not forget to register the eventlogsources in windows before you use this function...
function fnWriteEventLog() {
  param (
    [string]$sNode, 
    [string]$sMessage, 
    [string]$sEventLogSource = "DBA",  
    [int]$iEventID = 1001, 
    [string]$sType = "Error"
  )
  if ( ($sNode) -and ($sMessage) ) {
    [string]$sFormatMessage = "Node: " + $sNode + "`r`n`r`n" + $sMessage
    Write-EventLog -LogName Application -Source $sEventLogSource -EntryType $sType -EventID $iEventID -Message $sFormatMessage
  } else {
    return "ERROR: fnWriteEventLog missing arguments..."
  }
}
  
function fnSendMail()
{
  param (
    [string]$sSMTPSrvrName, 
    [string]$sFrom, 
    [string]$sTo, 
    [string]$sSubject, 
    [string]$sBody, 
    [string]$sAttFileName, 
    [switch]$swIsBodyHTML = $false
  )

  if (($sSMTPSrvrName) -and ($sFrom) -and ($sTo) -and $sSubject) {
  
    # Declare mail objects...
    $oMessage = new-object Net.Mail.MailMessage
    $oSMTP = new-object Net.Mail.SmtpClient($sSMTPSrvrName)

    # Pass values to object...
    $oMessage.From = $sFrom
    $oMessage.To.Add($sTo)
    $oMessage.Subject = $sSubject
    $oMessage.IsBodyHtml = $swIsBodyHTML
    $oMessage.BodyEncoding = ([System.Text.Encoding]::UTF8)
    if ($sBody) { $oMessage.Body = $sBody }

    # Add attachment...
    if ($sAttFileName) {
      $oAttachment = new-object Net.Mail.Attachment($sAttFileName)
      $oMessage.Attachments.Add($oAttachment)
    }

    $oSMTP.Send($oMessage)
    if ($? -eq "True") {
      #fnWriteEventLog -sNode (fnGetFQDN) -sMessage "Email has been sent..." -iEventID 1 -sType "Information"
      return "INFO: Email has been sent..." 
    } else {
      fnWriteEventLog -sNode (fnGetFQDN) -sMessage "Error sending mail..." -iEventID 501
      return "ERROR: Error sending e-mail..."
    }

  } else {
    fnWriteEventLog -sNode (fnGetFQDN) -sMessage "fSendMail argument missing..." -iEventID 501
    return "ERROR: Argument(s) missing. [fnSendMail `"SMTPServerName`" `"From`" `"To`" `"Subject`" `"Body`" (`"AttachFileName`" `"isBodyHTML`")]"
  }
}

function fnSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  # Register SQPPS Module...
  $oSQLPSModule = Get-Module | Where {$_.name -eq "SQLPS" }
  if ($oSQLPSModule.Count -eq 0) {
    Import-Module "SQLPS" -WarningAction SilentlyContinue
  }

  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; 
    if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; 
    if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

function fnTCPSock([string]$sHostName, [string]$sPort, [string]$sScript) {
  # Convert script string to byte for write stream...
  [byte[]]$byteScript = [System.Text.Encoding]::ASCII.GetBytes($($sScript))
  # Open 4096 Bytes buffer for read stream...
  [byte[]]$byteBuffer = New-Object System.Byte[] 16384
  
  # Connect to host...
  $oTCPSock = New-Object System.Net.Sockets.TcpClient($sHostName, $sPort)
  $oStream = $oTCPSock.GetStream() 
  
  # Feed script(byte) tot write stream...
  $oStream.Write($byteScript, 0, $byteScript.Length)

  # Return read buffer converted to string...
  [string]$sCount = $oStream.Read($byteBuffer, 0, 16384)
  return [System.Text.Encoding]::ASCII.GetString($byteBuffer, 0, $sCount) 

  # Close objects...
  $oResult.Close()
  $oSockTCP.Close()
}

function fnSSHCmd()
{
  param (
    [string]$sHostName, 
    [int]$iPort = 22, 
    [string]$sUser, 
    [string]$sKeyFile, 
    [string]$sCommand
  )

  # Register SSH.NET (http://www.powershelladmin.com/wiki/SSH_from_PowerShell_using_the_SSH.NET_library) Library...
  $oSSHModule = Get-Module | Where {$_.name -eq "SSH-Sessions" }
  if ($oSSHModule.Count -eq 0) {
    Import-Module "SSH-Sessions" -WarningAction SilentlyContinue
  }
  
  # Start Session...
  $null = New-SshSession -ComputerName $($sHostName) -Port $($iPort) -Username $($sUser) -KeyFile $($sKeyFile)

  # Execute Command...
  [string]$sSshCmd1 = "Invoke-SshCommand -ComputerName " + $sHostName + " -Quiet -Command " + $sCommand
  [string]$sSshResult1 = Invoke-Expression $sSshCmd1

  # Cleanup and return result...
  $null = Remove-SshSession -ComputerName $($sHostName)
  return $sSshResult1
}

function fnGetSFTPItem()
{
  param (
    [string]$sHostName, 
    [int]$iPort = 22, 
    [string]$sUser, 
    [string]$sKeyFile, 
    [string]$sSourcePath,
    [string]$sDestinationPath,
    [bool]$bDeleteSourceFiles = $false, 
    [string]$sHostKeyFP
  )

  # Load SCP assembly. More info: http://winscp.net/eng/docs/library_powershell
  if (-not (Test-Path 'D:\Tools\WinSCP\WinSCPnet.dll')) {
    throw 'Cannot find WinSCPnet assembly'
  }
  # Import assembly
  [void][System.Reflection.Assembly]::LoadFile('D:\Tools\WinSCP\WinSCPnet.dll')

  # Set connection options...
  $oSFTPSessionOpts = New-Object WinSCP.SessionOptions
  $oSFTPSessionOpts.Protocol = [WinSCP.Protocol]::SFTP
  $oSFTPSessionOpts.HostName = $sHostName
  $oSFTPSessionOpts.PortNumber = $iPort
  $oSFTPSessionOpts.UserName = $sUser
  $oSFTPSessionOpts.SshPrivateKeyPath = $sKeyFile
  if ($sSshHostKeyFP) {
    $oSFTPSessionOpts.SshHostKeyFingerprint = $sSshHostKeyFP
    $oSFTPSessionOpts.GiveUpSecurityAndAcceptAnySshHostKey = 0
  } else {
    $oSFTPSessionOpts.GiveUpSecurityAndAcceptAnySshHostKey = 1
  }
    		
  # Setting transfer options
  $oSFTPTransferOptions = New-Object WinSCP.TransferOptions
  $oSFTPTransferOptions.TransferMode = [WinSCP.TransferMode]::Binary

  # Creating session
  $oSFTPSession = New-Object WinSCP.Session
  $oSFTPSession.Open($oSFTPSessionOpts)

  # Sending files...
  $oTransferResult = $oSFTPSession.GetFiles($sSourcePath, $sDestinationPath, $bDeleteSourceFiles, $oSFTPTransferOptions)
  
  # Cleanup and return result...
  $oSFTPSession.Close()
  $oSFTPSession.Dispose()
  return $oTransferResult
}

function fnGetFTPSItem()
{
  param (
    [string]$sHostName, 
    [int]$iPort = 21, 
    [string]$sUser, 
    [string]$sPassword, 
    [string]$sSourcePath,
    [string]$sDestinationPath,
    [bool]$bDeleteSourceFiles = $false
  )

  # Load SCP assembly. More info: http://winscp.net/eng/docs/library_powershell
  if (-not (Test-Path 'D:\Tools\WinSCP\WinSCPnet.dll')) {
    throw 'Cannot find WinSCPnet assembly'
  }
  # Import assembly
  [void][System.Reflection.Assembly]::LoadFile('D:\Tools\WinSCP\WinSCPnet.dll')

  # Set connection options...
  $oSFTPSessionOpts = New-Object WinSCP.SessionOptions
  $oSFTPSessionOpts.Protocol = [WinSCP.Protocol]::FTP
  $oSFTPSessionOpts.FtpMode = [WinSCP.FtpMode]::passive
  $oSFTPSessionOpts.FtpSecure = [WinSCP.FtpSecure]::explicittls
  $oSFTPSessionOpts.HostName = $sHostName
  $oSFTPSessionOpts.PortNumber = $iPort
  $oSFTPSessionOpts.UserName = $sUser
  $oSFTPSessionOpts.Password = $sPassword
    		
  # Setting transfer options
  $oSFTPTransferOptions = New-Object WinSCP.TransferOptions
  $oSFTPTransferOptions.TransferMode = [WinSCP.TransferMode]::Binary

  # Creating session
  $oSFTPSession = New-Object WinSCP.Session
  $oSFTPSession.Open($oSFTPSessionOpts)

  # Sending files...
  $oTransferResult = $oSFTPSession.GetFiles($sSourcePath, $sDestinationPath, $bDeleteSourceFiles, $oSFTPTransferOptions)
  
  # Cleanup and return result...
  $oSFTPSession.Close()
  $oSFTPSession.Dispose()
  return $oTransferResult
}

GNU/Linux Curl SOAP Request using Mutual SSL

POST a Soap Message using curl using Mutual SSL…

curl -k --cert certchain.pem:password --key server.key -X POST \
-d "<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:tot=\"http://timvkn.nl/services/testservice\" ><soapenv:Header/><soapenv:Body><tot:getTest><Name>%</Name></tot:getTest></soapenv:Body></soapenv:Envelope>" \
-H 'Content-Type: application/soap+xml' \
-H 'SOAPAction: "http://timvkn.nl/services/testservice/getTest"' \
https://timvkn.nl/testservice/getTest --tlsv1.2 -o result.xml -v

MSSQL Query Value From XML Datatype

Query some values from a XML datatype result…

SELECT [datumtijd]
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/"; 
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:ID'
                               ).value('.', 'nvarchar(100)')) AS ID
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/";
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:Name'
                               ).value('.', 'nvarchar(100)')) AS Name

      --,[body]
FROM [DATABASE].[dbo].[Logtable] 
WHERE [kolom1] = 'waarde' 
ORDER BY datumtijd DESC

GNU/Linux Docker QuickRef

Simple Dockerfile example for a dockered teiid instance…

# HU Teiid Docker QuickRef:
# ->docker build -t dteiid-example .
# ->docker run --name dteiid-example -p 8081:8080 -p 9991:9990 -v /var/log/docker/dteiid-example/:/opt/jboss/teiid-10.0.0.Final/standalone/log/ --env-file=teiid-test.env --restart unless-stopped -it dteiid-example

FROM teiid/teiid:10.0.0.Final
MAINTAINER Tim van Kooten Niekerk (tim@totietoot.nl)

# Pass logs to host; Location on host needs chmod 1000:1000 so jboss can write...
RUN mkdir /opt/jboss/teiid-10.0.0.Final/standalone/log/
RUN chown -R jboss:jboss /opt/jboss/teiid-10.0.0.Final/standalone/log/
VOLUME /opt/jboss/teiid-10.0.0.Final/standalone/log/
# Add config files...
ADD customization /opt/jboss/teiid-10.0.0.Final/customization/

# Add Drivers...
ADD ojdbc7-12.1.0.1.0.jar /opt/jboss/teiid-10.0.0.Final/standalone/deployments/
ADD sqljdbc42.jar /opt/jboss/teiid-9.3.4/standalone/deployments/

# Deploy VDB's....
ADD cursuscatalogus-vdb-vdb.xml /opt/jboss/teiid-10.0.0.Final/standalone/deployments/

# Configure Environment and Start server...
CMD ["/opt/jboss/teiid-10.0.0.Final/customization/execute.sh"]

# You can expose ports...
EXPOSE 8081 8080
EXPOSE 9991 9990

Build a docker…

docker build -t dteiid-example .

Run, Start, Stop commands…

docker run --name dteiid-example -p 8081:8080 -p 9991:9990 --env-file=teiid-test.env --restart unless-stopped -it dteiid-example
docker ps -a
docker rm <containername|id>
docker start|stop <containername|id>
docker attach <containername|id>
docker image[s] [save|load|ls|rm|prune|...] [-[o|i] image.tar.gz]

Connect to a container…

docker exec [-u 0] -it <containername|id> bash

GNU/Linux ODATA Query Examples

ODATA Query OptionDescription
$orderbyUri parameter for sorting…
$selectUri parameter to select specific coluimns…
$topUri parameter to limit the result…
$skipUri parameter to skip number of rows…
$filterUri parameter to filter result…
$expandUri parameter to expand related entity…
$inlinecountUri parameter to include a total record count…

Example to retrieve metadata from JBoss DV Odata service…

https://localhost:8443/odata/vdbname/$metadata

Retrieve all records in JSON format…

https://localhost:8443/odata/vdbname/modelname?$format=json

Order by column ‘Name’ and retrieve first 5 records…

https://localhost:8443/odata/vdbname/modelname?amp;$orderby=Name&$top=5

Order by column ‘Name’ and retrieve records 6-10 records…

https://localhost:8443/odata/vdbname/modelname?$orderby=Name&skip=5&$top=5

Filter ODATA result ($filter=Name eq ‘John’)…

https://localhost:8443/odata/vdbname/modelname?$filter=Name%20eq%20%27John%27

Select specific rows…

https://localhost:8443/odata/vdbname/modelname?$select=ID,Name,Description

GNU/Linux Test LDAP server SSL/TLS connection

Test LDAP server SSL/TLS connection using LDAP commandline client…

ldapsearch -H ldaps://dc01.totietoot.nl -b "OU=Employees,OU=Totietoot,DC=Totietoot,DC=nl" "userPrincipalName=john@totietoot.nl" -W -D john@totietoot.nl -d 1
env LDAPTLS_REQCERT=never|allow|try|demand LDAPTLS_CACERT=/path/to/ca-cert.pem ldapsearch -H ldaps://dc01.totietoot.nl -b "OU=Employees,OU=Totietoot,DC=Totietoot,DC=nl" "userPrincipalName=john@totietoot.nl" -W -D john@totietoot.nl -d 1