Command Shell Examples
Useful SQL Queries
Command Shell: Where-Object vs. -Criteria - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

Command Shell: Where-Object vs. -Criteria

Command Shell: Where-Object vs. -Criteria

  • Comments 3
  • Likes

I’ve seen this question come up more than a few times in the forums and from customers, so I thought I’d do a quick post on when and why we should use –Criteria expression rather than a Where-Object clause in our Command Shell scripts.

First, the –Criteria option isn’t necessarily a PowerShell “thing”.  It’s a Command Shell “thing”.  We build –Criteria options into the SDK for Get cmdlet’s that could potentially return a very large recordset.  The reason we have the –Criteria expression option on certain Get cmdlet’s is to decrease the amount of processing required by the client and help overall performance of Command Shell scripts.

Using –Criteria

The Criteria option essentially tells the client to send the select query to the Operations Manager database with the where clause, and return a smaller recordset to the client that only matches our Criteria filter.

Get-Alert -Criteria 'ResolutionState = 0'

This is synonymous to T-SQL:

Select * From Alert Where ResolutionState = 0

Using Where-Object

The Where-Object clause in Command Shell tells the client to send the select query to the Operations Manager database and return all records, then the client will filter the recordset to display objects that match the Where-Object clause.

Get-Alert | Where-Object {$_.ResolutionState -eq 0}

This is synonymous to T-SQL:

Select * From Alert

The result

Even though both of these commands will return the same alerts, the client will receive, process and filter all alerts with the Where-Object method.  Whereas, using the –Criteria option, the client only receives the recordset that matches the criteria, so no filtering needs to be done at the client and the entire recordset returns immediately without further processing and filtering.

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments
  • I'm glad to see this Criteria option.  I hate pulling a whole recordset down when I only need a small set. I'm surprised this is not a default functionality. Why pull down 10,000 records to then filter with the where clause for 10 of them when you can request just those 10 in the first place.  

    Thanks for this post.

  • Hi Jonathan,

    Do you know if this works with "get-agent"?

  • Criteria expression isn't available on get-Agent.  You can see whether a cmdlet has the Criteria expression available by executing

    Get-Help [cmdlet] -Full

    Look at the Parameter section in the help details.