Using SQL and Powershell to set Extended Properties

I searched around for a bit and didn’t find this anywhere easy so I banged it out myself.  Hope this helps somebody to set an extended property on one of your SQL Objects using Powershell.

Assume you want to set a property on a database. If you don’t have your SQL psdrive loaded you can add it (assuming you have the prereqs) with:

add-pssnapin SqlServerProviderSnapin100

The name of your property will be “TestName” and you want a value of “TestValue”.

You will need your DB as an object.  If you have your SQL PSDrive you can use:

$DBObject = Get-Item SQLSERVER:\SQL\SERVERNAME\INSTANCE\Databases\$DBName

First, you create your new ExtendedProperty Object: 

$ExtProp = new-object Microsoft.SqlServer.Management.Smo.ExtendedProperty

Then you need to designate what object you are attaching your new property to:

$ExtProp.Parent = $DBObject

Set your Name and Value:

$ExtProp.Name = "TestName"

$ExtProp.Value = "TestValue"

Finally, create the object.  This seems to be more like applying it to me:

$ExtProp.Create()

Full list:

$DBObject = Get-Item SQLSERVER:\SQL\SERVERNAME\INSTANCE\Databases\$DBName
$ExtProp = new-object Microsoft.SqlServer.Management.Smo.ExtendedProperty

$ExtProp.Parent = $DBObject

$ExtProp.Name = "TestName"

$ExtProp.Value = "TestValue"

$ExtProp.Create()

Using powershell to run SQL Jobs serially

We had some backups fail due to disk space issues.  Once we cleared the space, we needed to run the backups as I didn’t want to wait an entire day.  I didn’t want to run them at once due to contention issues and I am way too lazy to manually start each one and wait for it to finish.

Powershell to the rescue. 

The SQL provider is slick, gives you a sql server psdrive that you can explore.  Finding the jobs was easy enough.  Finding failed jobs was easy enough.  Running the jobs… wtf?  Not hard, but seems like I should be able to just start a job rather than send it as a sql command.  I hardly need the sql snapin for that.

Anyway, update SERVERNAME (and potentially instance) in the code below and it will find all your failed jobs and run them serially.

Sweet

cd SQLSERVER:\SQL\SERVERNAME\DEFAULT\JobServer\Jobs

$jobs  = dir | where {$_.lastrunoutcome -eq "failed" }

foreach ($job in $jobs) {

      "starting $($job.name)"

      invoke-sqlcmd -database msdb "sp_start_job '$($job.name)'"

      while ((invoke-sqlcmd -database msdb "sp_help_jobactivity @job_id = NULL, @job_name =  '$($job.name)'").run_status -ne 1) {

            "still running..."

            sleep 120

      }

      "Completed at $(date)"

}

Timeouts Removing VMWare snapshots w/ PowerCLI

On commit of several of our vmsnapshots we are getting the annoy “Operation Timed Out” after the vcenter timeout limit is reached.  Luckily, the remove snapshot operation is still performed in the background and eventually completes.  Below is a quick and dirty way to delete a vm snapshot and wait for the process to complete.  I check the existence of the snapshot every 60 seconds and return when it is gone.  If it is still going after 2 hours, I throw an error.  It is pretty noisy as I wanted to see what was happening but didn't feel like getting into logging, you will probably want to add to it if you need to actually use it. 

This is to be run in a PowerCLI window.  Add the function and then you can use
    delete-vmsnapshot "VMName"

Note: if you add -confirm:$false to the remove-snapshot line, you won't be prompted to confirm the delete. I took it out of the below to add some protection against copying and pasting code.  B)

Note 2: if you don't care about how monitoring how long it takes you can just do something like the following.  I wanted to loop through several serially and didn't want them starting over each other.
     get-vm -name "VMNAME" | get-snapshot | remove-snapshot

function delete-vmsnapshot {

      param ($vmname)

       $d1 = date

       "Starting at $d1"

       $vm = get-vm -name $vmname

       $sn = $vm | get-snapshot

       "Deleting:"

       $sn | fl *

       $sn | remove-snapshot 

       $timesleeping = ((date) - d1).TotalSeconds

       while (-not (($vm | Get-Snapshot) -eq $null)) {

            $timesleeping = ((date) - d1).TotalSeconds

            if ($timesleeping -gt 7200) {

                  throw ("waiting over 2 hours for snapshot to delete")

            } else {

                  "Merging for $timesleeping seconds."

            }

            sleep 60

      }

      $d2 = ((date) - d1).TotalSeconds

      "Complete taking a total of $d2 seconds" 

}

      

SCCM Distribution point tar file error

I spent all weekend fighting various sccm parent-> Child site replication issues.  One I had around some of my software update packages had basically no information on the web despite what I would expect to be a very ‘searchable’ signature.  the misspelling in signature in this line:

failed to register the source with the signautre repository

The full error message I was getting is below but the most obvious lines were (from distmgr.log on the child)

invalid tar file signature
UnpackFolderW failed; 0x80040299
UnpackFolder() failed
failed to register the source with the signautre repository
UpdateStagedFolderRdcW failed; 0x80070299
Cannot copy package CEN00004 from e
Failed to copy package CEN00004 from e
STATMSG
Forward package status for pkg CEN00004 to site CEN
Error occurred.

I was stuck in a loop w/ two of my packages where they would expand to an e:\_S XXXXXXX temp directory but would fail during or just after that step w/ the above errors. 


Resolution:
Short answer:
Delete from dp, turn off binary replication, repush.

Step by step:
What finally worked was to remove the packages from the child distribution point at the parent server, turn off binary replication, and re-add them to the DP.  So, from the parent server Computer Management –> Software Updates –> Deployment Packages –> My Package –> right click on distribution points –> manage distribution points –> delete from selected.  Once I saw this go through in the distmgr log of the child site, which looks something like:  

Updated replicated package info for package CEN00004
Updated replicated package server ["Display=\\childsite\"]MSWNET
Updated replicated package program info for package CEN00004, program *, dependent program
Successfully processed C
Removing package CEN00004 from the active package array.

Right click on your package under Deployment Packages and uncheck ‘Enable Binary Replication’.
Right click on Deployment Packages –> your package –> distribution points and add a new DP.
You should see it uncompress to the _S temp folder and then continue to creating the directory structure and moving the files into your SMSPGKE$ (or whichever drive is appropriate).
Full log of the repeating (erroring) attempt below. 
Hopefully this will save somebody an afternoon.
Good luck


Starting package processing thread,thread ID = 1670
Sleep 3600 seconds...
STATMSG
Retrying package CEN00004
No action specified for the package CEN00004.
Start adding package to server ["Display=\\childsite\"]MSWNET
["Display=\\childsite\"]MSWNET
Will wait for 1 threads to end.
Thread Handle = 7056
Attempting to add or update a package on a distribution point.
for ["Display=\\childsite\"]MSWNET
STATMSG
Established connection to ["Display=\\childsite\"]MSWNET
Signature share exists on distribution point path MSWNET
Found the existing package path on MSWNET
Validating the compressed file E
Used 1 out of 7 allowed processing threads.
Sleep 3600 seconds...
The package file requires a NTFS drive to decompress to.
Decompressing package E
Processing incoming file C
Package CEN00004 is currently being processed
Processing incoming file C
Package CEN00004 is currently being processed
Used 1 out of 7 allowed processing threads.
Sleep 3600 seconds...
Used 1 out of 7 allowed processing threads.
Sleep 3600 seconds...
Used 1 out of 7 allowed processing threads.
Sleep 3600 seconds...
STATMSG
GetPackageSignature() called for package CEN00004 with version 3. UnpackedSignature = 0
RDC
for ["Display=\\childsite\"]MSWNET
IISPortsList in the SCF is "80".
IISSSLPortsList in the SCF is "443".
IISWebSiteName in the SCF is "".
IISSSLState in the SCF is 0.
Virtual Directory SMS_DP_SMSPKGE$ for the physical path E
for ["Display=\\childsite\"]MSWNET
IISPortsList in the SCF is "80".
IISSSLPortsList in the SCF is "443".
IISWebSiteName in the SCF is "".
IISSSLState in the SCF is 0.
Virtual Directory SMS_DP_SMSSIG$ for the physical path E
Get access to the package directory and the number of free bytes at that location.
Attempting to make an accessible connection to MSWNET
Established connection to MSWNET
Getting the NAL path's NOS equivalent.
The NOS path is \\childsite\SMSPKGE$\CEN00004\
Getting the number of free bytes.
The number of free K bytes 221886568
Successfully made an accessible connection, got a NOS path, and, if requested, got the number of free bytes at this location.
Attempting to make an accessible connection to MSWNET
Established connection to MSWNET
Getting the NAL path's NOS equivalent.
The NOS path is \\childsite\SMSSIG$\
The number of free bytes at the specified location has not been requested.
Successfully made an accessible connection, got a NOS path, and, if requested, got the number of free bytes at this location.
Successfully set access security on MSWNET
GetPackageSignature() called for package CEN00004 with version 3. UnpackedSignature = 0
RDC
RegisterSignatureUsage() called with for Package CEN00004, Version 3 with Targetpath as E
invalid tar file signature
UnpackFolderW failed; 0x80040299
UnpackFolder() failed
failed to register the source with the signautre repository
UpdateStagedFolderRdcW failed; 0x80070299
Cannot copy package CEN00004 from e
Failed to copy package CEN00004 from e
STATMSG
Forward package status for pkg CEN00004 to site CEN
Error occurred.
Performing error cleanup prior to returning.
DP thread with array index 0 ended.
DP thread with thread handle 7056 and thread ID 5628 ended.
Updating package info for package CEN00004
Only retrying local DP update for package CEN00004, no need to replicate package definition to child sites or DP info to parent site.
StoredPkgVersion (3) of package CEN00004. StoredPkgVersion in database is 3.
SourceVersion (3) of package CEN00004. SourceVersion in database is 3.
STATMSG
Failed to process package CEN00004 after 3 retries, will retry 97 more times

Automatically closing SCOM alerts w/ powershell

You may have seen my post earlier about Syslog Monitoring Walkthrough with Systems Center Operations Manager 2007.  Well, I went on vacation and my colleagues got a little overzealous in what we directed at scom.  I came back to over 200k warnings from one device that was sending alerts at waaaay too low a threshhold.  These were going into a view that wasn’t normally checked (it seems) and now we have a problem.  The console crashes when trying to select all of them (surprise) and I do not want to do this by hand.  Powershell to the rescue?
After opening the OpsMgr PS console, we can get started.  Setting the alert to closed (ResolutionState =255) should be trivial.  We mustn’t forget to update the alert after we set the state.  The interesting part is that we need to get our collection of alerts with as little impact as possible.
I could just do:
$colAgents = get-alert
but that would crush my database and take forever.  To narrow this down a bit, I am going to select by source (as basically all of these came from the same source.) 
We could run a get-alert | where {SOMETHING} but that would have to return all the alerts and then parse them which would be very heavy.  The SCOM cmdlets have a –criteria to do the filtering in the db, lets use that.
Source seems to translate, at an object level, to MonitoringObjectDisplayName so I set my criteria, do the query and loop through the results like so:

$criteria = “ResolutionState = 0 AND MonitoringObjectDisplayName = ‘SERVERNAME’”

$colSysLogAlerts = get-alert -criteria $criteria

foreach($Alert in $colSysLogAlerts) {

      $Alert.ResolutionState = 255

      write-host “Closing $($Alert.id)”

      $Alert.Update("Closed by Powershell")

}

Powershell to the rescue indeed.

Syslog Monitoring Walkthrough with Systems Center Operations Manager 2007

Building on excellent posts here, here and here and feedback on the forums here, I am going to describe our Operations manager 2007 syslog setup.


First step was to build a group of servers that will be our Syslog collectors in Opsmgr.  Populate your Syslog Collectors group w/ the servers you will be pointing your syslog messages towards.  I created a new management pack called _Default_Syslog that will hold all the work in this section. 



Once I have my group, I want to create a new rule.  Under the rule wizard, I select Alert Generating Rules –> Event Based-> Syslog (Alert). 

I am going to have 3 Syslog rule.  One for each state, warning, error and informational.  The first rule I am making is the error rule.  So we are going to name it, Syslog Error.  (I know, imaginative)

Since I am collecting Alerts, it seems “Alert” would be a good rule target but I will admit to being a bit hazy on this area of SCOM and what it implies.  If you know better, please let me know.

For rule target, I am just going to pick Windows Computers.  Note that at some point I tried to target this at the Agent and this caused problems when I wanted to use a Management server as a collector.  To quote Graham Davies from the System Center Operations Manager General forums:

If you want the rule to run on the RMS \ Gateway then you'll need to target the appropriate class - management server. Although the Management Servers include "agent capability", they are not agents and hence won't get the rule if you target agent.

MAKE SURE you unselect Rule is Enabled or you will turn on syslog collection for all of your servers.  We will override to turn on these rules for our specific group later.




For Event Expressions, I want to catch all events w/ a Severity of less than or equal to 3.  So I build my Filter like so:



For my alerts, I like to get more data so I am going to put the following in the description:

Facility:
$Data/EventData/DataItem/Facility$
Severity:
$Data/EventData/DataItem/Severity$
Priority:
$Data/EventData/DataItem/Priority$
Priority Name
$Data/EventData/DataItem/PriorityName$
Time Generated:
$Data/EventData/DataItem/TimeStamp$
Hostname:
$Data/EventData/DataItem/HostName$
Message:
$Data/EventData/DataItem/Message$

Click create to finish the wizard.

Repeat the above for the Warning Alerts.  You will only need to change the Name, the severity on the alert and make your Filter equal to 4 like so:



And again for your Informational alerts.  Your filter will be greater than or equal to 5 and don’t forget to change your severity.

I chose to capture these as infomational alerts for now, I am going to see how spammy this gets.  another option is to create these as Event collection rules without alerts.  That will allow you to hold on to the data but not alert on it.  It will depend on your alerting requirements.  You can also manage what you send from your sending device’s syslog configuration.

To enable these rules on specific servers, right click on your rule and choose ‘Overrides’ –> Override the Rule –> For a group…



 Select your Syslog Servers group and override the rule to enable it on these servers.



You can verify that the syslog listener is enabled by getting on the machine you expect to be listening and checking for UDP 514 in netstat.  Open a command windows and type ‘netstat –an | findstr 514’  (findstr = grep).  You want to see a line like the below.  If you don't get anything, you are not listening for syslog packets.



In order to make this easy to see, I also created a view.  Under the SCOM Console Monitoring view, find your _default_syslog folder –> right click –> New –> Alert View. Include alerts ‘created by specific sources’.  Pick your rules that you just created.





You should end up w/ a view like (your source will probably be a computer name):




More info I found two ways to display more info in the line.  The first way is the most straightforward and I typed it up first so I am going to show it anyway.  You probably want the second way.  B)
The first way:

You can add more information to your alert and then set your view to show it.  Under your alert configuration, you can add custom alert fields.  I added fields for both my hostname and my priority name.



Send a few more test syslogs.  BTW, the kiwi syslog generator works perfectly for this.  Update your view so that you show Custom Field 1 and Custom Field 2 from the Display tab under your view properties.



Note that for some reason my extra columns were not displaying initially.  I had to delete and recreate the view.  Since my source was always the same, I also stopped showing the source.  You can see the syslog entry created after the change has populated the custom fields:



The second way:

This initially involved some (simple) XML munging so I was going to have an advanced and a simple method, the simple being the one above.  Once I figured out how in the XML, it turns out you can do it in the GUI as well. 

I first exported the MP and saw the below XML

          <Name>Syslog Error</Name>
          <Description>Facility:
{0}
Severity:
{1}
Priority:
{2}
Priority Name
{3}
Time Generated:
{4}
Hostname:
{5}
Message:
{6}</Description>

Note that the curly bracked enclosed items are expanded to our variables.  So {5} is analogous to $Data/EventData/DataItem/HostName$ in our GUI based alert description.  Putting $Data/EventData/DataItem/HostName$  in the alert name doesn’t work but putting {5} does.  So I changed the XML to

          <Name>Syslog Error: {5} - {3}</Name>
          <Description>Facility:
{0}
Severity:
{1}
Priority:
{2}
Priority Name
{3}
Time Generated:
{4}
Hostname:
{5}
Message:
{6}</Description>

When I checked my alert view, voila!, My alert name has changed as in the screen shot below.  My next thought was why can’t I do that from the gui?  So, i edited my alert name to be: Syslog Warning: {5} - {3}



Back to my alert view and, check it out, useful alert names!!  Maybe I am too excited about this but it makes it much more supportable in my opinion.  B)



One note, I am putting the syslog collector on other running servers.  We have only a few devices that are sending data here and only critical events are expected.  YMMV, you may want to dedicate a server to this.

Using Powershell to create groups, populate groups and retrieve LDAP distinguished names

Update 20121203 - I just noticed this is still getting hits.  this is largely deprecated, from Powershell 2.0 and beyond, do your self a favor and use the ActiveDirectory module.  if you don't have access to that module for some reason (they do exist) the below should still work.

Update: 20100507 - updated the sam search string to do fuzzy matching.

A couple more functions I needed during a recent project.  Using powershell of course, we needed to create a group in ad in a specific OU.  We also wanted to populate that group.  To do the second, I needed a helper function to get the distinguished name of a group or user.  I leverage two main sites for most of these with some light modifications.   

For create group we pass it the name of our new group and the OU we want to create our group in, such as:

Create-group “Newgroup” “OU=ServerGroups,DC=example,DC=com”

function create-group ($groupname, $strOU) {

      $OU = [adsi]"LDAP://$strOU"

      $group = $ou.Create("Group", "CN=$groupname")

      $group.setinfo()

      $g2 = [adsi]"LDAP://CN=$groupname,$strOU"

      $g2.sAMAccountName = $groupname

      $g2.setinfo()

      Write-Host "Created $groupname in $strOU"

}

For add-usertogroup we pass it a user DN.  DNs are the full LDAP distinguished name like:

LDAP://CN=myuser,OU=Admins,DC=example,DC=com 

So we would pass something like

Add-usertogroup “LDAP://CN=myuser,OU=Admins,DC=example,DC=com” “LDAP://CN=Newgroup,OU=ServerGroups,DC=example,DC=com”

That is kind of annoying so you could also use the get-dn function below. 

The safe way:

$userDN = get-dn "myuser"

$groupDN = get-dn "newgroup"

add-usertogroup $userDN $groupDN

or the one liner

add-usertogroup (get-dn "myuser") (get-dn "newgroup")

function add-usertogroup ($userDN, $groupDN) {

      $user = [adsi]$userDN

      $group = [adsi]$groupDN

      Write-Host "Adding $($user.cn) to $($group.cn)"

      $members = $group.member

      $group.member = $members+$user.distinguishedName

      $group.setinfo()

}

I would further note this excellent site describing how to write an LDAP filter.  It cleared a few things up for me at long last.  I can’t believe I never realized that & was a logical AND...  Pretty straightforward afterwards but the writeup above helped me bridge the gap.  Note that it is for some software or other so ignore the part about escaping the special characters at the top. B)

function get-dn ($SAMName)

{

      $root = [ADSI]''

      $searcher = new-object     System.DirectoryServices.DirectorySearcher($root)

      #note: if you don't want fuzzy searches, remove the *s from the line below.  
      #this will force a match of the search string only - thanks jc for the tip
      $searcher.filter = "(&(|(objectClass=user)(objectClass=group))(sAMAccountName=*$SAMName*))"

      $user = $searcher.findall()

      if ($user.count -gt 1)

      {   

            $count = 0

            foreach($i in $user)

            {

            write-host $count ": " $i.path

            $count = $count + 1

      }

    $selection = Read-Host "Please select item: "

      return $user[$selection].path

      } else {

      return $user[0].path

      }

}

Note that it matches the SAM Account Name (aka the ‘Pre-Windows 2000’ name in the AD snapin)

Enjoy

Use powershell to quickly backup all TFS Work Item Types

Hey all,
Just a quickie to easily backup all your current work items from all projects in Team Foundation Server 2008 SP1.  I wanted to do this ahead of a big migration and I am adverse to manual labor.

It starts w/ a function i found a couple years ago.  I don’t recall where or I would give credit here.  It is very useful for any tfs powershell manipulation.

Edit below to enter your $TFSHost and update your path if necessary.  I recommend running from a blank directory.  It will create a directory for each project and export each WIT for that project to the respective directories.

Update: I added another function from my library test-win32 and had that manage the default paths for x64 and x86 archs.


Good luck

$tfshost = "thshost"

 

function Test-Win32() {

    return [IntPtr]::size -eq 4

}

if (test-win32) {

      $tfstoolspath = "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE"

} else {

      $tfstoolspath = "C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE"

}

function get-tfs {

      #10-08 - cornasdf - i stole this from the web but this is a great way to connect to TFS

      # in your code you use: $tfs =  get-tfs $TFSHost

      #some quick examples from resolvedticketsmail.ps1:

      #get select from Stored query

      #$query = $tfs.WIT.Projects[$ProjectHoldingQuery].StoredQueries | where{$_.Name -eq $QueryViewName}

      #get results of specified query

      #$oldTickets = $tfs.WIT.Query($query.QueryText)

      param(

            [string] $serverName = $(throw 'serverName is required')

      )

      begin

      {

            # load the required dll

            [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.Client")

            $propertiesToAdd = (

                  ('VCS', 'Microsoft.TeamFoundation.VersionControl.Client', 'Microsoft.TeamFoundation.VersionControl.Client.VersionControlServer'),

                  ('WIT', 'Microsoft.TeamFoundation.WorkItemTracking.Client', 'Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore'),

                  ('CSS', 'Microsoft.TeamFoundation', 'Microsoft.TeamFoundation.Server.ICommonStructureService'),

                  ('GSS', 'Microsoft.TeamFoundation', 'Microsoft.TeamFoundation.Server.IGroupSecurityService')

            )

      }

      process

      {

            # fetch the TFS instance, but add some useful properties to make life easier

            # Make sure to "promote" it to a psobject now to make later modification easier

            [psobject] $tfs = [Microsoft.TeamFoundation.Client.TeamFoundationServerFactory]::GetServer($serverName)

            foreach ($entry in $propertiesToAdd) {

                  $scriptBlock = '

                        [System.Reflection.Assembly]::LoadWithPartialName("{0}") > $null

                        $this.GetService([{1}])

                  ' -f $entry[1],$entry[2]

                  $tfs | add-member scriptproperty $entry[0] $ExecutionContext.InvokeCommand.NewScriptBlock($scriptBlock)

            }

            return $tfs

      }

}    

$tfs = get-tfs $tfshost

$projects = $tfs.wit.Projects

foreach ($proj in $projects) {

      if ( -not (Test-Path -path .\$($proj.Name))) {

            New-Item .\$($proj.Name) -type directory

      }

     

      foreach ($wit in $proj.WorkItemTypes) {

            "Exporting $($proj.Name) : $($wit.Name) -> .\$($proj.Name)\$($wit.Name).xml"

            & "$tfstoolspath\witexport.exe"  /p "$($proj.Name)" /n "$($wit.Name)" /f ".\$($proj.Name)\$($wit.Name).xml" /t $tfshost

           

      }

}