The PowerShell Box Of Tricks GUI

When I started as a DBA at MyWork I faced a challenge. Many hundreds of databases, dozens of servers and no idea what was on where. It was remembering this situation when new team members were appointed that lead me to write the Find-Database script and I had written a simple GUI using Read-Host to enable the newbies to see the functions I had created

image

Whilst writing this series of posts I decided that I would create a new GUI

image

I wanted the choice to be made and then the form to close so I had to use a separate function for calling all the functions referenced in the form. This function takes an input $x and depending on the value runs a particular code block. Inside the code block I ask some questions using Read-Host to set the variables, load the function and run it as shown below for Show-DriveSizes

image

Then I set about creating the GUI. First we load the Forms Assembly, create a new Form object and add a title

image

Then using the details found here I I converted the image to ASCI and use it as the background image and set the size of the Form

image

I choose a default font for the form. Note there are many many properties that you can set for all of these objects so use your best learning aid and find the ones you need.

image

I then create three labels. I will show one. I think the code is self-explanatory and you will be able to see what is going on. Don’t forget to the last line though! That adds it to the form, if you miss it you can spend a few minutes scratching your head wondering why it hasn’t appeared!!!

image

We need a Text Box for the User to put their choice in. Again the code is fairly easy to understand

image

The next bit of code enables the user to use Enter and Escape keys to Go or to Quit. Notice that both call the Close() method to close the Form and return to the PowerShell console

image

Add a button for OK and one for quit

image

and finally Activate the Form, Show it and run the function to call the correct function

image

The Return-Answer function simply calls the Return-Function function. I am not sure if that is the best way of doing it but it works in the way i wanted it to

image

Add User to SQL Server Database Role with PowerShell and Quickly Creating Test Users

There is a newer up to date version of this post here using the dbatools module and the sqlserver module

 

But if you want to continue with this way read on!!

Having created Windows Users or SQL Users using the last two days posts, today we shall add them to a role on a database.

As I discussed previously I believe that to follow good practice I try to ensure that database permissions are granted by role membership and each role is created with the minimum amount of permissions required for successful execution of the task involved.

So with each database having the correct roles created and the users created we just need to add the user to the database and to the role. This is easily done with PowerShell.

image

The Add-UserToRole function takes four parameters Server,Database,User and Role and does a series of error checks.

With these functions you can easily create a number of Users and add them to database roles quickly and easily and repeatedly.

If the test team come to you and require 10 Test Users and 3 Test Administrators adding to the test database. I create 2 notepad files

image  image

and use them with the Add-SQLAccountToSQLRole and Add-UserToRole functions to create the users

image

Here are the results in PowerShell

image

and in SSMS

image

The Code is here

 

Creating a Windows User and adding to a SQL Server Role with PowerShell

Another post in the PowerShell Box of Tricks series.

In a previous post Checking SQL Server User Role Membership we showed how to check which roles users were added to. This function allows you to add Windows Users to Server Roles. A nice simple function which can easily be piped into to allow users to be added form a list in a text file, csv file or even from Active Directory. This makes it easy to recreate Dev and Test environments and can be added to Disaster Recovery processes.

We create a Login Object, set the Logintype and create it with the Create Method. It is then added to the Role specified.

image

The function does some simple error checking. If the login already exists on the server it will just add it to the role and if the role has been mistyped it will let you know. It does this by checking if the Role object is Null for the Roles and the Contains Method for the Logins

image

The function is called as follows. To just create a login I add the user to the public role

Add-WindowsAccountToSQLRole FADE2BLACK ‘FADE2BLACK\Test’ public

The code can be found here

 

Tomorrow we will create and add a SQL Authenticated User

Displaying the Windows Event Log with PowerShell

The latest post in the  PowerShell Box of tricks series is here.

I’ll start by saying this is a bit of a cheat. PowerShell has a perfectly good cmdlet called Get-EventLog and plenty of ways to use it

I created this function because I like to use Out-GridView and specify only a few records so it is quicker on remote systems. I like Out-GridView as it enables me to filter easily by typing in the top box. This is most often used via the simple GUI I have created. At the command line I would just use Get-EventLog

image

The function is shown below. It is important to know that the $log parameter is CaSeSensItive

image

The Code is here

 

Showing and Killing SQL Server Processes with PowerShell

Another post in the PowerShell Box of Tricks series. There are much better ways of doing this I admit but as you can do it with PowerShell I created a function to do it.

Create a Server Object and notice that there is a Method named EnumProcesses by piping it to Get-Member and then look at the Properties and Methods of EnumProcesses

image

Once I had done that then it was easy to create a function to display what is going on. It’s quick and easy. Not as good as sp_WhoIsActive but it displays about the same info as sp_who, sp_who2

image

image

You can also find a Method called KillProcess on the Server Property so I asked a Yes/No question using Windows Forms. You can find much more detail on that here

image

All you need to supply is the spid

The code is here Show-SQLProcesses

Show The Last Backups On A Server with PowerShell

Another day another PowerShell Box of Tricks post

Auditors, managers and bosses often want proof of following processes successfully so when they come knocking on my door(I don’t have a door, it’s usually my shoulder they knock) asking when the last backups were taken I either use this function or show them the excel file my automated process creates. This depends on if I think the pretty colours in the excel sheet will impress them!

The Show-LastServerBackup function iterates through each database on the server and takes each of the three properties mentioned in yesterdays post. However this time I created an empty hash table and added each result to it as follows

I created the hash table with @() and then assign each property to a variable inside the loop and add it to a temporary PSObject with some custom NoteProperties to fit the data

image

The last line adds the temporary object to the hash table. I then simply pipe the hash table to Format-Table to show the results

Call it like this

image

Ooops I haven’t backed up my system databases! Luckily it is my Azure server for blogging and presenting and is torn down and recreated whenever it is needed

You can get the code here

 

Checking For A Database Backup with PowerShell

Todays PowerShell Box of Tricks Post is about backups

I highly recommend you go and read bookmark and come straight back Stuart Moore’s series on Backups with Powershell The link takes you to the category showing all of the posts in the series

Whilst I have many other methods to inform me if backups fail, sometimes someone walks up to the desk ignores the headphones in my ears (They are bright yellow FFS), eyes fixed on the screen, face deep in concentration and asks me a question. It was for times like these that the functions from this series were written.

“When was this database last backed up?”

Auditors, managers, bosses, developers all can come and ask this question. I just turn to my PowerShell prompt type

image

and show him the screen. Job done (Sometimes)

As I mentioned previously there are 154 properties on the database object. Three of them are

LastBackupDate
LastDifferentialBackupDate
LastLogBackupDate

We simply call these and if the date reported is ’01 January 0001 00:00:00′ print NEVER

The Code is here

 

Searching the SQL Error Log with PowerShell

Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!

Yesterday we looked at Reading Todays SQL Error Log Today we are going to search all* of the SQL Error Logs. This is usually used by DBAs to troubleshoot issues

The SQL Server Error Logs (by default) are located in the folder Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and are named as ERRORLOG.n files. The most recent has no extension the rest 1 to 6.

Using PowerShell you can easily find the location of the SQL Error Log using the ErrorLogPath Property

image

You can also read it with PowerShell using the ReadErrorLog Method. This has the following properties LogDate, Processinfo and Text. You can easily filter by any of those with a bit of PowerShell 🙂

I have created a function which takes two parameters $SearchTerm and $SQLServer adds *’s to the Search Term to allow wildcards and searches each of the SQL Error Logs

image

Simply call it like this and use the results as needed

image

Of course, as the results are an object you can then carry on and do other things with them

image

The code can be found here

 

* Technically we are only searching the default number of 7 but if your environment is different you can easily add the lines to the function

Reading Todays SQL Error Log With PowerShell

Todays post from my PowerShell Box of Tricks series is about the SQL Error Log.

DBAs need to read the error log for many reasons and there are different ways to do it. sp_readerrorlog, xp_readerrorlog, using SSMS opening the file in notepad. I’m sure every DBA has their own favourite. This one is mine.Of course, it uses PowerShell

It is very simple as there is a method on the server property called ReadErrorLog.

In this function I read the latest Error Log and filter it for the last 24 hours using the Get-Date cmdlet and the AddDays Method

image

Here is the output

image

You can also save the output to a text file and open it by piping the function to Out-File

Show-LatestSQLErrorLog fade2black|Out-File -FilePath c:\temp\log.txt
c:\temp\log.txt

or send it by email

image

or as an attachment

image

PowerShell is cool.

The code can be found here Show-Last24HoursSQLErrorLog