Press "Enter" to skip to content

Category: Powershell

Powershell Tools and Excel Tips

Jess Pomfret shares a few useful Powershell modules and follows up with tips for maximizing your Excel game:

Since I’ve written a lot about PowerShell previously, I wanted to highlight some other tools that I depend on. I’ve always been a fan of Excel, my personal life is full of spreadsheets – most decisions end with a spreadsheet (lucky for me, my wife is also a big fan of Excel!).  I often find myself copying data into Excel to keep track of work, or to quickly analyse data.  It’s also a great way of sharing data with a clear structure.  I’m also a big fan of shortcuts – so here’s a few I use often.

Jess also reminds me that it’s about time to tune up the bicycle…

Comments closed

Testing Power BI Report Server Datasources

Aaron Nelson has some cmdlets for us:

I finally did it. I created a function I’ve been wanting to be able to use for *years*. Test-RsRestItemDataSource is here.

I can’t tell you how many times I’ve started to work on a report I was told was working, only to find the connection info was invalid. This wastes valuable time, especially when you’ve already made changes to the report.

Other times, I’ve been asked to figure out why a bunch of subscriptions weren’t working, only to find out it was a simple connection issue. I’ve always wanted a simple PowerShell command to check the credentials of a bunch of reports before I touch anything.

Turns out, it wasn’t that hard to build at all. I only wish I had built it years ago.

Click through to see an example of this, as well as two more cmdlets.

Comments closed

Shuffling Excel Worksheets and Changing Tab Colors with Powershell

Mikey Bronowski continues a series on using Powershell to modify Excel files:

In this part, we will work on an existing workbook that already has worksheets. If you want to learn how to add new worksheets using the Add-Worksheet have a look at this blog post.

In case you haven’t noticed in the script above we used -MoveToStart switch, that means all the new worksheets were added at the beginning.

Read on for examples around moving sheets to the front or end, moving sheets before or after other sheets, and changing the colors of different tabs.

Comments closed

Getting SQL Agent Jobs and Job Steps

Anthony Nocentino takes the dbatools approach to a problem:

Recently I needed to take a look at all of the SQL Server Agent Jobs and their Jobs Steps for a customer. Specifically, I needed to review all of the Jobs and Job Steps for Ola Hallengren’s Maintenance Solution and look at the Backup, Index Maintenance and Integrity Jobs to ensure they’re configured properly and also account for any customizations and one-offs in the Job definitions. This customer has dozens of SQL Server instances and well, I wasn’t about to click through everything in SSMS…and writing this in TSQL would have been a good candidate for a Ph.D. dissertation. So let’s check out how I solved this problem using dbatools.

Click through for the script.

Comments closed

Powershell Editors and Environments

Greg Moore gives us an overview of the Powershell IDE landscape:

Before I go too deep into this article, I want to distinguish between editing a file and running it. I’m going to focus on editors here, but most development environments include a way to execute a PowerShell script or PowerShell commands. However, do not confuse the editor with the execution environment.

I used the Powershell ISE for a long while, but eventually stopped because its settings were just different enough from the shell’s settings that things which would work just fine in the ISE would fail when I set them up as automated tasks. I don’t remember what those things were, though, so further research may be required. Nowadays, I’ll use VS Code when I need a proper editor and just wing it on the shell for one-off stuff.

Comments closed

Searching through Powershell History

Jess Pomfret describes a helpful module:

I was listening to a podcast last week about PowerShell, when one of the hosts mentioned having to ‘up arrow’ back through your history to find a command you wanted to rerun.  This made me realise that I should write this quick post on using PSReadLine’s interactive search function.  This tip is a serious time saver and I rely on it heavily.

The great news is that if you are using Windows PowerShell on Windows 10 or if you’re using PowerShell 6+, PSReadLine is already installed and you can immediately start using this tip.  If you don’t have the module though, it’s easy enough to install from the PowerShell Gallery:   

Read on to learn how to use it.

Comments closed

Refreshing Power BI Report Server Reports

Aaron Nelson has some new cmdlets for us:

I just wanted to give everyone a heads-up that a new version of the ReportingServicesTools module went out last week, and it includes 3 new PowerShell functions for working with Power BI reports on a Power BI Report Server (PBIRS) instance.

You can now Create (New), Get, & Start a CacheRefreshPlan of a Power BI report deployed to a PBIRS instance.  For clarity, these only apply to reports using an Imported model, not those using Direct Query.

Click through for more detail.

Comments closed

Hiding Excel using Powershell

Mikey Bronowski shows how you can hide an Excel worksheet, as well as specific rows and columns, using Powershell:

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

MS Excel offers many different functionalities and one of them is making things to disappear like hiding worksheets or columns and rows, even cells.

Read on to see how.

Comments closed

Using Azure Cloud Shell

Joey D’Antoni shows off some of the benefits of using Azure Cloud Shell:

One of the challenges of being a consultant is having to work with a number of clients, and having different login credentials and accounts. In the early days of Azure, this was exceptionally painful, but over time the experience of using the portal with multiple identities and connecting to Azure tenants has gotten much easier. However, when writing PowerShell or Azure CLI code, switching accounts and contexts is slightly more painful. Also, when you are doing automation, you may be touching a lot of resources at one time, you want to be extra careful that you are in the right subscription and tenant.

Enter cloud shell.

Read on to see how to use it, get an idea of its cost, and see some of the benefits.

Comments closed