Searching for Installed Windows Update With PowerShell

Yesterdays Post Show-WindowsUpdatesLocal does enable you to search for an installed update as follows

Show-WindowsUpdatesLocal|Where-Object {$_.HotFixID -eq ‘KB2855336’} |Select Date, HotfixID, Result,Title|Format-Table –AutoSize

image

I thought I would be able to do it quicker especially if I was searching a server with a lot of updates so I thought I would create a function to answer theΒ  question Is this update installed on that server

It is very similar to Show-WindowsUpdatesLocal but does not include the Title or Description on the grounds that if you are searching for it you should know those!!

It also only adds the output to the collection if the KB is in the HotFixID property as shown below

image

If we use Measure-Command to compare the two we can see

image

image

From 3.89 seconds on my poor overworked machine to 1.79 seconds πŸ™‚

You can find the code here

 

Advertisements

Show Windows Updates Locally With PowerShell

I wanted to be able to quickly show the Windows Updates on a server. This came about during a discussion about auditing.

Of course, there is no point in re-inventing the wheel so I had a quick Google andΒ  found a couple of posts on fromΒ  Hey Scripting Guy blog and one from Tim Minter. Neither quite did what I wanted so I modified them as follows.

We start by creating a Update object and find the total number of updates and setting them to a variable $History which we pass to the QueryHistory Method. This enables us to show all the updates

image

Passing this to Get-Member shows

image

which doesn’t show the KB so I read a bit more and found Tom Arbuthnot’s Blog Post

image

this transforms the ResultCode Property to something meaningful and places the KB in its own column.

I have created a function called Show-WindowsUpdatesLocal It’s Local because doing it for a remote server takes a different approach but I will show that another day.

This means you can call the function and use the results however you like

Show-WindowsUpdatesLocal

image

Show-WindowsUpdatesLocal| Select Date, HotfixID, Result|Format-Table -AutoSize

image

Show-WindowsUpdatesLocal|Where-Object {$_.Result -eq ‘Failed’} |Select Date, HotfixID, Result,Title|Format-Table -AutoSize

image

Output to file Show-WindowsUpdatesLocal|Format-Table -AutoSize|Out-File c:\temp\updates.txt

image

Output to CSV Show-WindowsUpdatesLocal|Export-Csv c:\temp\updates.csv

image

You can get the code here

 

Finding Text In All Files In A Folder With PowerShell

Whilst writing my PowerShell Box of Tricks GUI I realised that I had hard-coded the path to the sqlservers.txt file in several functions and I wanted one place where I could set this. At the top of the GUI script I added a variable and in the ReadMe explained this needed to be set but I needed to change it in all of the functions where it was referenced.

The Hey Scripting Guy Blog came to the rescue

image

Only four entries so i di them manually but You can also use PowerShell to replace the entries.

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