If you have data that you’re collecting with PowerShell and wan’t to insert new entries or update existing ones in a Database then a SQL MERGE function is what you need. I frequently run scheduled queries to collect information that I want to submit to SQL for reporting purposes. When testing this function I was able to merge a DataTable with 100,000+ rows and 20+ columns very quickly. A unique identifier in the results is used as a primary key in the merger. When there’s a match it updates that row, if it doesn’t exist already it inserts it as a new row. To do this effectively a temporary table is created by first looking up the primary key in the targeted table (if there’s one); then builds the query with all the columns in the source. It also compares the source columns to the target table columns to make sure they correspond for the column mapping. The DataTable is then bulk copied to the temporary table, this becomes the source of the merge to the target table. After which the temporary table is dropped.
Being able to quickly submit data from PowerShell to SQL is something I often do. Usually I want the results inserted into a SQL table. Being able to do so in bulk without needing a for each insert loop is very helpful and much faster. The easiest way to do this is to use sqlclient.sqlbulkcopy. It requires that the input variable be a DataTable object so if it’s not already use the ConvertTo-DataTable function I wrote earlier. Once you have your data in the DataTable you can use it as the source in this Copy-DataTableToSQL function I wrote.
For many reasons you may want to retrieve data from SQL to be used in PowerShell. Maybe it’s data from a service’s database or data that was stored in SQL from another script. I wrote this function to quickly get a SQL table into a PowerShell DataTable object. It’s provides parameters for setting the target server, database, table, and where statement. Timeout defaults to 60 seconds for the SQL client connection but can be overridden. Security defaults to SSPI, but can be set, and I didn’t add any credentials arguments as the intent is that the script is run as a user with the appropriate permissions to select data from the database. In the case of Microsoft SQL the dbreader role at a minimum is required assigned to your Windows credentials.
There’s many ways to store data in PowerShell but I find DataTables to be the most useful. The extra capabilities available are very flexible. I’ve made this function to quickly convert arrays, hashes, and other objects to DataTables quickly. I’ll often use this in combination with SQL connections to complete bulk copies of entire tables in one command, or even user that in combination with SQL merge statements to insert and update matched records. Continue reading
This PowerShell module I’ve developed to collect the software installed on all Windows servers within a forest. Requirements: PowerShell v2+, Remote Server Admin Tools (script uses AD cmdlets), & remote registry enable/permissions. Feel free to share this script but please provide credit if you do. Download the script here
It works by first getting a list of all Windows Servers in the AD forest then collecting registry entries from each of those servers and putting that into a table of data. It’s looking for the keys created by the Windows Installer/uninstaller (therefore this doesn’t list features/roles/standalone apps). It also can provide an error report which will tell you the severs that were successfully queried, failed to ping, or failed remote registry connection.
There are 2 functions in this module; Get-InstalledSoftware-Threaded & Get-InstalledSoftware.
By default these look for all Windows severs in the forest that the script is run in; you can limit the scope to a specific domain or to the name of severs matching your criteria. The threaded function allows parallel collection up to the thread limit (defaults to 1). So if you specify 50 threads and there 2000+ servers in the forest then 50 servers will be queued at the same time and as they finish new threads (powershell.exe instances) are generated until all severs are collected. When I tested it against 2000+ servers using 50 threads it ran in about 1.5Hrs with a server that had 2 cores and 8GB RAM. If you have a lot of server’s you want to get, use the threaded function. If you only have a few, use the regular function. Continue reading