There are a number of cmdlets in the SMLets project (http://smlets.codeplex.com) which retrieve data from Service Manager. To reduce the amount of data in getting simple instances from Service Manager, Get-SCSMObject provides a filter parameter which lets you provide a simple property/operator/value triad to reduce the amount of data that is retrieved from the CMDB. This is really helps performance because the filtering happens on the server. We can see the difference pretty easily:
PS# measure-command { get-scsmobject -Class $incidentclass | ?{ $_.Title -like "Ipsum*" } } Days : 0 Hours : 0 Minutes : 0 Seconds : 1 Milliseconds : 460 Ticks : 14609004 TotalDays : 1.69085694444444E-05 TotalHours : 0.000405805666666667 TotalMinutes : 0.02434834 TotalSeconds : 1.4609004 TotalMilliseconds : 1460.9004 PS# measure-command { get-scsmobject -Class $incidentclass -filter { Title -like "Ipsum*" } } Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 134 Ticks : 1341265 TotalDays : 1.5523900462963E-06 TotalHours : 3.72573611111111E-05 TotalMinutes : 0.00223544166666667 TotalSeconds : 0.1341265 TotalMilliseconds : 134.1265
In total time, this might not be impressive, but from a percentage perspective, it is. In this case, filtering on the server cuts the operation time by 90%, which is pretty substantial. However, simple instances are small potatoes in comparison to what we can save if we implement a filter on projection retrieval.
There is also a filter parameter on Get-SCSMObjectProjection, but it only allows you to filter against properties of the seed object, there’s no way to query the relationships in this filter. However, since much of the interesting information about a projection is the relationship data, so a simple filter isn’t as much help as it is for simple instances. Because I wanted to be sure that there was at least some way that you could query against the relationships, I included a criteria parameter which takes an ObjectProjectionCriteria, but left the creation of this criteria as “an exercise for the reader”. I’ve had a few requests for this, so I thought it would be good to build a way to easily create this criteria based on the projection. Behaviorally, I wanted to provide a similar experience to that of the filter’s property/operator/value trio, so the filter that I created for projections has the same basic shape, but the property part of the trio has a different look.
The property part of the filter is broken into 2 pieces, the relationship (as expressed in the alias) and the property on that relationship. If we look at the System.WorkItem.Incident.View.ProjectionType we see the following structure:
PS# get-scsmtypeprojection incident.view.projection ProjectionType: System.WorkItem.Incident.View.ProjectionType ProjectionSeed: System.WorkItem.Incident Components: Alias TargetType TargetEndPoint ----- ---------- --------------- AffectedUser System.User RequestedWorkItem AssignedUser System.User AssignedWorkItem
This projection has two components “AffectedUser” and “AssignedUser”. With this script, I can construct a filter like this:
AssignedUser.DisplayName = 'Joe User'
which will check the DisplayName property of the System.User object which is the end point of the relationship. I also wanted to support multiple queries, so I added support for -AND which allows you to create multiple property/operator/value statements.
The savings in retrieving projection data is substantial. Here’s a query which retrieves incidents which have a priority of 2 and have a related work-item which has a DisplayName which is equal to MA37. Filtering in the query is 200 times faster.
PS# measure-command { >> .\new-scsmProjectionCriteria.ps1 $ipfull.__Base -filter { >> priority = 2 -and RelatedWorkItems.DisplayName -eq "MA37" } -result >> } Days : 0 Hours : 0 Minutes : 0 Seconds : 0 Milliseconds : 625 Ticks : 6258242 TotalDays : 7.24333564814815E-06 TotalHours : 0.000173840055555556 TotalMinutes : 0.0104304033333333 TotalSeconds : 0.6258242 TotalMilliseconds : 625.8242
PS# measure-command { Get-SCSMObjectProjection -ProjectionObject $ipfull | >> ?{ $_.priority -eq "2" -and ($_.RelatesToWorkItem_ |?{$_.DisplayNAme -eq "MA37" })} } Days : 0 Hours : 0 Minutes : 2 Seconds : 5 Milliseconds : 888 Ticks : 1258883302 TotalDays : 0.0014570408587963 TotalHours : 0.0349689806111111 TotalMinutes : 2.09813883666667 TotalSeconds : 125.8883302
It should be no surprise that it’s much faster to return only the data that you want, because there’s so much less information that needs to be passed back from the CMDB. Also, during the first pipeline, the CPU utilization was quite high (ranging between 60-80%) where utilization was split between PowerShell (PowerShell does a lot of adaptation of the returned projection), the SQL server and the DataAccess Service.
Here are some of the filters that I tested against the System.WorkItem.Incident.ProjectionType projection:
'title -like "Ipsum*" -and CreatedByUser.DisplayName -like "D*"' 'title -like "Ipsum*" -and RelatedWorkItems.DisplayName -like "M*"' 'title -like "Ipsum*" -and RelatedWorkItems.DisplayName -eq "MA37"' 'RelatedWorkItems.DisplayName -eq "MA37"' 'priority = 2 -and RelatedWorkItems.DisplayName -eq "MA37"' 'priority -gt 1 -and RelatedWorkItems.DisplayName -eq "MA37"' 'priority -lt 3 -and RelatedWorkItems.DisplayName -eq "MA37"' 'priority -le 3 -and RelatedWorkItems.DisplayName -eq "MA37"' 'priority <= 3 -and RelatedWorkItems.DisplayName -eq "MA37"' 'priority -ne 3 -and RelatedWorkItems.DisplayName -eq "MA37"' 'priority -ne 3 -and RelatedWorkItems.DisplayName -notlike "MA3*"' 'priority -eq 3 -and Status -eq "Closed" -and RelatedWorkItems.DisplayName -notlike "MA3*"' 'priority -eq 3 -and AssignedUser.displayname -like "D*" -and Status -eq "Closed"
-and RelatedWorkItems.DisplayName -notlike "MA3*"'
Each time, the difference in time between client side and server side filtering is huge!
Here’s the script:
1: ### 2: ### filters have the form of:
3: ### [alias.]propertyname <operator> value 4: ### if there's no ".", then the assumption is that the
5: ### criteria is looking for the property of a seed 6: ### if there is a ".", then it's a property of a relationship
7: ### the relationship is described by the alias 8: ###
9: [CmdletBinding()] 10: param (
11: [parameter(Mandatory=$true,Position=0)] 12: $projection,
13: [parameter(Mandatory=$true,Position=1)][string]$filter, 14: [parameter()][switch]$results
15: ) 16:
17: # determine whether the property is an enumeration type 18: function Test-IsEnum
19: { 20: param ( $property )
21: if ( $property.SystemType.Name -eq "Enum" ) { return $true } 22: return $false
23: } 24: # Get the string which provides a reference in our criteria to the
25: # management pack which contains the element we're searching against 26: function Get-ReferenceString
27: { 28: param (
29: $ManagementPack, 30: [ref]$alias
31: ) 32: $alias.Value = $ManagementPack.Name.Replace(".","")
33: $refstring = '<Reference Id="{0}" PublicKeyToken="{1}" Version="{2}" Alias="{3}" />' 34: $refstring -f $ManagementPack.Name,$ManagementPack.KeyToken,$ManagementPack.Version,$Alias.Value
35: } 36:
37: # retrieve the property from the class 38: # we want to do this because we may get a property from the user which has the case
39: # incorrect, this allows us to match property names case insensitively 40: function Get-ClassProperty
41: { 42: param ( $Class, $propertyName )
43: $property = ($Class.GetProperties("Recursive")|?{$_.name -eq $propertyName}) 44: if ( ! $property ) { throw ("no such property '$propertyName' in " + $Class.Name) }
45: return $property 46: }
47: # in the case that the value that we got is applicable to an enum, look up the 48: # guid that is needed for the comparison and substitute that guid value
49: # replace the '*' with '%' which is needed by the criteria 50: function Get-ProperValue
51: { 52: param ( $Property, $value )
53: if ( Test-IsEnum $property ) 54: {
55: $value = get-scsmenumeration $property.type|?{$_.displayname -eq $value}|%{$_.id} 56: }
57: return $value -replace "\*","%" 58: }
59: # create the XML expression which describes the criteria 60: function Get-Expression
61: { 62: param (
63: $TypeProjection, 64: [Hashtable]$POV,
65: [ref]$neededReferences 66: )
67: $Property = $POV.Property 68: $Operator = $POV.Operator
69: $Value = $POV.Value 70: $ExpressionXML = @'
71: <Expression> 72: <SimpleExpression>
73: <ValueExpressionLeft><Property>{0}</Property></ValueExpressionLeft> 74: <Operator>{1}</Operator>
75: <ValueExpressionRight><Value>{2}</Value></ValueExpressionRight> 76: </SimpleExpression>
77: </Expression> 78: '@
79: [ref]$MPAlias = $null 80:
81: # a proper property reference in a projection criteria looks like this: 82: # <Property>
83: # $Context/Path[Relationship='CustomSystem_WorkItem_Library!System.WorkItemAffectedUser' 84: # TypeConstraint='CustomSystem_Library!System.User']/
85: # Property[Type='CustomSystem_Library!System.User']/FirstName$ 86: # </Property>
87: # we need to collect all the bits and do the same 88: # if the property has a "." in it, we will assume that this is the property
89: # of a relationship. Therefore, get the relationship and construct the 90: # appropriate string for the property access
91: # 92: # This routine only supports a single ".", anything more complicated and this will
93: # fail 94: if ( $property -match "\." )
95: { 96: $alias,$prop = $property -split "\."
97: $component = $projection.TypeProjection[$alias] 98: $references = @()
99: $NS = "Microsoft.EnterpriseManagement" 100: $ConfigNS = "${NS}.Configuration"
101: $ComponentType = "${ConfigNS}.ManagementPackTypeProjectionComponent" 102: if ( $component -isnot $ComponentType)
103: { 104: throw "'$alias' not found on projection"
105: } 106: $target = $component.TargetType
107: $references += Get-ReferenceString $target.GetManagementPack() $MPAlias 108: $TargetFQN = "{0}!{1}" -f $MPAlias.Value,$Target.Name
109: $property = Get-ClassProperty $target $prop 110: $value = Get-ProperValue $property $value
111: 112: $relationship = $component.Relationship
113: $references += Get-ReferenceString $relationship.GetManagementPack() $MPAlias 114: $relationshipFQN = "{0}!{1}" -f $MPAlias.Value,$relationship.name
115: 116: $PropString = '$Context/Path[Relationship=''{0}'' TypeConstraint=''{1}'']/Property[Type=''{1}'']/{2}$'
117: $XPATHSTR = $PropString -f $RelationshipFQN,$TargetFQN,$property.Name 118:
119: $Expression = $ExpressionXML -f $XPATHSTR,$QueryOperator,$value 120: $neededReferences.Value = $references | sort-object -uniq
121: return $Expression 122: }
123: else 124: {
125: $SeedClass = get-scsmclass -id $projection.TargetType.Id 126: $property = Get-ClassProperty $SeedClass $property
127: $value = Get-ProperValue $Property $value 128:
129: $SeedMP = $SeedClass.GetManagementPack() 130: $reference = Get-ReferenceString $SeedMP $MPAlias
131: $typeFQN = "{0}!{1}" -f $MPAlias.Value,$SeedClass.Name 132:
133: $PropString = '$Context/Property[Type=''{0}'']/{1}$' -f $typeFQN,$Property.Name 134: $Expression = $ExpressionXML -f $PropString,$Operator,$Value
135: $neededReferences.Value = $reference 136: return $Expression
137: } 138: }
139: 140: trap { $error[0];exit }
141: if ( $projection -is "psobject" ) 142: {
143: $projection = $projection.__base 144: }
145: $ProjectionType = "Microsoft.EnterpriseManagement.Configuration.ManagementPackTypeProjection" 146: if ( $projection -isnot $ProjectionType )
147: { 148: throw "$projection is not a projection and cannot be converted"
149: } 150: # right now, only AND is supported,
151: # eventually, OR will be supported 152: $GroupOperators = " -and "
153: # and the conversion to what is needed in the criteria 154: $OperatorConverter = @{
155: "=" = "Equal" 156: "-eq" = "Equal"
157: "!=" = "NotEqual" 158: "-ne" = "NotEqual"
159: "-like" = "Like" 160: "-notlike" = "NotLike"
161: "<" = "Less" 162: "-lt" = "Less"
163: ">" = "Greater" 164: "-gt" = "Greater"
165: ">=" = "GreaterEqual" 166: "-ge" = "GreaterEqual"
167: "<=" = "LessEqual" 168: "-le" = "LessEqual"
169: } 170: # a list of allowed operators, generated from the converter
171: $Operators = ($OperatorConverter.Keys |%{" $_ "}) -join "|" 172: # split the filter up based on the GroupOperator
173: $filters = @($filter.ToString() -split $GroupOperators | %{$_.trim()}) 174: # some variables that we will need
175: [ref]$neededrefs = $null 176: $Expressions = @()
177: $ReferenceStrings = @() 178: # loop through the filters and construct some XML which we will use
179: foreach ( $filterString in $filters) 180: {
181: # check to be sure we have a valid filter which includes 182: # a property, an operator and a value
183: $foundMatch = $filterString.toString() -match "(?<p>.*)(?<o>$operators)(?<v>.*)" 184: if ( ! $foundMatch )
185: { 186: throw "bad filter $filter"
187: } 188: # manipulate the found elements into a PropertyOperatorValue hashtable
189: # which we will use to encapsulate the filter 190: $Property = $matches['p'].Trim()
191: $Operator = $matches['o'].Trim() 192: $QueryOperator = $OperatorConverter[$Operator]
193: if ( ! $Operator ) { throw "Bad Operator '$Operator'" } 194: $Value = $matches['v'].Trim() -replace '"' -replace "'"
195: $POV = @{ 196: Property = $Property
197: Operator = $QueryOperator 198: Value = $Value
199: } 200: # now go get the expression that we need for the criteria
201: # pass the projection, the PropertyOperatorValue hashtable 202: # and the needed references (as a reference variable }
203: $expressions += get-expression $projection $POV $neededrefs 204: $neededRefs.Value | %{ $ReferenceStrings += $_ }
205: } 206: # now that we have looped through the filters, construct the XML
207: # which we need to call the ObjectProjectCriteria constructor 208: # start off with the start of the criteria XML
209: $CriteriaString = '<Criteria xmlns="http://Microsoft.EnterpriseManagement.Core.Criteria/">' 210: # now add the references that are needed in the criteria
211: $ReferenceStrings | sort -uniq | %{ $CriteriaString += "`n $_" } 212: # if we actually had multiple filters, add the
213: # <And> 214: if ( $Filters.count -gt 1 )
215: { 216: $CriteriaString += "`n<Expression>"
217: $CriteriaString += "`n <And>" 218: }
219: # now, for each of the expressions, add it to the criteria string 220: foreach($ex in $expressions ) { $CriteriaString += "`n $ex" }
221: # and in the case where we have filters that have and "-and", add the 222: # </And> to finish correctly
223: if ( $Filters.Count -gt 1) 224: {
225: $CriteriaString += "`n </And>" 226: $CriteriaString += "`n</Expression>"
227: } 228: $CriteriaString += "`n</Criteria>"
229: write-verbose $CriteriaString 230: # at this stage, the criteria XML should be complete, so we can create the
231: # criteria object 232: $CTYPE = "Microsoft.EnterpriseManagement.Common.ObjectProjectionCriteria"
233: 234: $criteriaobject = new-object $CTYPE $CriteriaString,$projection,$projection.ManagementGroup
235: if ( $criteriaObject -and $Results ) 236: {
237: get-scsmobjectprojection -criteria $criteriaobject 238: }
239: elseif ( $criteriaObject ) 240: {
241: $criteriaObject 242: }
243:
I added a Result parameter to the script which calls Get-SCSMObjectProjection, just for convenience. Eventually, I’ll add this logic into the filter parameter for the cmdlet, so it will be part of the cmdlet rather than this addition.
