Automatically Declining Itanium Updates in WSUS using PowerShell

Working with WSUS, I sometimes find myself declining the exact same type of updates each month after Patch Tuesday. Not surprisingly, a common trend of those updates involve having the word ‘’Itanium” somewhere in the update title.  Instead of having to mess with clicking through the UI and finding all of these annoying updates, and as there are no automated declining rules available in WSUS, I think a more automated approach is necessary using the APIs and PowerShell.

Honestly, there really isn’t a whole lot of code required to make this happen. The key piece is that you need to have the WSUS Administrator console installed (for Win2K8R2 and below) or the UpdateServices module available (Win2K12 and above).

I will break down the code below and at the very end have all of it available to copy to create. My idea behind this is to just make a scheduled job that can be used to run every second Wednesday (after Patch Tuesday) to automatically decline all of these unneeded updates.

Param (
    [string]$UpdateServer = 'DC1',
    [int]$Port = 80,
    [bool]$Secure = $False
)

This is my main parameters that are configurable based on your environment. Here I decide server I will connect to, the port that is open on the server and if the connection is secure.

If (-Not (Import-Module UpdateServices -PassThru)) {
    Add-Type -Path `
"$Env:ProgramFiles\Update Services\Api\Microsoft.UpdateServices.Administration.dll" -PassThru
} 

A check is made for the UpdateServices module by actually attempting to import the module. I call –Passthru so an object is outputted which can then be used in the If statement. If no object is outputted, then it is assumed that the module doesn’t exist, the OS doesn’t support the module, etc… and then attempts to load the assembly from a known good location.

$Wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::`
GetUpdateServer($UpdateServer,$Secure,$Port)

Finally, a connection is made to the WSUS server using the parameters specified.

image

$approveState = 'Microsoft.UpdateServices.Administration.ApprovedStates' -as [type]
 
$updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope -Property @{
    TextIncludes = 'itanium'
    ApprovedStates = $approveState::NotApproved,
    $approveState::LatestRevisionApproved,
    $approveState::HasStaleUpdateApprovals
}

image

Here I begin to piece together the filter that will be used with the query for updates. I create an object that holds the ApprovedStates enumeration which will make it easier to find what I can use with the creation of the UpdateScope object. Speaking of the UpdateScope object, this is the filtering piece that I will include with my query. I make sure that I specify itanium as for the TextIncludes propery as well as the ApprovedStates. Because I want to make sure that all non-approved and all non-declined updates have been found, I add the necessary ApprovedStates to the object.

$wsus.GetUpdates($updateScope) | ForEach {
    Write-Verbose ("Declining {0}" -f $_.Title) -Verbose
    $_.Decline()
}

image

Lastly, I can now finally begin declining all of these updates that have been found by my query. Fortunately, all I need to do is call the Decline() method associated with each update object.

You can download the source code for this below and modify it to suit your needs before deploying it in your environment as a scheduled job!

In my case, I will use PowerShell to create my scheduled job.

$triggerParam = @{
    At = "12:00 AM"
    DaysOfWeek = 'Wednesday'
    WeeksInterval = 1
    Weekly = $True
}
$trigger = New-JobTrigger @triggerParam
$JobParam = @{
    Trigger = $trigger
    FilePath = 'C:\DeclineItanium.ps1'
    Name = 'DeclineItaniumUpdates'
}
Register-ScheduledJob @JobParam

image

image

Source Code

Param (
    [string]$UpdateServer = 'DC1',
    [int]$Port = 80,
    [bool]$Secure = $False
)

If (-Not (Import-Module UpdateServices -PassThru)) {
    Add-Type -Path "$Env:ProgramFiles\Update Services\Api\Microsoft.UpdateServices.Administration.dll" -PassThru
} 

$Wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::`
GetUpdateServer($UpdateServer,$Secure,$Port)
 
$approveState = 'Microsoft.UpdateServices.Administration.ApprovedStates' -as [type]
 
$updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope -Property @{
    TextIncludes = 'itanium'
    ApprovedStates = $approveState::NotApproved,
    $approveState::LatestRevisionApproved,
    $approveState::HasStaleUpdateApprovals
}
 
$wsus.GetUpdates($updateScope) | ForEach {
    Write-Verbose ("Declining {0}" -f $_.Title) -Verbose
    $_.Decline()
}
 
Posted in powershell, WSUS | Tagged , , | 9 Comments

Restore a SQL Database Using SQL Server Management Objects (SMO) and PowerShell

At some point in your career with SQL, there may come a time to perform a restore of the database (and maybe transaction logs depending on your situation). Given that we covered how to back up a SQL database, we now need to take a look at performing a restore of the database to recover data which was deleted due to some craziness with a sysadmin.

What you will see in the following code examples will be some familiar things that I used when it came to performing a backup of the SQL database. So don’t be surprised if you see something and are thinking that this looks just like what I did with a backup!

So with that, lets load up those assemblies!

#region SQL Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, 
Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Smo, 
Version=10.0.0.0, Culture=neutral,PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, 
Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, 
Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, 
Version=10.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
#endregion SQL Assemblies

Now we can connect to a SQL server and verify the connection is good by checking out the object and ensuring it actually has data in it.

#SQL Connection
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server `
-ArgumentList 'boe-pc\shire'
$sqlServer

image

I also want to make it easier to get some specific types that will be used with the restores.

#Frequently used types
$Action = 'Microsoft.SqlServer.Management.Smo.RestoreActionType' -as [type]
$DeviceType = 'Microsoft.SqlServer.Management.Smo.DeviceType' -as [type]

The next step is to determine the backup directory on the SQL server. This way I have a good idea as to where the files are at.

#Find backup directory
$backupDir = $sqlServer.BackupDirectory

image

Perfect, now we can see what exists in the backup directory and can use one of those files in the restore process.

image

Let’s find a table to ‘accidently’ wipe out. This one (Store) looks good to me.

$sqlServer.Databases['AdventureWorks'].Tables | Where {
    $_.Name -eq 'Store'
} | Format-Table Name, ID,State -AutoSize

image

Now time to kiss it goodbye!

($sqlServer.Databases['AdventureWorks'].Tables | Where {
    $_.Name -eq 'Store'
}).Drop()

image

I actually had issues dropping this due to a Foreign Key Constraints on this table. In order to accomplish this, I had to run the following TSQL statement:

ALTER TABLE Sales.Customer DROP CONSTRAINT FK_Customer_Store_StoreID

Double check that the table has been removed.

image

Now lets get the full paths to each of the backup files.

$DBBackup = Get-Item ("{0}\AdventureWorks_20131009.bak" -f $backupDir)
$TLogBackup = Get-Item ("{0}\AdventureWorks_20131009.trn" -f $backupDir)

Note: If running this against a remote SQL server, you will first need the UNC path to actually get the files, but then you must revert back to a local path as the restore process will throw an error if specifying UNC paths.

Now I will create a SMO.Restore object and reference some extra properties that will be used in the restoral of the database.

$restore = New-Object Microsoft.SQLServer.Management.Smo.Restore -Property @{
    Action = $Action::Database
    Database = 'AdventureWorks'
    ReplaceDatabase = $True
    NoRecovery = $False
}

 

Notice in this case that I set NoRecovery to $False. This means that after I restore this database file, it will be ready for use and will not allow me to restore the transaction log backup.

Like before with the backup, I have to created a device object that will be used as part of the restoral process.

$Device = New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $DBBackup.fullname,'File'
$restore.Devices.Add($Device)

Now it is time for the restore. I actually had an error occur during my restore attempt stating that the database was still in use. To get around this, I made sure to kill all processes to that database.

$sqlServer.KillAllProcesses('AdventureWorks')

And now I can make the restore happen!

$restore.SQLRestore($sqlServer)

I need to refresh the list of tables and then I can verify if the Store table exists again.

$sqlServer.Databases['AdventureWorks'].Tables.Refresh()
$sqlServer.Databases['AdventureWorks'].Tables | Where {
    $_.Name -eq 'DatabaseLog'
} | Format-Table Name, ID,State -AutoSize

image

Now I will repeat this process to restore the database but instead set the NoRecovery property to $True to allow the restoral of the transaction log backup.

#Find backup directory
$backupDir = $sqlServer.BackupDirectory
# If working remotely, get UNC path to locate file; but then revert back to local path as errors may occur during restore!
$DBBackup = Get-Item ("{0}\AdventureWorks_20131009.bak" -f $backupDir)
$TLogBackup = Get-Item ("{0}\AdventureWorks_20131009_tlog.trn" -f $backupDir)

## Database Restore
#Create the Restore object
# http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx
$restore = New-Object Microsoft.SQLServer.Management.Smo.Restore -Property @{
    Action = $Action::Database
    Database = 'AdventureWorks'
    ReplaceDatabase = $True
    NoRecovery = $True
}

#Create the Device that will be used for Restore
# http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backupdeviceitem.aspx
$Device = New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $DBBackup.fullname,'File'
$restore.Devices.Add($Device)
$restore.SQLRestore($sqlServer)

Now the AdventureWorks database is in a ‘Restoring’ State. In order to get an accurate representation of the database, I use the following line of code to refresh the object.

$sqlServer.Databases['AdventureWorks'].Refresh()

image

image

This is where we can now proceed with the transaction log restore and making sure that the NoRecovery property is set to $False.

## T-Log Restore
$restore = New-Object Microsoft.SQLServer.Management.Smo.Restore -Property @{
    Action = $Action::Log
    Database = 'AdventureWorks'
    ReplaceDatabase = $True
    NoRecovery = $False
}

#Create the Device that will be used for Restore
$Device = New-Object Microsoft.SQLServer.Management.Smo.BackupDeviceItem -ArgumentList $TLogBackup.fullname,$DeviceType::File
$restore.Devices.Add($Device)
$restore.SQLRestore($sqlServer)

And the database is now back and available to use!

image

image

There you have it! I was able to do a restore of my database as well as performing a restore on both a database and its transaction log backup without much issue at all!

Posted in powershell, SQL | Tagged , , , , | 1 Comment

PowerShell 4.0 Now Available for Download

That’s right, Windows Management Framework 4.0 is now available to download for down-level OS’s. Some important things to know regarding this:

New Features and Updated Versions

  • Windows PowerShell
  • Windows PowerShell Integrated Scripting Environment (ISE)
  • Windows PowerShell Web Services (Management OData IIS Extension)
  • Windows Remote Management (WinRM)
  • Windows Management Infrastructure (WMI)
  • Windows PowerShell Desired State Configuration (DSC)

Supported Down-level Operating Systems (Already available with Windows 8/8.1 and Windows Server 2012 R2)

  • Windows 7 SP1
  • Windows 7 Embedded
  • Windows 2008 R2 SP1
  • Windows Server 2012

Unsupported Platforms (Installing on these platforms could cause serious issues)

  • System Center 2012 Configuration Manager (not including SP1)
  • System Center Virtual Machine Manager 2008 R2 (including SP1)
  • Microsoft Exchange Server 2013, Microsoft Exchange Server 2010 and Microsoft Exchange Server 2007
  • Microsoft SharePoint 2013 and Microsoft SharePoint 2010
  • Windows Small Business Server 2011Standard

.Net 4.5 Needs to be installed!!!!

There are issues that if you do not have .Net 4.5 installed on your system and attempt to load WMF 4.0, it will silently fail and will still continue to load 3.0 instead (you can verify by running $PSVersionTable to see.

Download Here

http://go.microsoft.com/fwlink/?LinkId=293881

Enjoy!

Posted in News, powershell | Tagged , | Leave a comment

Using the ESPN API with PowerShell

Being a sports fan, I wanted to see what the ESPN API (just started in March 2012) had to offer (not much unfortunately unless you are a premium member) and make use of what PowerShell has to offer to pull back some data from those APIs. Using what useful APIs are available, I will show you how you can simply pull some data from ESPN and return it into a UI that you can then select from and open via your default web browser.

Get Your API!

The ESPN API is only available to those who have an API key available. First go out to http://developer.espn.com/ and register for the API.

After a minute or so you should have access to your API key.

image

Now we can start playing around with what we can actually query.

I mentioned before the lack of things you can query for because this is just for public use. Unless you have bigger aspirations, this is as good as it is going to get. You will be able to locate specific IDs for teams, athletes, etc… but will not be able to generate rosters, stats or anything else of the sort.

You can find the possible things to query here: http://developer.espn.com/docs and dig deeper into each area to actually see what methods are usable as well as the URI Parameters.

You can even build out your own web request string here: http://developer.espn.com/io-docs which will allow you to add your own parameters and then just copy that string and use it in your own request!

Moving on from that, I am going to take a look at the headlines from the day in the Green Bay Packers. But before I can do that, I need to figure out what their team ID is. So with that I will perform a query to get all of the NFL teams IDs.

#First figure out the team IDs
$team = Invoke-RestMethod `
'http://api.espn.com/v1/sports/football/nfl/teams/?apikey=<insert your own API>'

#Create a hash table to associate ID with Team
$teams = @{}
$team.sports.leagues.teams | ForEach {
    $teams[$_.Name] = $_.id
}

#Look for the Green Bay Packers
$teams['Packers']

image

image

Knowing that the team ID for the Packers is 9, I can now look at their latest headlines.

$packers = Invoke-RestMethod `
'http://api.espn.com/v1/now/?leagues=nfl&teams=9&apikey='<insert api>'
$packers.feed | ForEach {
    [pscustomobject]@{
        Headline = $_.headline
        Description = $_.description
        Modified = [datetime]$_.lastModified
        Type = $_.type
        Source = $_.source
        Link = $_.links.web.href
    }
} | Out-GridView -PassThru | ForEach {Start-Process $_.link}

 

I make use of Out-Gridview with the –PassThru parameter which will allow me to select one item from the UI and then output the object into the pipeline which then will open of the link in my default browser.

image

image

I have to use a ForEach because Start-Process doesn’t know how to handle the pipeline input of the Link property of the PSObject custom object.

The last thing is that I want to check out the current 25 news events going on in the NFL using the same approach as before.

#Get the latest news across the NFL
$news = Invoke-RestMethod `
'http://api.espn.com/v1/sports/football/nfl/news/headlines/top/?insider=no&limit=25&apikey=<insert api>'
$news.headlines | ForEach {
    [pscustomobject]@{
        Headline = $_.headline
        Description = $_.description
        Modified = [datetime]$_.lastModified
        Type = $_.type
        Source = $_.source
        Link = $_.links.web.href
    }
} | Out-GridView -PassThru | ForEach {Start-Process $_.link}

Same output as before with the UI made from Out-GridView and then opening up the same site (if I chose something to view).

image

So that is really that for working with the ESPN API unless you have a better account. For what its worth, you at least get some access to the headlines so you could have your own feed going. But all of the stuff that I really would like such as teams and accounts is just not available.

Posted in powershell | Tagged , , | 1 Comment

Guest Post on Hey, Scripting Guy! Talking PInvoke and PowerShell

Today my Hey, Scripting Guy! guest blog goes live. I am talking about using Pinvoke with PowerShell to handle the removal of files which may be locked by a process.

Check it out and let me know what you think!

Weekend Scripter: Use PowerShell and Pinvoke to Remove Stubborn Files

Posted in News, powershell | Tagged , , , | Leave a comment