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.

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

PowerShell Retrieve OAUTH Access Token

PowerShell function example to retrieve an OAUTH2 access token from ADFS using form-based authentication or by using a previously retrieved OAUTH Refresh Token.

function fnGetOauthXSToken()
{
  param (
    [string]$ADFSOAUTHAuthorizeUri, 
	[string]$ADFSOAUTHGetTokenUri, 
	[string]$Client_ID, 
	[string]$Resource, 
	[string]$Redirect_URI,
	[string]$RefreshToken, 
	[string]$UserName, 
	[string]$Password,
	[string]$Secret
	)

  if ( $RefreshToken -ne "" ) { 
    
    # Get OAUTH Access Token by using OAUTH refresh_token...

    ## Retrieve OAUTH Token...
    $vPostValues = "grant_type=refresh_token&client_id=" + $Client_ID + "&redirect_uri=" + $Redirect_URI + "&refresh_token=" + $RefreshToken
    $oResult0 = Invoke-RestMethod -Method Post -Uri $ADFSOAUTHGetTokenUri -UserAgent "Windows-AzureAD-Authentication-Provider" -Body $vPostValues -ContentType application/x-www-form-urlencoded

  } else {
    
    # Get OAUTH Access Token by using authorization_code (username and password)... 
  
    ## Build authentication Uri and create websession...
    $sUri = $ADFSOAUTHAuthorizeUri + "?response_type=code&client_id=" + $Client_ID + "&resource=" + $Resource + "&redirect_uri=" + $Redirect_URI
    $oWebSession = New-Object Microsoft.PowerShell.Commands.WebRequestSession

    ## Authenticate by using username and password (formbased)...
    $aPostValues = @{UserName=$UserName; Password=$Password; AuthMethod='FormsAuthentication' }
    $oResult0 = Invoke-WebRequest -Method Post -Uri $sUri -UserAgent "Windows-AzureAD-Authentication-Provider" -Body $aPostValues -Websession $oWebSession -MaximumRedirection 0 -ErrorAction SilentlyContinue

    ## Retrieve authorization code...
    $oResult0 = Invoke-WebRequest -Uri $sUri -UserAgent "Windows-AzureAD-Authentication-Provider" -Websession $oWebSession -MaximumRedirection 0 -ErrorAction SilentlyContinue
    $sCode = $oResult0.Headers.Location.Substring($oResult0.Headers.Location.IndexOf("?code=") + 6, ($oResult0.Headers.Location.Length - ($oResult0.Headers.Location.IndexOf("?code=") + 6) ) )
  
    ## Cleanup websession...
    $oResult0 = $null
    $oWebSession = $null
    $aPostValues = $null
  
    ## Retrieve OAUTH Token...
    if ($Secret -eq "") {
      $vPostValues = "grant_type=authorization_code&client_id=" + $Client_ID + "&redirect_uri=" + $Redirect_URI + "&code=" + $sCode
    } else {
      $vPostValues = "grant_type=authorization_code&client_id=" + $Client_ID + "&redirect_uri=" + $Redirect_URI + "&code=" + $sCode + "&client_secret=" + $Secret
    }
    $oResult0 = Invoke-RestMethod -Method Post -Uri $ADFSOAUTHGetTokenUri -UserAgent "Windows-AzureAD-Authentication-Provider" -Body $vPostValues -ContentType application/x-www-form-urlencoded
  
  }
  
  # Return Result and Cleanup...
  return $oResult0
  $vPostValues = $null
  $oResult0 = $null
}

GNU/Linux JBoss Fuse Shell ActiveMQ Commands

Display basic queue information…

activemq:query -QQueue=* --view Name,EnqueueCount,DequeueCount,QueueSize

Display queue messages…

activemq:browse --amqurl tcp://localhost:61616  --user [username] --password [password] queue:[queuename]

Purge a specific message from the command line…

activemq:purge --msgsel "JMSMessageID='ID:XXXXXXXX-000000-0000000000000-0:0:00:0:0'" [queuename]

Purge all messages from a specific queue…

activemq:purge [queuename]