by Duncan Russell
on May 12, 2015
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;
{ }
by Duncan Russell
on February 27, 2015
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
{ }
by Duncan Russell
on November 15, 2014
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
{ }
