Collection Query Statement / Language

Welcome Forums General PowerShell Q&A Collection Query Statement / Language

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 7 years ago.

  • Author
  • #6571

    Points: 0
    Rank: Member

    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.


The topic ‘Collection Query Statement / Language’ is closed to new replies.