SQL Server & PowerShell – Get Memory Utilization

Background

Earlier today I found myself trying to better understand a system.

As part of my quick read-up, I found a one line code that shows how to read memory utilization via Powershell.

Code

Overview

Here is that one line code in far too many lines.

Powershell


Set-StrictMode -Version Latest;
Import-Module SQLPS -DisableNameChecking

function displayPath($path)
{
	[object] $objGI = $null;
	[int] $physicalMemoryUsageInKB = 0;
	[int] $physicalMemoryUsageInMB = 0;

	$objGI = (gi $path) 
	
	#Get Data
	$displayName = $objGI.DisplayName;
	$physicalMemoryUsageInKB = $objGI.PhysicalMemoryUsageInKB;
	$physicalMemoryUsageInMB = $objGI.PhysicalMemoryUsageInKB / 1024;
	
	#Display Data       
	$strLog = "`tPath {0}" -f $path;        
	Write-Host $strLog
	
	$strLog = "`tInstance {0}" -f $displayName;     
	Write-Host $strLog
	
	$strLog = "`tPhysical Memory (KB) {0}" -f $physicalMemoryUsageInKB;     
	Write-Host $strLog
	
	$strLog = "`tPhysical Memory (MB) {0}" -f $physicalMemoryUsageInMB;     
	Write-Host $strLog      
	
	Write-Host
        
}

[string] $typeSQLEngine = "SqlServer";
[string] $defaultInstance = "MSSQLServer";
[string] $machineName = $null;
[int]    $iNamedInstanceStartingPos = 6;

# Get machine name
$machineName = $env:computername


#Get a managed computer instance  
$mc = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer  

#List out all sql server instances running on this machine  
foreach ($item in $mc.Services)
{

	if ($item.Type -eq $typeSQLEngine)
	{
		
		if ($item.Name -eq $defaultInstance)
		{
			$instanceName = "DEFAULT";
		}
		else
		{
		
		    $instanceNameLen = ($item.Name).length - $iNamedInstanceStartingPos;
			
			$instanceName = ($item.Name).Substring( `
													  $iNamedInstanceStartingPos `
													, $instanceNameLen `
												  );
			
		}
		
		$path = "SQLSERVER:\SQL\" + $machineName + "\" + $instanceName;
		
		displayPath $path
					
	} # if ($item.Type -eq $typeSQLEngine)
	
} 

Output

 

Source Code Control

GitHub

DanielAdeniji/SQLServerPS

Link

SQL Server – Dynamic SQL Queries – Duplicate Plans ?

Background

In the last couple of weeks we have taken a sure aim at some of our dynamic queries.

This morning came in and started reviewing our cached plans and noticed that we might have duplicates.

 

Cached Plans

Image

Here is a slimmed down version of our cached Plans

Explanation

I was wondering why it seems I have duplicate entries filtering on timePeriod, stateLimit, and Phone.

The first entry has 2040 hits and the other 134 matches.

 

Comparison

SQL Server Management Studio ( SSMS )

I tried to compare the plans using SQL Server Management Studio v2017, but nothing jumped at me.

 

Dynamic Management Views

sys.dm_exec_plan_attributes

I also looked at the normal causes such as:

  1. userid
    • attribute user_id
  2. Set Options
    • Attribute set_options
      • Quoted Identifier
      • Ansi Nulls

 

Text File Comparison

I then saved the query plans into text files and used an online text comparison tool.

DiffNow

DiffNow is here.

From the few differences between the files, here is why my eyes finally rested.

Image

 

Explanation

  1. Saw that @stateLimit only appears in the second plan

 

In Code

Output

Using XQuery was able to confirm that different queries is indeed attributable to slight variation in filtering columns.

Parameter List

Sample 1

 

Sample 2

 

Take away

Thankfully considering text file comparison got me closer to identifying the causation of the “duplicate” plans.

PowerShell – Environment Customization – Prompt

Background

By default, when one initiates Powershell, the environment prompt takes upon the current folder.

Default Prompt

Customization

Outline

  1. Determine the name of our Powershell Profile file
  2. Edit existing file or create a new one
    • Add function called prompt

Get Profile file

Syntax

powershell -Command "Write-Host('Profile file is {0}', $profile);"

Output

Textual


C:\Users\dadeniji\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1

 

Edit Profile file

Sample


function prompt
{
	[string] $prompt;
	
	$prompt = ">";
	
	return($prompt);
	
}

Validate

In a new command shell, launch powershell…

And, irrespective of our current folder, we will be greeted with “PS>”.

References

  1. Microsoft
    • Microsoft Technet
      • Scripting Guy
        • Change the Powershell Prompt
          Link
  2. Robert Sheldon
    • ITProToday
      • PowerShell Basics: Console Configuration
        How to customize the PowerShell console
        Link

Powershell Modules – AzureAD – “Get AD Policy”

Background

Using the “AzureAD” Powershell module, let us review how Azure-AD User Policy is configured.

PowerShell Modules

Currently, there are two versions of the Azure AD Powershell module.

  1. AzureAD ( Release Module )
  2. AzureADPreview ( Preview Module )

The functionality we need is only available in the Preview Module.

Installation

Review

Let us review the version we have installed.

Syntax


powershell -Command "Get-Module -ListAvailable" -Name "*Azure*"

Output

AzureAD

AzureADPreview

Explanation

Please review the following Columns

  1. Module
    • AzureAD ( released version )
    • AzureADPreview ( yet to be released )

Upgrade

Let us upgrade our install.

Syntax


Powershell -Command "UnInstall-Module AzureAD"
Powershell -Command "Install-Module AzureADPreview"

Code

Outline

  1. To connect to our “Tenants Domain“, Issue “Connect-AzureAD
  2. To get AzureAD policies, call Get-AzureADPolicy
    • Get the returned object type by issuing GetType().Fullname

 

API

Get-AzureADPolicy

Let us make have a proper insight on how to invoke the Get-AzureADPolicy.

Get-Help

Syntax

Get-Help Get-AzureADPolicy

Output

Explanation

We can see that Get-AzureADPolicy really accepts only a singular argument.

It is an optional one, Id.

 

Get-Help -detailed

Syntax

Get-Help Get-AzureADPolicy -detailed

Output

Explanation

When we pass in “-detailed” we see that the ID is the Policy ID.

Get-Help -example

Syntax

Get-Help Get-AzureADPolicy -examples

Output

Examples

Unfortunately, there is nothing here per examples.

Actual Code


Set-StrictMode -Version Latest;

[string]$CHAR_NEWLINE ="`r`n";

function listObjectProperties($object)
{
 
    [int] $iPropertyIndex = 0;
 
    #prepare formatting
    $strFormat = "{0}) Name :- {1} - Value :- {2}"
 
    #Iterate Object Properties
    Foreach ($objProperty in $object)
    {
 
       # increment property counter
       $iPropertyIndex = $iPropertyIndex + 1;
 
       $objPropertyName = $objProperty.Name;
 
       #place variable name in single quotes to ensure that
       #PowerShell does not evaluate\substite value
       $objPropertyNameFull = '$object' + '.' + $objPropertyName

	   <# # dadeniji 2018-01-22 10:30 AM #commented out and replaced with "$($objProperty.Value) # prepare to use variable substitution # Invoke-Expression # http://technet.microsoft.com/en-us/library/dd347550.aspx # $objPropertyValue = invoke-expression $objPropertyNameFull; #>	
	   
	   #$objPropertyValue = invoke-expression $objPropertyNameFull;
	   
	   $objPropertyValue = "$($objProperty.Value)"
 
       #format data
       $strLog = [String]::Format(
                                         $strFormat
                                       , $iPropertyIndex
                                       , $objPropertyName
                                       , $objPropertyValue
                                  );
 
       # display data
       $strLog;
 
     }
 
}

try
{

	$objCredential = Get-Credential -ErrorAction SilentlyContinue

}
catch
{

     $strLog = "get-Credential failed!";
	 
	 $strLog = $strLog + $CHAR_NEWLINE + $_.Exception.Message
	 
	 Write-Host $strLog -ForegroundColor red 
     
	 return

}
	
if (!$objCredential)
{
     
	 $strLog = "get-Credential failed!";

	 Write-Host $strLog -ForegroundColor red;
	 
     return
	 
}

#Connect to Azure AD
try
{

	$connect = Connect-AzureAD  -Credential $objCredential -ErrorAction SilentlyContinue
}
catch
{

     $strLog = "Connect-AzureAD failed!";
	 
	 $strLog = $strLog + $CHAR_NEWLINE + $_.Exception.Message
	 
	 Write-Host $strLog -ForegroundColor red 
     
	 return

}

if (!$connect)
{

     $strLog = "Connect-AzureAD failed!";
	 
	 Write-Host $strLog -ForegroundColor red 
     
	 return

 }

 
#Get-AzureADPolicy
try
{

	 
	# get list of AD Policy
	#$objListofADPolicy = Get-AzureADPolicy  -ErrorAction SilentlyContinue
	$objListofADPolicy = Get-AzureADPolicy

}
catch
{

     $strLog = "Get-AzureADPolicy failed!";
	 
	 $strLog = $strLog + $CHAR_NEWLINE + $_.Exception.Message
	 
	 Write-Host $strLog -ForegroundColor red 
     
	 return

}

# if List is empty, then say so
if (!$objListofADPolicy)
{
	 
     $strLog = "Get-AzureADPolicy failed!";
 
	 Write-Host $strLog -ForegroundColor red 
     
	 return
}
 
# Keith Hill - Get Type name
# the-typename-and-inheritance-chain/
# http://rkeithhill.wordpress.com/2007/10/28/powershell-quicktip-using-pstypenames-to-see-# 
$strLog = "Type name is " + $objListofADPolicy.GetType().Fullname;
$strLog
 
# Get top item in list
$objADPolicy = $objListofADPolicy | Select-Object -first 1

# if List is empty, then say so
if (!objADPolicy)
{

     $strLog = "Object (objADPolicy) is null (empty)";
	 
	 Write-Host $strLog -ForegroundColor red 
 
     return

 }
 
#Show all of Object's properties 
#https://www.codykonior.com/2013/03/26/powershell-how-to-show-all-of-an-objects-properties-and-values/
$objADPolicyPropList = $objADPolicy | Select-Object -Property *

if (!$objADPolicyPropList)
{
	 
     $strLog = "Object has no properties";
	 
	 Write-Host $strLog -ForegroundColor red 

	 return
}

$objADPolicyPropList


# get Object Properties
#   By calling PsObject.Properties
$objADPolicyProps = $objADPolicy.PsObject.Properties;

#list properties
listObjectProperties($objADPolicyProps);

Source Code

GitHub

DanielAdeniji/Office365AzureADPowerShell
Link

Specifically Office365AzureADPolicy.ps1.

Summary

There are so many ways to go wrong on this one.

Inclusive are:

  1. Installation
    • Having the right PowerShell Module
      • AzureAD or AzureADPreview
  2. Security
    • Having enough security on your Tenant Account
      • If one does not
        • Errors not returned when one issues Get-AzureADPolicy
        • The only indicator is that null is returned

References

  1. Microsoft
    • Microsoft Azure
      • Azure / Azure PowerShell
        • Azure Active Directory PowerShell for Graph
          Link
        • Get-AzureADPolicy
          Link
    • Microsoft – Docs
      • Office 365 Enterprise > Manage Office 365 with Office 365 PowerShell > Getting started with Office 365 PowerShell > Connect to Office 365 PowerShell
        • Connect to Office 365 PowerShell
          Link
    • Tech Community
      • Home > Azure Active Directory > Azure Active Directory
        • Azure AD PowerShell v2 cmdlets not working, e.g. Get-AzureADPolicy
          Link

Transact SQL – XQuery – Error – “XQuery [query()]: Attribute may not appear outside of an element”

Background

Stole one of Bob Beauchemin’s Code and had to understand it.

 

  1. Author :- Bob Beauchemin
  2. Topic :- Move over developers! SQL Server XQuery is actually a DBA tool
  3. Link :- Link
  4. What does the code do
    • Looks for queries that have operators bearing a specific operator

 

Enhancement Envisioned

  1. Include the operator node in the list of projected records

Error

Textual


Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 30 [Batch Start Line 14]
XQuery [query()]: Attribute may not appear outside of an element


Image

 

Versions of SQL Server

  1. sql Server Version :- Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) – 13.0.4435.0 (X64)
    Apr 27 2017 17:36:12
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

Code

Original Code

Here is the original Code


use master
go

if object_id('[dbo].[sp_LookForPhysicalOps.Original]') is null
begin


    exec('CREATE PROCEDURE [dbo].[sp_LookForPhysicalOps.Original] as ')

end
go

ALTER PROCEDURE [dbo].[sp_LookForPhysicalOps.Original]
(
      @op VARCHAR(30)
    , @maxNumberofRecords int = null
)
AS
begin

    /*
        Author :- Bob Beauchemin
        Topic  :- Move over developers! SQL Server XQuery is actually a DBA tool
        Link   :-	https://www.sqlskills.com/blogs/bobb/move-over-developers-sql-server-xquery-is-actually-a-dba-tool/

    */

    SELECT 
            TOP
            (
                case
                    when  @maxNumberofRecords is  null then 10000
                    when  @maxNumberofRecords = 0 then 10000
                    else @maxNumberofRecords
                end
            )

            sql.text
            , qs.EXECUTION_COUNT
            , qs.*
            , p.* 
            --, relOp.node.query('.')

    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
    CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
    CROSS APPLY p.query_plan.nodes
				('
					declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
					/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
				'
				) relOp(node)
    WHERE query_plan.exist('
                            declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
                           ') = 1



end

GO



Revised Code

Here is the revised code – version 1

dbo.sp_LookForPhysicalOps.Revised01



use master
go

if object_id('[dbo].[sp_LookForPhysicalOps.Revised01]') is null
begin


    exec('CREATE PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised01] as ')

end
go

print 'sql Server Version :- ' + @@VERSION
go

ALTER PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised01]
(
      @op VARCHAR(30)
    , @maxNumberofRecords int = null
)
AS
begin

    /*
        Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 24 [Batch Start Line 11]
        XQuery [query()]: Attribute may not appear outside of an element
    */

    ; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT 
            TOP
            (
                case
                    when  @maxNumberofRecords is  null then 10000
                    when  @maxNumberofRecords = 0 then 10000
                    else @maxNumberofRecords
                end
            )

            sql.text
            , qs.EXECUTION_COUNT
            , qs.*
            , p.* 
            , relOp.node.query('.')

    FROM sys.dm_exec_query_stats AS qs 

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql

    CROSS APPLY sys.dm_exec_query_plan(plan_handle) p

    /*
        Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 32 [Batch Start Line 11]
        XQuery [query()]: Attribute may not appear outside of an element

    */
    CROSS APPLY p.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]') relOp(node)

    WHERE query_plan.exist('
                            declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
                           ') = 1



end

GO



Compile Error


Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 30 [Batch Start Line 14]
XQuery [query()]: Attribute may not appear outside of an element


dbo.sp_LookForPhysicalOps.Revised02

Overview

  1. Added
  2. Modifies
    • Split into nodes
      • Original
        • CROSS APPLY p.query_plan.nodes(‘/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable(“@op”)]’) relOp(node)
      • Revised
        • CROSS APPLY p.query_plan.nodes(‘//RelOp[@PhysicalOp = sql:variable(“@op”)]’) relOp(node)
      • Explanation
        • Please properly indicate attribute name
          • Change from /@PhysicalOp[.
          • To [@PhysicalOp

Code


use master
go

if object_id('[dbo].[sp_LookForPhysicalOps.Revised02]') is null
begin


    exec('CREATE PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised02] as ')

end
go

ALTER PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised02]
(
      @op VARCHAR(30)
    , @maxNumberofRecords int = null
)
AS
begin

    ; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT 
                TOP 
                (
                    case
                        when  @maxNumberofRecords is  null then 10000
                        when  @maxNumberofRecords = 0 then 10000
                        else @maxNumberofRecords
                    end
                )

              [sql].[text]

            , [executionCount]
				= qs.execution_count

            , qp.query_plan

			, [nodeID] 
				= relOp.node.value
					(
						  '@NodeId'
						, 'integer'
					)

            , [node] 
				= relOp.node.query('.')

    FROM sys.dm_exec_query_stats AS qs 

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql

    CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp

    CROSS APPLY qp.query_plan.nodes
				(
					'//RelOp[@PhysicalOp = sql:variable("@op")]'
				) relOp([node])


    WHERE qp.query_plan.exist
		  ('
            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
          ') = 1

	order by
		qs.execution_count desc

end

GO


Invoke



declare @op sysname
declare @maxNumberofRecords int = null

set @op = 'Hash Match'

set  @maxNumberofRecords = 5

EXECUTE [dbo].[sp_LookForPhysicalOps.Revised02]
          @op =  @op
        , @maxNumberofRecords = @maxNumberofRecords



Output

 

Summary

And so this works


    WHERE qp.query_plan.exist
		  ('
            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
          ') = 1

 

but, this does not


    CROSS APPLY p.query_plan.nodes
			(
				'/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]'
			) relOp([node])

 

The period (/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable(“@op”) ) works on exist, but not on the cross apply ( /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. ).

Addendum

Addendum – 2018-04-09

The code above has a lot of deficiencies.

Kept the deficiencies has I did want to deviate too much from my young desire to address an error message.

SQLServer – Xquery – Using local-name

Background

As always too little time to study, but I need to get a node’s name.

And, everything points at using local-name.

 

What is local-name?

I like mozilla.org’s  definition & example.  It is here:

Definition

The local name of a node is that part of the node’s qualified name that comes after the colon. Qualified names are typically used in XML as part of the namespace(s) of the particular XML documents.

Sample

For example, in the qualified name ecomm:partners, partners is the local name and ecomm is the prefix:

 

Xml

Body

Here is a sample of the XML that we will be working with.

<hub>
  
<country>
United States

  <state>California
	  <city population="4 million">Los Angeles</city>
  </state>


  <state>Illinois
	  <city population="2.7 million">Chicago</city>
  </state>

  <state>New York
	  <city population="8.6 million">New York City</city>
  </state>


  <state>Texas
  <city population="2.3 million">Houston</city>
  <city population="1.3 million">Dallas</city>
  <city population="950 thousand">Austin</city>
  </state>


 </country>
  
<country>
Canada
  <province>Ontario
  <city population="2.8 million">Toronto</city>
  </province>

  <province>British Columbia
  <city population="2.5 million">Vancouver</city>
  </province>


 </country>
  
</hub>

SQL

Query



declare @xmlHub xml
  
set @xmlHub =
'<hub>
  
<country>
United States

  <state>California
      <city population=''4 million''>Los Angeles</city>
  </state>


  <state>Illinois
      <city population=''2.7 million''>Chicago</city>
  </state>

  <state>New York
      <city population=''8.6 million''>New York City</city>
  </state>


  <state>Texas
  <city population=''2.3 million''>Houston</city>
  <city population=''1.3 million''>Dallas</city>
  <city population=''950 thousand''>Austin</city>
  </state>


 </country>
  
<country>
Canada
  <province>Ontario
  <city population=''2.8 million''>Toronto</city>
  </province>

  <province>British Columbia
  <city population=''2.5 million''>Vancouver</city>
  </province>


 </country>
  
</hub>
'
  
--select [hubList] = @xmlHub
  
select
  
     [country]
        =
           (
                r.[node].value
                (
                   'text()[1]'
  
                    , 'varchar(80)'
                )
  
            )


    , [stateOrProvince]
        =
           (
                r2.[node2].value
                (
                   'text()[1]'
  
                    , 'varchar(80)'
                )
  
            )

/*
    , [stateOrProvinceAlt]
        =
           (
                r3.[node3].value
                (
                   '../text()[1]'
  
                    , 'varchar(80)'
                )
  
            )

*/
  
    , [IsStateOrProvince?]
            =  (
                    cast
                    (
  
                        (
  
                            r2.[node2].query
                                (
                                'local-name(.)'
                                )
  
                        )
  
						as varchar(80)
					)	
  
				)

    , [IsStateOrProvinceAlt?]
            =  (
                    cast
                    (
  
                        (
  
                            r3.[node3].query
                                (
                                'local-name(..)'
                                )
  
                        )
  
                    as varchar(80)
                )
  
            )

  
    , [city]
        =
           (
                r3.[node3].value
                (
                   'text()[1]'
  
                    , 'varchar(80)'
                )
  
            )
 
    , [population]
        =
           (
                r3.[node3].value
                (
                   '@population'
  
                    , 'varchar(80)'
                )
  
            )

--from @xmlHub.nodes('/hub/country') as r(node)
from @xmlHub.nodes('//country') as r(node)


/*
	Msg 9335, Level 16, State 1, Line 138
	XQuery [r.node.nodes()]: The XQuery syntax 'union' is not supported.
*/
--cross apply r.node.nodes('(state | region)') as r2(node2)


--cross apply r.node.nodes('*') as r2(node2)
cross apply r.node.nodes('(state, province)') as r2(node2)


--cross apply r2.node2.nodes('*/city') as r3(node3)
cross apply r2.node2.nodes('city') as r3(node3)

order by
      [country]
    , [stateOrProvince]
    , [city]


 

Output

 

Explanation

  1. XML
    • XML Data in SQL
      • Population
        • Population is represented as a string ( 5 million, 2.3 million, etc ) and we have to escape using two quotes
  2. SQL Query
    • Shred XML
      • We use the nodes method a couple of times
        • Country
          • The first nodes method splits our XML into Country fragments
            • The Xquery passed to the nodes method is
              • //Country
              • /hub/country will work just as well
            • The result of the shredding is projected out as Row r and column n
        • State or Region
          • The second nodes method splits the Country fragment into City or Region
            • Here are sample XQueries
              • (state, province)
                • Means we can parse on State or Province
              • *
                • Place holder for any node
        •  City
          • The third nodes method splits the result of the second node into a city fragments
            • The XQuery passed in is
              • city
    • Projected Columns
      • Country
        • Here are sample clause to extract the Country
          • r.[node].value(‘text()[1]’, ‘varchar(80)’ )
      • City
        • Sample clauses
          • r2.[node2].value(‘text()[1]‘, ‘varchar(80)’)
            • Uses second node()’s
          • r3.[node3].value(‘../text()[1]‘, ‘varchar(80)’ )
            • Uses third node()’s parent
      • Is State or Province
        • Sample Clause
          • cast( ( r2.[node2].query( ‘local-name(.)’ )) as varchar(80))
          • cast( ( r3.[node3].query( ‘local-name(..)’ )) as varchar(80))
      • City
        • Sample Clause
          • r3.[node3].value( ‘text()[1]‘ , ‘varchar(80)’ )
      • Population
        • Sample Clause
          • r3.[node3].value( ‘@population‘ , ‘varchar(80)’ )

References

  1. StackOverflow
    • What is the difference between name() and local-name()?
      Link
  2. Mozilla.Org
    • MDN web docs
      • Node.localName
        Link
  3. Microsoft
    • Docs / SQL / T-SQL / Data types / XML
      • Nodes method
        • Nodes() Method
          Link
      • value() method
        Link
  4. Codegumbo
    • SQL Server XQuery: .value() method
      Link
    • SQL Server XQuery: .query() method
      Link
  5. Priscilla Walmsley
    • Definitive XML Schema 2e
      XQuery
      Priscilla Walmsley (pwalmsley@datypic.com)
      ISBN: 1491915102
      2nd edition, 2015, O’Reilly Media, Inc.
    • Chapters

Powershell – Dynamically List Properties & Property values

Background

Back in Dec 2011, I stitched together a script to dynamically display property names and values.

Today I found it is no longer working.

Btw, here is the original post.

Script

Original

Code


Set-StrictMode -Version Latest;

function listObjectProperties($object)
{

    [int] $iPropertyIndex = 0;

    #prepare formatting
    $strFormat = "{0}) Name :- {1} - Value :- {2}"

    # get Object Properties
    $objProperties = $object.PsObject.Properties;

    #Iterate Object Properties
    Foreach ($objProperty in $objProperties)
    {

       # increment property counter
       $iPropertyIndex = $iPropertyIndex + 1;

       $objPropertyName = $objProperty.Name;

       #place variable name in single quotes to ensure that
       #PowerShell does not evaluate\substite value
       $objPropertyNameFull = '$object' + '.' + $objPropertyName

       #prepare to use variable substitution
       # Invoke-Expression
       # http://technet.microsoft.com/en-us/library/dd347550.aspx

       $objPropertyValue = invoke-expression $objPropertyNameFull;

       #format data
       $strLog = [String]::Format(
                                         $strFormat
                                       , $iPropertyIndex
                                       , $objPropertyName
                                       , $objPropertyValue
                                  );

       # display data
       $strLog;

     }

}

#PowerShell 2.0 Commands
#http://ss64.com/ps/
Clear-Host;

# instantiate object
[Datetime] $object = Get-Date;

if (!$object)
{
     "Object is null (empty)"
     return
}

# Keith Hill - Get Type name
# http://rkeithhill.wordpress.com/2007/10/28/powershell-quicktip-using-pstypenames-to-see-#
# the-typename-and-inheritance-chain/
$strLog = "Type name is " + $object.GetType().Fullname;
$strLog

# get Object Properties
$objProperties = $object.PsObject.Properties;

if (!$objProperties)
{
     "Object has no properties"
     return
}

#list properties
listObjectProperties $object

Output

 

Explanation

In the original example, I passed in a date object and things worked well.

Revised

Objective

Today tried passing in the results of get-azureaduser, but it was failing terrible.

Error

Image

Image #1

Image #2

 

Textual

Textual #1


The property 'ExtensionProperty' cannot be found on this object. Verify that the property exists.
At line:1 char:1
+ $object.ExtensionProperty
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], PropertyNotFoundException
    + FullyQualifiedErrorId : PropertyNotFoundStrict

1) Name :- ExtensionProperty - Value :-
The property 'DeletionTimestamp' cannot be found on this object. Verify that the property exists.
At line:1 char:1
+ $object.DeletionTimestamp
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], PropertyNotFoundException
    + FullyQualifiedErrorId : PropertyNotFoundStrict

2) Name :- DeletionTimestamp - Value :-
The property 'ObjectId' cannot be found on this object. Verify that the property exists.
At line:1 char:1
+ $object.ObjectId
+ ~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], PropertyNotFoundException
    + FullyQualifiedErrorId : PropertyNotFoundStrict

3) Name :- ObjectId - Value :-
The property 'ObjectType' cannot be found on this object. Verify that the property exists.
At line:1 char:1
+ $object.ObjectType
+ ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], PropertyNotFoundException
    + FullyQualifiedErrorId : PropertyNotFoundStrict

4) Name :- ObjectType - Value :-
The property 'AccountEnabled' cannot be found on this object. Verify that the property exists.
At line:1 char:1
-- More  --

Textual #2


The property 'TelephoneNumber' cannot be found on this object. Verify that the property exists.
At line:1 char:1
+ $object.TelephoneNumber
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], PropertyNotFoundException
+ FullyQualifiedErrorId : PropertyNotFoundStrict

The property 'UsageLocation' cannot be found on this object. Verify that the property exists.
At line:1 char:1
+ $object.UsageLocation
+ ~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], PropertyNotFoundException
+ FullyQualifiedErrorId : PropertyNotFoundStrict

The property 'UserPrincipalName' cannot be found on this object. Verify that the property exists.
At line:1 char:1
+ $object.UserPrincipalName
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], PropertyNotFoundException
+ FullyQualifiedErrorId : PropertyNotFoundStrict

The property 'UserType' cannot be found on this object. Verify that the property exists.
At line:1 char:1
+ $object.UserType
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], PropertyNotFoundException
+ FullyQualifiedErrorId : PropertyNotFoundStrict

Remediation

Summary

Replace


$objPropertyValue = invoke-expression $objPropertyNameFull;

with


$objPropertyValue = "$($objProperty.Value)"

 

Code


function listObjectProperties($object)
{

    [int] $iPropertyIndex = 0;

    #prepare formatting
    $strFormat = "{0}) Name :- {1} - Value :- {2}"

    #Iterate Object Properties
    Foreach ($objProperty in $object)
    {

       # increment property counter
       $iPropertyIndex = $iPropertyIndex + 1;

       $objPropertyName = $objProperty.Name;

       #place variable name in single quotes to ensure that
       #PowerShell does not evaluate\substite value
       $objPropertyNameFull = '$object' + '.' + $objPropertyName

	   	

	   #$objPropertyValue = invoke-expression $objPropertyNameFull;

	   $objPropertyValue = "$($objProperty.Value)"

       #format data
       $strLog = [String]::Format(
                                         $strFormat
                                       , $iPropertyIndex
                                       , $objPropertyName
                                       , $objPropertyValue
                                  );

       # display data
       $strLog;

     }

}

 

Source Code

Github

DanielAdeniji/PowerShellDynamicallyListProperties
Link

 

Summary

Wish I could tell you what went wrong.

But, hopefully if you are just as perplexed as I found myself, google on some terms and you will find this post, or a more able one.