Well, it’s been a while since I posted hasn’t it. A lot has changed in the 5 years since I last posted. Back then I was working with defense contractors developing a lot of on-prem infrastructure tools with PowerShell. Moved into cloud computing and doing Serverless development and DevOps automation for Continuous Integration and Continuous Deployment using AWS services. This has revolutionized the ways in which I look at computing. In the process I’ve gone back and forth on what development environments and languages to work with. There’s just so many options. Since I was working with PowerShell and .NET I used PowerShell ISE and VisualStudio primarily. I moved from enterprise Windows environments to then using Linux/Unix first with traditional virtualization, then containerization, and functions. Further abstracting away from the physical systems to focus working with services and my code that runs it. What I really care about is if someone is making a request for information or submitting information how can I retrieve the data and present that information to them as quickly, reliably, and securely as possible.
In the future posts I’ll be comparing development environment and patterns for cloud native applications. As I’m utilizing AWS services this will be focused on their development tools. Continue reading
This function will list all connections to a Windows computer in PowerShell. While most admins are familiar with Netstat and unless you have Windows 8.1 or Server 2012 R2 there’s no PowerShell equivalent. There’s a .Net method that could be utilized but it doesn’t supply the process identifier (PID) to relate that connection the process using it. Alternatively you create a custom type that uses IP Helper that does have the PID but that’s unnecessarily complicated for what I want. Instead I opted for parsing Netstat’s string output into a format I could use. Additionally I added in DNS lookup for remote addresses, process details from Get-Process, and several parameters to get just what you want.
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