≡ Menu

An end user reported an issue with their own machine not getting any software deployments. A deeper dive led us to find that the machine had failed on every deployment for some time, and needed some intervention. I wrote a T-SQL script to use in a report, so that our local techs could quickly determine clients that need investigation and intervention because of repeated failed deployments.

The following T-SQL script will check the last five deployments for every machine that has deployment status, and returns the ones that failed on all five of them (as well as their latest deployment status time, in Eastern Standard Time).

declare @tblFailedResourceIDs TABLE (ResourceID int, LastStatusTime datetime);
SET NOCOUNT ON;
declare @ResourceID int, @LastResourceID int, @iFailureCounter int, @LastStateName nvarchar(255), @iCounter int, @LastStatusTime datetime, @LastRecordedStatusTime datetime;
set @LastResourceID = 0;

DECLARE advert_cursor CURSOR READ_ONLY FOR
SELECT sys.ResourceID, LastStateName, stat.LastStatusTime
FROM v_advertisement adv 
JOIN v_ClientAdvertisementStatus  stat ON stat.AdvertisementID = adv.AdvertisementID 
JOIN v_R_System sys ON stat.ResourceID=sys.ResourceID 
WHERE 
(LastStateName != 'Accepted - No Further Status' and 
LastStateName != 'No Status')
order by sys.ResourceID, LastStatusTime desc;

OPEN advert_cursor;
FETCH NEXT FROM advert_cursor INTO @ResourceID, @LastStateName, @LastStatusTime;

WHILE @@FETCH_STATUS = 0
   BEGIN
      IF @ResourceID != @LastResourceID
	  BEGIN
		SET @iFailureCounter = 0;
		SET @iCounter = 0;
	  END
	  
	  SET @iCounter = @iCounter + 1
	  If @iCounter <= 5
	  BEGIN
		If @iCounter = 1
			SET @LastRecordedStatusTime = @LastStatusTime
		If @LastStateName = 'Failed'
			SET @iFailureCounter = @iFailureCounter + 1;
		If @iFailureCounter = 5
			INSERT INTO @tblFailedResourceIDs (ResourceID, LastStatusTime) VALUES (@ResourceID, @LastRecordedStatusTime)
	  END
	  SET @LastResourceID = @ResourceID
	  FETCH NEXT FROM advert_cursor INTO @ResourceID, @LastStateName, @LastStatusTime;

   END;
CLOSE advert_cursor;
DEALLOCATE advert_cursor;

SET NOCOUNT OFF;
SELECT Name0 AS Computer, DATEADD(HH,-5,F.LastStatusTime) AS Last_Status_Time_EST
 from v_r_system SYS
 INNER JOIN @tblFailedResourceIDs F
 on F.ResourceID = SYS.ResourceID
ORDER BY Name0;
{ 0 comments }

Where’s Waldo: SCCM Object Folder Path

You are in the Configuration Manager 2012 console, looking for a collection, or possibly a software package. You searched “All Subfolders” and found the item. But where exactly is it in the folder structure within the console?

I created a right-click tool for the console called SCCM Object Folder Path.  For collections (user and device), packages, applications, queries, and task sequences, right-click the object and select [Object] Path.  PowerShell will run in the background, and pop up a message box with that object’s folder path.  It has been tested with SCCM 2012 SP1 through R2.  Make sure you have at least PowerShell 3 installed (it has not been tested with versions below that).

NOTE: run PowerShell as Administrator to run Install.ps1 or Uninstall.ps1. Download it here: SCCM Object Folder Path

{ 0 comments }

Powershell Script to Download MMS session files

The Midwest Management Summit (MMS) completed earlier this week in Minneapolis, MN. Great sessions were given on System Center products, PowerShell, SQL, and other related technologies. I am thankful for the hard work of the Minnesota System Center User Group (MNSCUG) for organizing it, as well as the Windows Management User Group (WMUG) for the pass to the conference.

Below is a quick PowerShell script I wrote for downloading all of the session files. It iterates through the schedule on mms.sched.org, and downloads the attachment links from sched.org to session folders in c:\temp.  You can edit the base location for the download.  It will not download it again if it finds the file in the download location, so you can rerun it to check for additional attachments that have been added.

Get it: download-mms2014files.ps1

##############################################
#                                            #
# File:     download-mms2014files.ps1        #
# Author:   Duncan Russell                   #
#           http://www.sysadmintechnotes.com #
#                                            #
##############################################

$baseLocation = 'c:\temp'

Clear-Host
$uri = 'http://mms2014.sched.org'
$sched = Invoke-WebRequest -Uri $uri -WebSession $mms
$links = $sched.Links
$links | ForEach-Object {
    if(($PSItem.href -like '*event/*') -and ($PSItem.innerText -notlike '*birds*'))
    {
        $eventUrl = $PSItem.href
        $eventTitle = $($PSItem.innerText -replace "full$", "") -replace "filling$", "" 
        "Checking session '{0}' for downloads" -f $eventTitle
        $eventTitle = $eventTitle -replace "\W+", "_"
        
        $event = Invoke-WebRequest -Uri $($uri + $eventUrl)
        $eventLinks = $event.Links

        $eventLinks | ForEach-Object {
            $eventFileUrl = $PSItem.href;$filename = $PSItem.innerText;if($eventFileUrl -like '*hosted_files*'){
                $downloadPath = $baseLocation + '\mms2014\' + $eventTitle
                $outputFilePath = $downloadPath + '\' + $filename
                if((Test-Path -Path $($downloadPath)) -eq $false){New-Item -ItemType Directory -Force -Path $downloadPath | Out-Null}
                if((Test-Path -Path $outputFilePath) -eq $false)
                {
                    "...attempting to download '{0}'" -f $filename

                    Invoke-WebRequest -Uri $eventFileUrl -OutFile $outputFilePath -WebSession $mms;$doDownload=$false;
                    Unblock-File $outputFilePath
                    $stopit = $true
                }
            }
        }
    }
}

Get it: download-mms2014files.ps1

{ 8 comments }