Categories
PowerShell Scripting

PowerShell Validate XML Message

Quick (raw) guide to validate a XML message using Powershell.

# Declare schemas opbject…
$schemas = New-Object System.Xml.Schema.XmlSchemaSet

# Read schema from file…
$schemaItem = Get-Item File.xsd
$fileStream = $schemaItem.OpenRead()
$xsd = [Xml.Schema.XmlSchema]::Read($fileStream, $null)
$fileStream.Close()

# Add XSD to schemas & compile…
$schemas.Add($xsd)
$schemas.Compile()

# Read & validate XML…
[xml]$xml = Get-Content .\File.xml
$xml.Schemas = $schemas
$xml.Validate($null)

Categories
MSSQL Server

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
Categories
PowerShell Scripting

PowerShell Webservices Probe

The following script reads URL’s from an array to check if specific web services are online. When the script finds that one or more services are offline it sends an e-mail message (HTML). A TEXT version of the message is created in the Windows EventLog.

Het script uses custum functions: fnSendMail en fnWriteEventLog om berichten af te handelen.

# Global config settings...
[string]$sSMTPServer1 = "smtp.example.org"
[string]$sSubject1 = "EXAMPLE:WebService probes..."
[string]$sToAddress1 = "test@example.org"
[string]$sEventLogSource = "EVENTLOGSOURCE"
[switch]$swAlarm = $false
[array]$aWebServices1 = @(
   "https://example.org/webService?WSDL",
   "https://example.org/webService?WSDL",
)

# Functions and procedures...
function fnProbeWebSvc() {
  param (
    [string]$sWebSvcWSDL
  )
  $oWebSvc1 = New-WebServiceProxy -Uri $sWebSvcWSDL
  if ($oWebSvc1) {
    [switch]$swWebSvcIsAlive = $true
  } else {
    [switch]$swWebSvcIsAlive = $false
  }  
  return $swWebSvcIsAlive
}

# Start Main script...
# Create array for results...
[array]$aWebServiceState = @()

# Fill table from array...
foreach ($row1_aWebServices1 in $aWebServices1) {
  $oWebServiceState = New-Object PSObject -Property @{
    'WebService' = $row1_aWebServices1
    'IsAlive' = (fnProbeWebSvc -sWebSvcWSDL $row1_aWebServices1) }
  # FLip Alarm status if fnProbeWebSvc return value is false...  
  if ($oWebServiceState.IsAlive -eq $false) {[switch]$swAlarm = $true}
  $aWebServiceState += $oWebServiceState
}

# Build HTML and TEXT body w. array data if alarm status is set to true...
if ($swAlarm -eq $true) {
  
  # Body header HTML...
  $sbBody1_HTML = New-Object System.Text.StringBuilder
  [void]$sbBody1_HTML.Append("<html><head><title></title></head><body>`r`n")
  [void]$sbBody1_HTML.Append("<table><tbody><tr bgcolor='grey'><th>WebService</th><th>IsAlive</th></tr>`r`n")

  # Body header TEXT...
  $sbBody1_TEXT = New-Object System.Text.StringBuilder
  [void]$sbBody1_TEXT.Append("Webservice(s) offline. Contact your administrators.`r`n`r`n")

  # Build table rows from array...
  foreach ($row1_aWebServiceState in $aWebServiceState) {
    # Build table rows HTML...
    [void]$sbBody1_HTML.Append("<tr bgcolor='lightblue'><td>")
    [void]$sbBody1_HTML.Append($row1_aWebServiceState.WebService)
    # Row Coloring...
    if ($row1_aWebServiceState.IsAlive -eq $true) {
      [void]$sbBody1_HTML.Append("</td><td bgcolor='lightgreen'>")
    } else {
      [void]$sbBody1_HTML.Append("</td><td bgcolor='lightpink'>")
    }
    [void]$sbBody1_HTML.Append($row1_aWebServiceState.IsAlive)
    [void]$sbBody1_HTML.Append("</td></tr>")

    # Build table rows TEXT (only show failed services...
      if ($row1_aWebServiceState.IsAlive -eq $false) {
        [void]$sbBody1_TEXT.Append($row1_aWebServiceState.WebService)
        [void]$sbBody1_TEXT.Append(": ")
        [void]$sbBody1_TEXT.Append($row1_aWebServiceState.IsAlive)
        [void]$sbBody1_TEXT.Append("`r`n")
      }
  }

  # Mail body footer...
  [void]$sbBody1_HTML.Append("</tbody></table>`r`n")
  [void]$sbBody1_HTML.Append("<html><head><title></title></head><body>`r`n")

  # Send HTML body in mail to admins...
  [string]$sBody1 = $sbBody1_HTML.ToString()
  fnSendMail -sSMTPSrvrName $sSMTPServer1 -sFrom (fnGetFQDN -swIsEmail) -sTo $sToAddress1 -sSubject $sSubject1 -sBody $sBody1 -swIsBodyHTML

  # Send text body to eventlog...
  fnWriteEventLog -sNode "NODENAME" -sMessage $sbBody1_TEXT.ToString() 
}
Categories
PowerShell Scripting

PowerShell WebService Calls

Performing a SOAP web service call using PowerShell. The methods can be requested by means of the command: $oWebSvc1 | Get-Member.

#WebService values...
[string]$sWebSvcWSDL = "https://host.domain.fqdn/Webservice1?wsdl"
[string]$sPostValue1 = "Waarde"
[string]$sPostValue2 = $true

#Create webservice object...
$oWebSvc1 = New-WebServiceProxy -Uri $sWebSvcWSDL

#POST values w. result in een object...
$oResult1 = $oWebSvc1.method1($sPostValue1, $sPostValue2)

#POST values w. result to RAW XML...
[xml]$xResult = $oWebSvc1.method1($sPostValue1, $sPostValues2) | ConvertTo-Xml
$xResult.get_OuterXML() | Out-File C:\Pad\Naar\Bestand.xml
Categories
XML

XML Stylesheet Example

An XML stylesheet can be used to transform XML file to an HTML table. Check out the results of stylesheet below if it is linked to an XML file. In the source of the page you will only display XML.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" >
  
  <xsl:template match="/">
    <html>
    <head><title/></head>
    <body>
      <table border="0pt" style="solid">
        <tbody>
          <!--call table headers-->
          <!--table headers-->
          <tr bgcolor="grey">
            <!--header-->
            <xsl:for-each select="manuals/manual[1]/*">
              <th><xsl:value-of select="name()"/></th>
            </xsl:for-each>
            <th>new-id</th>
          </tr>
          <!--call table rows-->
          <xsl:apply-templates select="manuals/manual">
            <xsl:sort order="ascending" select="title"/>
          </xsl:apply-templates>
        </tbody>
      </table>
    </body>
    </html>
  </xsl:template>

  <xsl:template match="manuals/manual">
    <!--table rows-->
    <!--kleur bepalen-->
    <xsl:variable name="kleur">
      <xsl:choose>
        <xsl:when test="category='PowerShell Scripting'">lightblue</xsl:when>
        <xsl:when test="category='MSSQL Server'">lightpink</xsl:when>
        <xsl:otherwise>white</xsl:otherwise>
      </xsl:choose>
    </xsl:variable>
    <tr bgcolor="{$kleur}">
    <xsl:for-each select="*">
      <td><xsl:value-of select="."/></td>
    </xsl:for-each>
    <!--extra regel t.b.v. newid-->
    <td><xsl:value-of select="id+1000"/></td>
    </tr>
  </xsl:template>

</xsl:stylesheet>