Properly Querying SCSM Using SMLets Get-SCSMObject cmdlet
Published Feb 15 2019 05:36 AM 4,002 Views
First published on TECHNET on Apr 04, 2011
Jim Truher and I have been seeing some usage of the SMLets Get-SCSMObject cmdlet lately that will cause really bad performance when run in a production environment.  Things will work fine in a dev/test environment where the quantity of data is small but as soon as you put it into a fully loaded production environment it will cause massive problems.  This same kind of bad programming could be done in .NET code, but I never see it happen there.  I think for some reason it is just easier to sort of not think through what is really happening when you are writing scripts with the all powerful pipe in PowerShell.

Here is the bad example:

$Resolved = Get-SCSMEnumeration IncidentStatusEnum.Resolved$

Get-SCSMObject –ClassName System.WorkItem.Incident$ | Where-Object{$_.Status -ne $Resolved}

Can you see where the bad part is?

The bad part here is that Get-SCSMObject in this case is going to get every incident in the database and bring it back to the management server (or wherever this command is being run).  Then Where-Object is going to loop through all of those comparing the Status property to see if it is Resolved.  This works great on a dev or test system that just has a few incidents in it.  In a production system with 150,000 incidents, this is going to be very slow and make the processor work very hard on the management server.

This really should be done by leveraging the power of SQL Server to just get the incidents we care about.  There is a –Filter parameter on the Get-SCSMObject cmdlet that can be used for these purposes.  Rewriting the above using –Filter would look like this:

$ResolvedId = (Get-SCSMEnumeration IncidentStatusEnum.Resolved$).Id

Get-SCSMObject –ClassName System.WorkItem.Incident$ –Filter “Status –ne $ResolvedId”

Not only is that less typing but it is also vastly better performing!

Right now the –Filter parameter only works with a single property as criteria.  In the short term, you can use –Criteria as a way to handle multiple properties as criteria.

Bad Example:

$Now = (get-date).addhours(4)

$Closed = Get-SCSMEnumeration IncidentStatusEnum.Closed$

$Resolved = Get-SCSMEnumeration IncidentStatusEnum.Resolved$

Get-SCSMObject -ClassName ClassExtension_5cf048c3_5db1_4873_afb6_f85a7bfc8d3e | Where-Object{$_.Status -ne $Closed -and $_.Status -ne $Resolved -and $_.TargetResolutionTime -lt $Now -and $_.TargetResolutionTime -ne $Null}

Good Example:

$Now = (get-date).addhours(4)

$CId = (Get-SCSMEnumeration IncidentStatusEnum.Closed$).id

$RId = (Get-SCSMEnumeration IncidentStatusEnum.Resolved$).id

$Class = ClassExtension_5cf048c3_5db1_4873_afb6_f85a7bfc8d3e

$cType = "Microsoft.EnterpriseManagement.Common.EnterpriseManagementObjectCriteria"

$cString = "Status != '$CId' and Status != '$RId' and TargetResolutionTime < '$Now' and TargetResolutionTime Is Not Null"

$crit = new-object $cType $cString,$Class

Get-SCSMObject -criteria $crit

That’s a little harder than we would like it to be though so Jim is working on an improvement to the –Filter for SMLets that will allow you to specify more advanced filter criteria something like this:

Get-SCSMObject -Class $c -Filter "FirstName -eq 'Travis' -and LastName = ‘Wright' -and homephone -like '467*'"

Similarly don't do this:

$class = get-scsmclass | where{$ -eq “system.workitem.changerequest”}

Do this instead:

$class = get-scsmclass -name system.workitem.changerequest$

Version history
Last update:
‎Mar 11 2019 08:42 AM
Updated by: