While working through a challenge I stumbled through a few interesting items that are worth jotting down. I’ve used both of these constructs before but I had to re-search for some assistance. This created the realization that I need the answers closer at hand, so why not put something up on the SDM Blog.
The Challenge
One of the customers of SDM Software’s Group Policy Compliance Manager (GPCM) was asking about custom querying of the data in the database. GPCM, for those who are not aware, is a solution that collects Group Policy processing information from clients throughout your Active Directory. It allows an IT organization to see if their administrative intent is actually reflected as the configuration of the managed nodes out there in your enterprise.
An example of a scenario where a custom query could be helpful is this. You make a change to a GPO, imagine you introduce an updated script that executes a logon. Many computers/users in the organization have already processed the GPO with the initial version of the script. Now you want to find out which computers have not processed the GPO with the latest version of the script. This is a great example of something very common in GP. Make a change, prove the change made its way out to all of your managed nodes. The GPCM database has the version number of the GPO that the managed node has processed.
The real challenge is the fact that currently GPCM utilizes a SQLCE database. SQLCE is not accessed through PowerShell as elegantly as other versions of SQL. At least from my perspective. You want to navigate this store with PowerShell, and in a way that is easy, intuitive and surely reproducible.
The Learning
As I was pulling this response together I had to go look up a few items I have figured out in the past. When I have to re-search for a solution it always reminds me how important it is to store in some personal knowledge base, basic information, links, suggestions, hints etc. related to what was found. The two items worth discussing here are not a solution to the scenario presented in the Challenge above, but they are pointers to techniques that are important if you ever have to query a SQL CE instance and explore that data store.
SQL CE and PowerShell
So the process here is documented in a bunch of blog posts out there and all of the ones I have found have very similar approaches. The steps are like this;
- Load the assembly/library for SQL CE. Must have the binary available to make this connection
- Connect to the actual data source (Data Source = <path to file.sdf>)
- Create the actual SQLCEConnection object
- Create SQL Query
- Create a SQLCEDataAdapter
- Execute the Query
Simple, right! OK, there are elements in here that may not be familiar to a lot of Systems Administrators, but SQL folks and Devs are likely right there. But let’s dive a bit deeper. The assembly is called system.data.sqlServerce.dll it is in the install directory for SQL CE. The .sdf file is going to be wherever the product you are working with stores that file. For GPCM it is in \<Public documents>\SDM Software\GPCR\GroupPolicyComplianceReporter.sdf. For step #3 and #5 above (and #1) take a look at the example scripts on these blogs. Connect to SQL CE 4.0, or connect to SQL CE 3.5.
Working with the Query
One of the most interesting things about the SQL PowerShell module is the simplicity of plugging a query right into the Invoke-SQLCmd. This makes tweaking and troubleshooting your query really nice and easy. Unfortunately this is not available when accessing SQL CE, at least I haven’t figured it out yet!. It takes a bit of the interactive experience away. As you can see in the script above the query is in a construct called a ‘here-string’. A here string is a construct that look like (@” <bunch of stuff “@) Everything between ‘@”‘ and ‘”@’ is interpreted as you type it. White spaces and all are interpreted. Take a look at the following image.
If you ran this and then asked for the value of $test you would get…
What this does for this example is allow you to create your SQL Queries or Select Statements anyway you want. You just simply cut and paste into the here-string in your script and run. Typically when I am wallowing in a data base my SELECT statement doesn’t change as much as my WHERE or LIKE. So I tend to just ask for everything in a certain table and then add constraints. This technique is well worth playing with. There are clearly a ton of useful areas where here-strings can be helpful. This is just one example.
Enjoy
-Kevin