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:
This projection has two components “AffectedUser” and “AssignedUser”. With this script, I can construct a filter like this:
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.
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:
Each time, the difference in time between client side and server side filtering is huge!
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.
Time is an illusion. Lunchtime doubly so. 