Collection Query Statement / Language

by jaybird at 2013-01-04 08:20:27

How might one view/list a collection’s existing query statement (as entered via the GUI)? Is this what is know as a collection’s "rules?"

For example, if I look at Properties > Query Statement Properties (Query Language) for "All Systems" from the GUI, I see: "select * from sms_r_system." But how would I show the same via the PS prompt?
by DonJ at 2013-01-08 08:20:47
Pre-SP1, you couldn’t, unless you manually queried the query definition from the database. Post-SP1, I don’t have an answer for you - I don’t yet have a system with SP1 installed, sorry.
by Lembasts at 2013-01-08 14:42:26
Since finding the code to do SQL queries, I now do loads of scripts accessing CM data directly from SQL.

Here is a script that will dump all the Query Expressions to a csv file - its pretty crude but something you can work on.
Replace SSSSSSSS with the name of the server running SQL and DDD with your site name.

function Get-DatabaseData {
param (
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
$adapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command
$dataset = New-Object -TypeName System.Data.DataSet
$adapter.Fill($dataset) | Out-Null
$ErrorActionPreference = ‘Stop’
$scriptpath = Split-Path -parent $MyInvocation.MyCommand.Definition
Write-Host "Script launched from $scriptpath" -back black -fore yellow
$querystring = @"

select col.Name,crq.QueryExpression
from v_CollectionRuleQuery AS crq
join v_Collection AS col
on crq.CollectionID = col.CollectionID

$sqlqr = Get-DatabaseData -query "$querystring" -connectionstring "Server=SSSSSSSS;Database=SMS_DDD;Trusted_Connection=True;"
write-host "$($sqlqr.count) Records extracted"

$sqlqr | Export-Csv ("$scriptpath\temp-sqlquery.csv") -NoTypeInformation
by jaybird at 2013-01-09 12:10:08
And now for my disclosure… I’m no programmer, so I am taking to PowerShell like I would have DOS. I find it easier to learn / understand "scripting" from the prompt.

Would you be able to condense this for use at the prompt? I see the core of your script, but I’m having diffulty meshing it together…

Get-DatabaseData -query "$querystring" -connectionstring "Server=SSSSSSSS;Database=SMS_DDD;Trusted_Connection=True;"

select col.Name,crq.QueryExpression
from v_CollectionRuleQuery AS crq
join v_Collection AS col
on crq.CollectionID = col.CollectionID
by DonJ at 2013-01-09 13:34:23
When you start working with databases, you’re going to have to move beyond the prompt and into more formal programming structures. Not everything can be condensed into a one-liner. That said, the Get-DatabaseData function David provided can be used as a one-liner.

Get-DatabaseData -query "SELECT * FROM WHATEVER" -connectionString "Put connection string here"

I provided similar functions in "Learn PowerShell Toolmaking in a Month of Lunches."
by Makovec at 2013-01-16 02:44:57

If you want to replicate this in PowerShell, you have to use WMI Query for that. What you see in Query in WQL language (a bit different from SQL). In general - what you see in Query Window can be copid to Get-WmiObject cmdlet. Let me show you with another class than SMS_R_System (which should run really LONG time as it contains all systems in your hierarchy).

I usually use it with SMS_Site class. Let’s imagine you saw this in All Systems: select * from sms_site

Then you can run following PowerShell command to receive the same result]Get-WmiObject -ComputerName serverName -Namespace root\sms\site_XXX -Query "select * from sms_site"[/powershell]

It will return your site list. You have to change serverName to your real server and XXX to current sitecode.

To simplify it a bit, I work on module for ConfigMgr. You can find actual version at more functions to come as I am now testing it with my colleagues. The function you should use is Get-ConfigMgrObject. Ping me if you’ll need some advice with that.