Press "Enter" to skip to content

Category: Powershell

Filtering Event Log Entries

Published 2016-12-13 by Kevin Feasel

Kevin Hill gets into Powershell:

What follows is a step by step of how I expanded on the most basic command to get more and more out of it. I spent a lot of time on MSDN looking up cmdlets, and on Stack Overflow twice with basic questions. Even went to my “DBA” Twitter feed early in the process using #sqlhelp since a lot of my colleagues are veteran PoSH users.

Warning…its very possible I’ve gained a syntax error here and there when copying and formatting this into a post…run at your own peril 😉

One bit I’d recommend is using Out-GridView for occasional display and potentially getting rid of the Excel requirement.

1 Comment

Analysis Services Powershell

Published 2016-12-13 by Kevin Feasel

Aaron Nelson is advocating improvements to Powershell cmdlets around Analysis Services:

Frequently when developing updates to an SSAS cube I want to deploy my schema and process the dimension. Sometimes several of dimensions process successfully and then fails on one. At this point I go and correct the error, deploy the new schema, and then I only want to process all of my dimensions except the dimensions which did process successfully the first time. Sometimes this is really easy, but if you have a large number of dimensions this can become cumbersome since the only way to know which dimensions had been processed successfully or to right-click each dimension one at a time and find out, or to have memorized which dimensions had processed successfully on the earlier attempt. There can be a better way, and of course, PowerShell is one of those options. J The only problem is that as things currently stand, PowerShell is not as easy as it could be; the Invoke-ProcessDimension cmdlet doesn’t accept [direct] pipeline input. What is one to do when PowerShell isn’t as easy as it could be? File a Connect item of course!

Check out the Trello board.  It’s been instrumental in helping Microsoft developers get the leverage they need to dedicate time to improving particular aspects of the product.

Comments closed

Switching In Powershell

Published 2016-12-08 by Kevin Feasel

Chrissy LeMaire explain the switch command in Powershell:

Even less code and makes total sense. Awesome. There’s even more to switch — the evaluations can get full on complex, so long as the evaluation ultimately equals $true. Take this example from sevecek. Well, his example with Klaas’ enhancement.

The refrain with switch is, always make sure you cover every case and don’t let cases fall through when you don’t intend them to.  Fortunately, Powershell doesn’t allow fallthrough, so that makes it easier.

Comments closed

Lists And Ranges In Powershell And T-SQL

Published 2016-12-05 by Kevin Feasel

Phil Factor has an interesting post on lists and ranges in two languages:

When a  list like ‘1,3,5,6,9’, or ’12 Jan 2016,14 Jan 2016, 18 Feb 2016’  contains a datatype that can be unambiguously sorted in the order of the values of the datatype, it becomes possible to imply a range. This will trim unwieldy lists significantly if they have a lot of contiguous values.  ‘1,2,3,4,5’ can be expressed as 1-5 (or 1..5). The starting integer is separated from the end integer in the range by a dash sign. This representation rather clashes with the minus sign, represented by the same symbol, but the comma removes the ambiguity. A number followed immediately by a ‘-‘ means that the ‘-‘is a range symbol.  As with SQL’s BETWEEN clause that selects rows, the range includes all the integers in the interval including both endpoints. Because the range syntax is intended to be a more compact form,  it is generally only used where there are at least three contiguous values.

Interesting article.  I recommend checking it out.

Comments closed

Powershell Runspaces

Published 2016-12-05 by Kevin Feasel

Chrissy LeMaire has a pair of posts on runspaces.  First, a post which includes a runspace template:

Runspaces can be intimidating. I had heard about them, took a look at the code and was like “Ah, that looks complex. I’ll learn that later.” Because of this, I repeatedly went over the bulk insert code to familiarize the audience with the functionality that I was eventually going to multi-thread.

Then she shows us how to output results without waiting for all runspaces to complete:

Recently, I had a request to add multi-threading to Read-DbaBackupHeader. This was the first runspace in which I had to output the results from the runspace to the host — usually I just needed commands to run in the background, like with bulk-inserts to SQL Server.

Runspaces seems to be a good option in Powershell for multithreading.

Comments closed

Powershell Remoting To Linux

Published 2016-12-05 by Kevin Feasel

Max Trinidad shows that Powershell remoting works for connecting Linux to Windows and vice versa:

Connecting Linux to Windows

This is strange but, more likely, you will get an error the first time you try to connect. Just try the second try and it will work.

I need to see what Powershell objects for Linux currently exist; my guess is “not many, if any” but as those start getting fleshed out, I think even the most adamant of grep-sed-awk users will want to pick up at least a little bit of Powershell.

Comments closed

Comparing Active Directory Membership

Published 2016-12-01 by Kevin Feasel

Jana Sattainathan shows how to use Powershell to compare Active Directory group membership for two users:

Before we begin, if you are running Windows 7 or if you do not have the Active Directory module installed, please do so first by downloading and installing “Remote Server Administration Tools”. I personally cannot live without this module.

Let us call the new employee as NEW_USER and the existing employee as OLD_USER. Here is how to use PowerShell to do the comparison.

The retrieval and comparison code is pretty easy; click through to see.

Comments closed

Powershell And Python

Published 2016-11-29 by Kevin Feasel

Max Trinidad mixes two powerful scripting languages:

For this section we previously installed the python module pyodbc which is needed to connect via ODBC to any SQL Server on the network giving the proper authentication method.

The following sample code can be found this link: https://www.microsoft.com/en-us/sql-server/developer-get-started/python-ubuntu

This is probably more useful in larger shops with multiple operations personnel covering different domains, but it’s nice to know that both languages play nice.

Comments closed

Finding Relational Data Sources In SSAS

Published 2016-11-29 by Kevin Feasel

Jens Vestergaard builds a Powershell script to figure out which relational database servers feed data to which Analysis Services cubes:

Whenever you are introduced to a new environment, either because you visit a new client or take over a new position from someone else, it’s always crucial to get on top of what’s going on. More often than not, any documentation (if you are lucky to even get hands on that) is out of date or not properly maintained. So going through that may even end up making you even more confused – or in worst case; misinformed.

In a previous engagement of mine came a request from the Data Architecture team. I was asked to produce a list of all servers and cubes running in a specific environment. They provided the list of servers and wanted to know which servers were hit by running solutions. Along with this information the team also needed all sorts of information on the connection strings from the Data Source Views, as well as which credentials were used, if possible.

If you’re dealing with a large number of cubes, this becomes even more useful.

Comments closed

Buffer Pool TreeMap

Published 2016-11-23 by Kevin Feasel

Aaron Nelson has a post on using Powershell to visualize contents in the buffer pool:

On Monday Chrissy LeMaire & I Did a session called “SQL Server Cmdlets and Community Involvement” for the PowerShell 10 Year Anniversary all-day event on Channel9 on MSDN. If you jump to the 18 minutes 30 second mark of that video you’ll see me showing how to look at the Buffer Pool of your SQL Server instance, first with the Out-GridView cmdlet, then I used a function from PowerShell MVP Boe Prox ( b | t ) called Out-SquarifiedTreeMap like so:

Read on to get a link to the code.

Comments closed