Transact SQL – XQuery – Ancestor

Background

Playing around with XML, but dug in a quick sand.

XML

Here is the XML

Image

foo.xml

Code

Code

<root>                                                                                                             
  <foo id="0">                                                                                                       
	<foo id="1">                                                                                                   
	  <bar attr="xxx" />                                                                                         
	</foo>                                                                                                         
	<foo id="2">                                                                                                   
	  <bar attr="sam" />                                                                                         
	</foo>                                                                                                         
	<foo id="3">                                                                                                   
	  <tar>                                                                                                      
		<bar attr="samuel" />                                                                                     
	  </tar>                                                                                                     
	</foo>                                                                                                         
  </foo>                                                                                                             
</root>

Code

Find Nodes whose element matches specific value

SQL


declare @id int

set @id = 3

SELECT
         [id] = item.value
                    (
                          '@id'
                        , 'INT'
                    ) 

FROM @foo.nodes('//foo') t (item)

WHERE item.value('@id', 'INT') = @id

Output

XPath.attribute.value.is.3

Pass X PATH :- Child::bar/Attribute

SQL


-- child//bar/@attr=sam
SELECT
         [element.attribute.sam]
            = t.item.query('.')

FROM @foo.nodes('//*[child::bar[@attr=''sam'']]') t (item)

Output

XPath.attribute.value.is.sam

Find Descendants

SQL


-- descendant
SELECT
         [element.descendant]
            = t.item.query('.')

FROM @foo.nodes('//foo[@id=2]/descendant::*[position()=1]') t (item)

Output

XPath.element.descendant

Find Ascendants

SQL


-- ancestor
SELECT
         [element]
            = t.item.query('.')

FROM @foo.nodes('//bar[@attr=''sam'']/ancestor::*[position()=1]') t (item)

Output

Output – Textual


Msg 9335, Level 16, State 1, Line 61
XQuery [nodes()]: The XQuery syntax 'ancestor' is not supported.

Output – Image

XPath.element.ascendant

Source Code Control

Git Hub

TransactSQLXQuery/ancenstor/
Link

 

Summary

Transact SQL/XQuery does not support the ancestor function.

Referenced Work

  1. Stack Overflow
    • XPath to find nearest ancestor element that contains an element that has an attribute with a certain value
      Link
    • xpath: find a node that has a given attribute whose value contains a string
      Link

 

Win OS – Error – “Certificate for local system with Thumbprint is about to expire or already expired”

Background

Want to quickly deal with a MS Windows Error.

The error is logged in the Event Viewer and it reads :-

Certificate for local system with Thumbprint is about to expire or already expired.

Event Viewer

Windows Logs

Application

List

List Events
Image

autoEnrollment.20190702.0802PM

Tabulate
  1. Log Name :- Application
  2. Source :- CertificateServicesClient-AutoEnrollment
  3. Event ID :- 64

Trouble Shooting

PowerShell

Outline

  1. Issue Directory command against local machine’s certificate repository
    • Specifically check machine, not services nor user
  2. Return Sorted By
    • Display the following fields
      • Subject
      • Thumbprint
      • Expire Date

 

Code


Set-StrictMode -Version Latest

Write-Host

Write-host "Certificates in Local Machine Store"
Write-host "==================================="

dir cert:\LocalMachine\my | Sort-Object NotAfter | Foreach-Object `
	{

		$log = "{0}" -f $_.subject;
		Write-host $log

		$log = "`tThumprint :- {0}" -f $_.thumbprint;
		Write-host $log

		$log = "`tExpiry Date :- {0}" -f $_.NotAfter;
		Write-host $log

		Write-host
		#Write-Host

	}

Output

listCertsMachine.20190702.0829PM.PNG

Expiration

We can see that one of our certificates expired at 5 AM this morning.

Avail Open-ssh on Windows

Background

Reading up more on AWS and wanted to review available options for connecting to EC2 instances.

There are a few pathways such as SSH ( Linus & Windows ), Remote Desktop ( Windows ), and API.

SSH Client

On MS Windows, Putty is the most popular SSH Client.

Starting with MS Window 10 Build 1809, Microsoft offers a built-in option via packaging in Open-SSH.

Open-SSH Client

Outline

  1. Add MS Windows Feature
    • Determine MS Windows Version
    • If Windows 10, Build 1809
      • Add feature
        • GUI
        • Command Line
  2. Manual
    • PowerShell/Win32-OpenSSH
      • Download

 

Add MS Windows Feature

Get MS Windows Version

winver
Syntax

winver

Output

Explanation
  1. Version
    • Our Version Number is 1607
    • OS Build 14393.2969
    • Windows 10 Enterprise 2016 LTSB
  2. To use integrated install, we need to be on Build 1809

 

Add Feature OpenSSH

GUI
Outline
  1. Access Control Panel
  2. Access Programs and Features Applet
  3. Turn Windows Features On and Off
  4. Select OpenSSH Client
Screen Shot
Explanation

We are on MS Windows Build 1809, and we can clearly see that “OpenSSH” is not offered.

Powershell WindowsCapability
Outline
  1. Issue Powershell Get-WindowsCapability
  2. If SSH is listed, issue Powershell Add-WindowsCapability
Get Windows Capability – Syntax

powershell -Command "Get-WindowsCapability -Online | ? Name -like '*ssh*'"

Add Windows Capability – Syntax

powershell -Command "Add-WindowsCapability -Online -Name OpenSSH.Client~~~~0.0.1.0"

Screen Shot
Screen Shot – Powershell Get-WindowsCapability

Screen Shot – Powershell Get-WindowsCapability – Output

Explanation

Since “Get-WindowsCapability” does not indicate that ssh packages are available, we will skip invoking the “Add-WindowsCapability“.

 

PowerShell/Win32-OpenSSH

Artifacts

The artifacts are here :-

https://github.com/PowerShell/Win32-OpenSSH/releases
Link

Current Version

Our current version is v7.9

Listing

Explanation
  1. Our OS is 64 bit and so we will go with Win64 files
  2. Not going to be debugging OpenSSH and so will skip files that bore Symbols in their name
  3. Our file is OpenSSH-Win64.zip

Download & Unpack

Please download OpenSSH*.zip and unpack it.

File Listing

Extracted File

  1. ssh.exe
    • SSH Client
  2. sshd.exe
    • SSH Server

Use

Let us issue a couple of commands to make sure that we are able to use our downloaded open-ssh client.

Use -01
Syntax

ssh user@host

Sample

ssh dadeniji@hrdb

Output

Summary

If one is on the latest MS Windows 10, one is able to quickly integrate OpenSSH.

On the other hand, if on an earlier OS or version prior to Windows 10  Build 1809, please download the artifacts, unpack it, and run the unpacked files.

 

Reference

  1. Microsoft
    • Docs / Windows Server / Management / Manage Windows Server systems and environments / Manage Windows with OpenSSH / Getting started with OpenSSH
      • Installation of OpenSSH For Windows Server 2019 and Windows 10
        Link
  2. PowerShell/Win32-OpenSSH
    • PowerShell/Win32-OpenSSH – Releases
      Link

Powershell/Regular Expression :- Get Host’s IP Address

Background

Quick follow-up to our past attempts at using Regular Expression to parse data within a lithe PowerShell Script.

Regular Expression

Positive Look Behind

  1. Regular-Expressions.Info
    • Lookahead and Lookbehind Zero-Length Assertions
      • Web Link
        Link
      • The construct for positive lookbehind is (?<=text): a pair of parentheses, with the opening parenthesis followed by a question mark, “less than” symbol, and an equals sign.
    • Regular Expression Reference: Special Groups
      • Web Link
        Link
      • Matches at a position if the pattern inside the lookbehind can be matched ending at that position.
      • Insight
        regular-expressions.info.positive.lookbehind.20190411.0825AM

Code

Powershell

Outline

  1. Get results of running IPConfig
    • Run IPConfig
    • Capture output in local variable
  2. Prepare Regular Expression
    • Look for IP Address
      • IPv4 Address Only

Regular Expression


(?<=IPv4 Address(\. )*): (\w*).(\w*).(\w*).(\w*)

Actual Code


Set-StrictMode -version 2
 
[string] $regExpPattern=$null;
[string] $inputString=$null;
[int]    $iNumberofMatches=0;
[object] $objList =$null;
[object] $objRegEx =$null;
[string] $objType = $null;
[int]    $iElementID = 0;
[string] $log=$null;
[string] $regExpOption=$null;
[Boolean] $debug = $false;
#[Boolean] $debug = $true;

[string]$domainName = $null;
[array]$domainList = $null;
[int] $domainListNumberofEntries=0;
[string]$topLevelDomain = $null;
[string]$secondLevelDomain = $null;
    
        
# Regular Expression
# Look for IPV4, once found get periods
#   look for 4 digits
# (?<=IPv4 Address(. )*): (\w*).(\w*).(\w*).(\w*)
$regExpPattern= "(?<="
$regExpPattern+= "IPv4 Address" 
$regExpPattern+= "(\. )*";
$regExpPattern+= ")";

$regExpPattern+= ":";
$regExpPattern+= " ";
$regExpPattern+= "(\w*).(\w*).(\w*).(\w*)";

# Regular Expression Option
$regExpOption="IgnoreCase";

# Run IP Config
$inputString = ipconfig

if ($debug)
{

    Write-Host;
    Write-Host;

    $log = "Input is {0} " -f $inputString;

    Write-Host($log);
   
    $log = "==========================================================================="
    Write-Host($log);

    Write-Host;
    
    
}

$log = "[regex]::expression is {0} " -f $regExpPattern;
Write-Host($log);


$objList = ( [regex]::matches( `
                                  $inputString `
                                , $regExpPattern `
                                , $regExpOption`
                             ) `
            )
 
if ($objList)
{
 
    if ($debug)
    {
         
        $objType = $objList.GetType();
 
        $log = "[regex]::matches return type is {0} " -f $objType;
 
        Write-Host($log);
 
    }
 
     
    $iNumberofMatches = $objList.Count;
    
    if ($debug)
    {   
 
        $log = "Number of Matches is {0}" -f $iNumberofMatches;
 
        Write-Host($log);
        
    }       
 
    #$objList;
    
    if ($iNumberofMatches -gt 0)
    {
 
        ForEach ($obj in $objList)
        {

            $ipAddress = $obj.Value;
        
            $log = "IP Address is {0}"  -f $ipAddress;
         
            Write-Host($log);
        
        }   
    }
 
 
}
else
{

	Write-Host "==================="
	$inputString
	Write-Host "==================="

    Write-Host "$objList is null"
}

Output

regex.regAhead.20190411.0145PM

Powershell :- Regular Expression – Using [regex]::matches

Background

In our last post spoke about using the match operator to issue Regular Expression calls.

Lineage

  1. PowerShell :- Regular Expression
    Link

RegEx Class

The -Match operator is a bit restrictive in terms of flexibility and so it is likely better to utilize the RegEx Class.

Outline

  1. Access RegEx Class Match operator
    • The first operand is the text
    • The second operator is the pattern
    • And, the third is RegEx Options
      • In our case, ignorecase
  2. Result is sent back as a collection
    • Specifically System.Text.RegularExpressions.MatchCollection
    • Simple access using index suffixes for our case

Code



Set-StrictMode -version 2

[string] $regExpPattern=$null;
[string] $fullname=$null;
[int]    $iNumberofMatches=0;
[hashtable] $obListofNames =$null;
[object] $objRegEx =$null;
[string] $listofNamesType = $null;
[int]    $iElementID = 0;
[string] $log=$null;
[string] $regExpOption=$null;
[Boolean] $debug = $false;
#[Boolean] $debug = $true;

# Regular Expression
$regExpPattern="(\w+)(\w+)";

# Regular Expression Option
$regExpOption="IgnoreCase";

$fullname="Daniel Adeniji"


$objListofNames = ( [regex]::matches( `
                                          $fullname `
                                        , $regExpPattern `
                                        , $regExpOption`
                                        ) `
                  )

if ($objListofNames)
{

    if ($debug)
    {

        
        $log = "[regex]::expression is {0} " -f $regExpPattern;

        Write-Host($log);

        
        $listofNamesType = $objListofNames.GetType();

        $log = "[regex]::matches return type is {0} " -f $listofNamesType;

        Write-Host($log);

    }

    
    $iNumberofMatches = $objListofNames.Count;

    $log = "Number of Matches is {0}" -f $iNumberofMatches;

    Write-Host($log);

    if ($iNumberofMatches -gt 0)
    {

        $log = "First name is {0}" -f $objListofNames[0];

        Write-Host($log);

    }

    if ($iNumberofMatches -gt 1)
    {

        $log = "Last name is {0}" -f $objListofNames[1];

        Write-Host($log);

    }   

}
else
{
    Write-Host "$objListofNames is null"
}

Output

regex.matches.output.20190406.0935AM

References

  1. Microsoft
    • System.Text.RegularExpressions
      • Regex.Matches Method

PowerShell :- Regular Expression

 

Background

Looking at a useful tool written in PowerShell.

But, it was fading.

Time to play around with Regular Expression, Regex.

Code

Powershell

Objective

  1. Prepare Regular Expression
    • In our case, stored in $regexp
  2. Prepare Input
    • In our case, store in $fullname
  3. Issue RegEx call using -match operator
  4. Results is saved in Hashtable
    • The name of the system populated Hashtable is $hashtable
      • The 0th element is the passed in input
      • 1 is the first match
      • 2 is the second match
      • And, on and on

Implementation


Set-StrictMode -version 2

[string] $regexp=$null;
[string] $fullname=$null;
[int]    $iNumberofMatches=0;
[hashtable] $obListofNames =$null;

$regexp="(\w+) (\w+)";
$fullname="Daniel Adeniji"

$fullname -match $regexp  | Out-Null;

$obListofNames = $Matches;

$iNumberofMatches = $obListofNames.Count-1;

Write-Host("Number of matches :- " + $iNumberofMatches);

if ($obListofNames)
{
    Write-Host("Full Name :- " + $obListofNames[0]);
}

if ($iNumberofMatches -gt 0)
{
    Write-Host("First Name :- " + $obListofNames[1]);
}

if ($iNumberofMatches -gt 1)
{
    Write-Host("Last Name :- " + $Matches[2]);
}

Output

powershell.regex.20190405.0348PM

XPath code Generator – Web Tools

Background

Wanted to quickly review freely available online tools for generating Xpath from XML documents.

Sample XML Documents

Will use these XML documents as samples :-

  1. Microsoft
    • Docs. / NET / C# Guide / Programming guide / Programming concepts / LINQ
      • Sample XML File: Customers and Orders in a Namespace
        Link

 

Tools

  1. Xmltoolbox
    • XPath Generator
  2. Qutoric
  3. XPather.com

 

In depth

Xmltoolbox

XPath Generator

Author

Xmltoolbox is authored by Ole Bille.

Usage

Outline
  1. Launch web browser
  2. And, go here ( http://xmltoolbox.appspot.com/xpath_generator.html )
  3. Paste the XML text into the canvas
  4. In the loaded canvas, click on the elements you want evaluated
  5. Please review and capture the generated Xpath results

 

Screenshots

xpathgenerator.20181217.0344AM

qutoric

PathEnq

Usage

Outline
  1. Launch web browser
  2. And, go here ( http://www.qutoric.com/xslt/analyser/xpathtool.html )
  3. Invoke the Choose File button at the bottom of the screen
  4. Select the file to be evaluated
  5. In the loaded canvas, click on the elements you want evaluated

 

Screen Shots

xmlSource.20181217.0319AM

xmlSource.20181217.0322AM

 

XPather.com

Author’s Guideline

  1. This web app enables you to query XML/HTML documents with your browser in real time. It can generate queries for you too!
    • You can enter your xpath query in the top-left panel and it will be instantly executed against this document.
    • To generate an xpath query for a specific element, please hold CTRL and hover over it. An xpath is generated.

Usage

To generate an XPath, please paste the XML into the canvas.

Once the XML is available and accessible, please move your cursor to the screen area, hold down the Ctrl button while hovering or selecting the XML tag or body.

Samples

Sample #1

xpather.com.20181217.0308AM

Deficiency

  1. Supports Element, but not attribute