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?"by DonJ at 2013-01-08 08:20:47
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?
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.by jaybird at 2013-01-09 12:10:08
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 {
[CmdletBinding()]
param (
[string]$connectionString,
[string]$query
)
$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
$dataset.Tables[0]
}
$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
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.by DonJ at 2013-01-09 13:34:23
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
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.by Makovec at 2013-01-16 02:44:57
Get-DatabaseData -query "SELECT * FROM WHATEVER" -connectionString "Put connection string here"
I provided similar functions in "Learn PowerShell Toolmaking in a Month of Lunches."
Hi,
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 https://bitbucket.org/makovec/configmgr-module 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.
David