Command to set a scheduled task monthly or weekly:

From CMD, not PowerShell (though you can call CMD commands from inside of PowerShell)

schtasks /create /ru SYSTEM /sc monthly /mo first /d SAT /tn Scheduled_Restart /sd "01/01/2016" /ed "12/31/2017" /st "22:00" /tr "shutdown /r /t 30"

schtasks /create /ru SYSTEM /sc WEEKLY /mo 1 /d SAT /tn Scheduled_Restart /sd "01/01/2016" /ed "12/31/2017" /st "22:00" /tr "shutdown /r /t 30"

SCHTASKS /Create [/S system [/U username [/P [password]]]] [/RU username [/RP password]] /SC schedule [/MO modifier] [/D day] [/M months] [/I idletime] /TN taskname /TR taskrun [/ST starttime] [/RI interval] [ {/ET endtime | /DU duration} [/K] [/XML xmlfile] [/V1]] [/SD startdate] [/ED enddate] [/IT | /NP] [/Z] [/F] [/HRESULT] [/?]

 

Description:

                Enables an administrator to create scheduled tasks on a local or remote system.

 

Parameter List:

/S	system	Specifies the remote system to connect to. If omitted the system parameter defaults to the local system.
/U	username	Specifies the user context under which SchTasks.exe should execute.
/P	[password]	Specifies the password for the given user context. Prompts for input if omitted.
/RU	username	Specifies the "run as" user account (user context) under which the task runs. For the system account, valid values are "", "NT AUTHORITY\SYSTEM" or "SYSTEM". For v2 tasks, "NT AUTHORITY\LOCALSERVICE" and "NT AUTHORITY\NETWORKSERVICE" are also available as well as the well known SIDs for all three.
/RP	[password]	Specifies the password for the "run as" user. To prompt for the password, the value must be either "*" or none. This password is ignored for the system account. Must be combined with either /RU or /XML switch.
/SC	schedule	Specifies the schedule frequency. Valid schedule types: MINUTE, HOURLY, DAILY, WEEKLY, MONTHLY, ONCE, ONSTART, ONLOGON, ONIDLE, ONEVENT.
/MO	modifier	Refines the schedule type to allow finer control over schedule recurrence. Valid values are listed in the "Modifiers" section below.
/D	days	Specifies the day of the week to run the task. Valid values: MON, TUE, WED, THU, FRI, SAT, SUN and for MONTHLY schedules 1 - 31 (days of the month). Wildcard "*" specifies all days.
/M	months	Specifies month(s) of the year. Defaults to the first day of the month. Valid values: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC. Wildcard "*" specifies all months.
/I	idletime	Specifies the amount of idle time to wait before running a scheduled ONIDLE task. Valid range: 1 - 999 minutes.
/TN	taskname	Specifies the string in the form of path\name which uniquely identifies this scheduled task.
/TR	taskrun	Specifies the path and file name of the program to be run at the scheduled time. Example: C:\windows\system32\calc.exe
/ST	starttime	Specifies the start time to run the task. The time format is HH:mm (24 hour time) for example, 14:30 for 2:30 PM. Defaults to current time if /ST is not specified.
/RI	interval	Specifies the repetition interval in minutes. This is not applicable for schedule types: MINUTE, HOURLY, ONSTART, ONLOGON, ONIDLE, ONEVENT. Valid range: 1 - 599940 minutes. If either /ET or /DU is specified, then it defaults to 10 minutes.
/ET	endtime	Specifies the end time to run the task. The time format is HH:mm (24 hour time) for example, 14:50 for 2:50 PM. This is not applicable for schedule types: ONSTART, ONLOGON, ONIDLE, ONEVENT.
/DU	duration	Specifies the duration to run the task. The time format is HH:mm. This is not applicable with /ET and for schedule types: ONSTART, ONLOGON, ONIDLE, ONEVENT. For /V1 tasks, if /RI is specified, duration defaults to 1 hour.
/K	kill	Terminates the task at the endtime or duration time. This is not applicable for schedule types: ONSTART, ONLOGON, ONIDLE, ONEVENT. Either /ET or /DU must be specified.
/SD	startdate	Specifies the first date on which the task runs. The format is mm/dd/yyyy. Defaults to the current date. This is not applicable for schedule types: ONCE, ONSTART, ONLOGON, ONIDLE, ONEVENT.
/ED	enddate	Specifies the last date when the task should run. The format is mm/dd/yyyy. This is not applicable for schedule types: ONCE, ONSTART, ONLOGON, ONIDLE, ONEVENT.
/EC	ChannelName	Specifies the event channel for OnEvent triggers.
/IT	interactive	Enables the task to run interactively only if the /RU user is currently logged on at the time the job runs. This task runs only if the user is logged in.
/NP	No password is stored.	The task runs non-interactively as the given user.
/Z	delete after run	Marks the task for deletion after its final run.
/XML	xmlfile	Creates a task from the task XML specified in a file. Can be combined with /RU and /RP switches, or with /RP alone, when task XML already contains the principal.
/V1	pre-Vista	Creates a task visible to pre-Vista platforms. Not compatible with /XML.
/F	force	Forcefully creates the task and suppresses warnings if the specified task already exists.
/RL	level	Sets the Run Level for the job. Valid values are LIMITED and HIGHEST. The default is LIMITED.
/DELAY delaytime	delay	Specifies the wait time to delay the running of the task after the trigger is fired. The time format is mmmm:ss.
/HRESULT	Result Code	For better diagnosability, the process exit code will be in the HRESULT format.

 

Modifiers:

Valid values for the /MO switch per schedule type:

MINUTE:              1 - 1439 minutes.
HOURLY:              1 - 23 hours.
DAILY:   1 - 365 days.
WEEKLY:               weeks 1 - 52.
ONCE:   No modifiers.
ONSTART: No modifiers.
ONLOGON: No modifiers.
ONEVENT:           XPath event query string.
ONIDLE:               No modifiers.
MONTHLY: 1 - 12, or  FIRST, SECOND, THIRD, FOURTH, LAST, LASTDAY.

 

Examples:

==> Creates a scheduled task "doc" on the remote machine "ABC" which runs notepad.exe every hour under user "runasuser".

SCHTASKS /Create /S ABC /U user /P password /RU runasuser  /RP runaspassword /SC HOURLY /TN doc /TR notepad

 

==> Creates a scheduled task "accountant" on the remote machine  "ABC" to run calc.exe every five minutes from the specified start time to end time between the start date and end date.

SCHTASKS /Create /S ABC /U domain\user /P password /SC MINUTE  /MO 5 /TN accountant /TR calc.exe /ST 12:00 /ET 14:00  /SD 06/06/2006 /ED 06/06/2006 /RU runasuser /RP userpassword

 

==> Creates a scheduled task "gametime" to run freecell on the  first Sunday of every month.

SCHTASKS /Create /SC MONTHLY /MO first /D SUN /TN gametime   /TR c:\windows\system32\freecell

 

==> Creates a scheduled task "report" on remote machine "ABC" to run notepad.exe every week.

SCHTASKS /Create /S ABC /U user /P password /RU runasuser  /RP runaspassword /SC WEEKLY /TN report /TR notepad.exe

 

==> Creates a scheduled task "logtracker" on remote machine "ABC" to run notepad.exe every five minutes starting from the specified start time with no end time. The /RP password will be prompted for.

SCHTASKS /Create /S ABC /U domain\user /P password /SC MINUTE  /MO 5 /TN logtracker   /TR c:\windows\system32\notepad.exe /ST 18:30  /RU runasuser /RP

 

==> Creates a scheduled task "gaming" to run freecell.exe starting at 12:00 and automatically terminating at 14:00 hours every day

SCHTASKS /Create /SC DAILY /TN gaming /TR c:\freecell /ST 12:00  /ET 14:00 /K

 

==> Creates a scheduled task "EventLog" to run wevtvwr.msc starting whenever event 101 is published in the System channel

SCHTASKS /Create /TN EventLog /TR wevtvwr.msc /SC ONEVENT  /EC System /MO *[System/EventID=101]

 

==> Spaces in file paths can be used by using two sets of quotes, one set for CMD.EXE and one for SchTasks.exe. The outer quotes for CMD need to be double quotes; the inner quotes can be single quotes or escaped double quotes:

SCHTASKS /Create   /tr "'c:\program files\internet explorer\iexplorer.exe'   \"c:\log data\today.xml\"" ...

Powershell Function - Get Occurance of Days of the Week

If you are like me, you keep running into people asking you to set their maintenance windows to the 3rd Saturday of the month or to the 2nd & 4th Friday of each month.  To help make it a little easier I've created this function that will return what the 3rd Saturday or the 4th Friday is.  Use this to help automate creation of those Maintenance Windows that don't easily fall into SCCM's normal parameters from the console.  Enjoy!

<#
.Synopsis
    Get-OccuranceOfDayOfWeek returns a specific occurance of a day of the week such as 3rd Saturday

.DESCRIPTION

   Get-OccuranceOfDayOfWeek returns a specific occurance of a day of the week
   such as the 3rd Saturday or the 1st Friday.  Useful for dealing with
   maintenance windows that occur on specific occurances of days of the week.
   The function only returns occurances within the current month, you will
   need to modify it if you need to project further into the future.

   It is possible that the 4th or 5th occurance may return a date in the
   following month.  This is not uncommon with the 5th occurance.

.EXAMPLE
   Get-OccuranceOfDayOfWeek -DayOfWeek Tuesday -Occurance 4
   Returns: Tuesday, February 28, 2017 11:28:54 PM

.EXAMPLE
   Get-OccuranceOfDayOfWeek -DayOfWeek Saturday -Occurance 2
   Returns: Saturday, February 11, 2017 11:39:37 PM

.INPUTS
   String  - Full name of a day of the week (Monday, Tuesday, etc)
   Integer - Which occurance to return (1-4)

.OUTPUTS
   DateTime - The date of the specified occurance within the current month

.NOTES
    # Script by Mark Randol
    # randoltech.blogspot.com
#>
function Get-OccuranceOfDayOfWeek{
    [CmdletBinding(DefaultParameterSetName='OccuranceOfDayOfWeek',
                  SupportsShouldProcess=$true,
                  PositionalBinding=$false)]
    [OutputType([DateTime])]
    Param(
        # Day of the Week
        [Parameter(Mandatory=$true,
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true,
                   ValueFromRemainingArguments=$false,
                   Position=0,
                   ParameterSetName='OccuranceOfDayOfWeek')]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [ValidateLength(0,15)]
        [ValidateSet("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")]
        [Alias("day","dayname","dow")]
        [String]
        $DayOfWeek,

        # Occurance
        [Parameter(Mandatory=$true,
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true,
                   ValueFromRemainingArguments=$false,
                   Position=1,
                   ParameterSetName='OccuranceOfDayOfWeek')]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [ValidateSet(1,2,3,4)]
        [Alias("occ")]
        [Int]
        $Occurance
    )
    Begin{
    }
    Process{
        [Int]$TestDayNum = 0
        [Arr]$OccuranceDates = @()
        do
        {
            $TestDayNum = $TestDayNum + 1
            $TestDayName = (Get-Date -Day $TestDayNum).DayOfWeek
        }
        until ($TestDayName -eq $DayOfWeek)
        $OccuranceDate = $TestDayNum
        do{
            $OccuranceDates += @($OccuranceDate)
            $OccuranceDate = $OccuranceDate + 7
        }
        while ($OccuranceDate -lt 32)
    }
    End{
        [Int]$OutputInt = $Occurance - 1
        [DateTime]$MyOutput = (Get-Date -Year (Get-Date).Year -Month (Get-Date).Month -Day $OccuranceDates[$OutputInt]).Date
        Return $MyOutput
    }
}

[DateTime]$MyDate = (Get-OccuranceOfDayOfWeek -DayOfWeek Saturday -Occurance 2)
Write-Output $MyDate


Powershell - Remove all included collections from a given collection ID

# Remove all included collections from a given collection ID
# Code Segment by Mark Randol - randoltech.blogspot.com
$ArrIncludeRules = Get-CMCollectionIncludeMembershipRule -CollectionId $DeploymentCollectionID
[Int]$LoopCounter = -1
foreach ($IncludeRule in $ArrIncludeRules)
{
    $LoopCounter++
    $IncCollID = $ArrIncludeRules.IncludeCollectionID[$LoopCounter]
    Write-Output "Removing $IncCollID"
    Remove-CMDeviceCollectionIncludeMembershipRule -CollectionId $DeploymentCollectionID -IncludeCollectionId $IncCollID -Force
}

Basic Maintenance Confguration Baseline

I like to create a configuration baseline, containing several configuration items, to check some basic client health items.  This is not only beneficial at the console but also benefits technicians/administrators at the endpoint when they are trying to troubleshoot issues.

• Configuration Items:
• ConfigMgr Client Health - Reboot Pending
• Checks CCMClientSDK in WMI
• (Invoke-WmiMethod -Class CCM_ClientUtilities -Name DetermineIfRebootPending -Namespace ROOT\ccm\ClientSDK -Computer $env:COMPUTERNAME).RebootPending equals False 
• Checks Component Based Servicing in registry
• HKLM\Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending must not exist
• Checks Pending File Rename Operations in registry
• HKLM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations string array must be empty
• Checks Windows Update Reboot Required in registry
• SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired must not exist
• Checks if last reboot time is greater than 90 day
• (New-TimeSpan -Start ((Get-CimInstance -ClassName win32_operatingsystem | select csname, lastbootuptime).lastbootuptime) -End (get-date)).Days less than or equal to 45
• ConfigMgr Client Health - Windows Update Service (wuauserv)
• (get-service -Name wuauserv).StartType not equal Disabled
• ConfigMgr Client Health - Windows Installer Service (msiserver)
• (get-service -Name msiserver).StartType not equal Disabled

• Configuration Baseline:
• ConfigMgr Client Health
• ConfigMgr Client Health - Reboot Pending
• ConfigMgr Client Health - Windows Update Service (wuauserv)
• ConfigMgr Client Health - Windows Installer Service (msiserver)

You can find client health collections here if interested: http://randoltech.blogspot.com/2016/06/client-health-collections.html

Client Health Collections

My Client Health Collections

The source that I used to use for client health collections has disappeared from the interwebs so I had to build my own.  I had someone mention that they used to use client health collections but have stopped doing so as more recent SCCM versions do a much better job of self-correcting.  While I agree that SCCM has gotten better at detecting and correcting health problems, it has not eliminated them.  If you don't know where on this spectrum your clients are at then it is far more difficult to troubleshoot problems.  So, I still believe that these are quite useful.  I still find duplicate computer names and duplicate GUIDs.  I still find missing hardware and software inventories as well as failures to heartbeat.  And, of course, I find things that SCCM can't auto-correct like missing clients, disabled services, and pending reboot.  If you trust SCCM to correct all of these and just assume everything is working nicely then you will end up with surprises ranging from deployment failures to surprise reboots.  Better to know ahead of time than to be surprised.

Cheater files are located here.  They include a powershell script to import collections from a .csv file (bonus, you can use it to create any collections you want) and a .csv with the definitions for all of these colllections.  As always, disclaimer, never blindly run anything that you find on the interwebs.  Look them over first to be sure that they will do what you think they should do.

1. Scope

Healthy Collection

• Name = "01. Healthy - Scope - Devices in Scope"

• Comment = "Checks for proper OS.  Discovered items like EMC Celera or UNIX systems are not in scope"

• Query Limited to = "All Systems"

• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%windows%"

Unhealthy Collection

• Name = "01. Ignored - Scope - Devices Not in Scope"

• Comment = "These do not have a standard Windows OS on them."
• Query Limited to = "All Systems"

• Query Statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion not like "%Windows%"

2. Obsolescence

Healthy Collection

• Name = "02. Healthy - Obsolescence - Device Not Obsolete"
• Comment = "These devices are discovered in both SCCM and AD"
• Query Limited to = "01. Healthy - Scope - Devices in Scope"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%windows%" and SMS_R_System.Obsolete = "0"

Unhealthy Collection

• Name = "02. Unhealthy - Obsolescence - Device Is Obsolete"

• Comment = "These devices are no longer in Active Directory.  They probably don't exist on the network anymore."

• Query Limited to = "01. Healthy - Scope - Devices in Scope"

• Query Statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%windows%" and (SMS_R_System.Obsolete != "0" or SMS_R_System.Obsolete is null )

3. Client

Healthy Collection

• Name = "03. Healthy - Client - SCCM Client is Installed"

• Comment = "These devices have an SCCM client installed.  This is the baseline for what SCCM should be able to manage."
• Query Limited to = "02. Healthy - Obsolescence - Device Not Obsolete"

• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = "0" and SMS_R_System.Client = "1"

Unhealthy Collection

• Name = "03. Unhealthy - Client - SCCM Client Not Installed"
• Comment = "These devices do not have a client installed.  They cannot be managed without a client."
• Query Limited to = "02. Healthy - Obsolescence - Device Not Obsolete"
• Query Statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = "0" and SMS_R_System.OperatingSystemNameandVersion like "%windows%" and (SMS_R_System.Client = "0" or SMS_R_System.Client is null )

4. Activity

Healthy Collection

• Name = "04. Healthy - Activity - Client Active"

• Comment = "These clients have provided some status to SCCM within the last 30 days."
• Query Limited to = "03. Healthy - Client - SCCM Client is Installed"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Obsolete = "0" and SMS_R_System.Client = "1" and SMS_R_System.Active = "1"

Unhealthy Collection

• Name = "04. Unhealthy - Activity - Client Inactive"
• Comment = "These clients have not provided any status to SCCM within the last 30 days."

• Query Limited to = "03. Healthy - Client - SCCM Client is Installed"

• Query Statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId where SMS_R_System.Obsolete = "0" and SMS_R_System.Client = "1" and (SMS_R_System.Active != "1" or SMS_R_System.Active is null ) 

5. Duplicate Name

Healthy Collection

• Name = "05. Healthy - Duplicate Name - Name Not Duplicated"
• Comment = "The device names of these clients are not duplicated on any known systems."
• Query Limited to = "04. Healthy - Activity - Client Active"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = "0" and SMS_R_System.Client = "1" and SMS_R_System.Active = "1"and SMS_R_System.Name not in (select r.Name from  SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Client = 1 and s1.Obsolete = "0" and s1.Name = s2.Name and s1.ResourceId != s2.ResourceId)

Unhealthy Collection

• Name = "05. Unhealthy - Duplicate Name - Name Duplicated"
• Comment = "These device names are duplicated, this can cause problems targeting devices by name."

• Query Limited to = "All Systems"

• Query Statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = "0" and SMS_R_System.Client = "1" and SMS_R_System.Active = "1" and SMS_R_System.Name in (select r.Name from  SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Client = 1 and s1.Obsolete = "0" and s1.Name = s2.Name and s1.ResourceId != s2.ResourceId)

6. Duplicate GUID

Healthy Collection

• Name = "06. Healthy - Duplicate GUID - GUID Not Duplicated"
• Comment = "These SCCM GUIDs are not duplicated on any active system."
• Query Limited to = "05. Healthy - Duplicate Name - Name Not Duplicated"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = "0" and SMS_R_System.Client = "1" and SMS_R_System.Active = "1" and SMS_R_System.Name not in (select r.Name from  SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Client = 1 and s1.Obsolete = "0" and s1.SMSUniqueIdentifier = s2.SMSUniqueIdentifier and s1.ResourceId != s2.ResourceId)

Unhealthy Collection

• Name = "06. Unhealthy - Duplicate GUID - GUID Duplicated"

• Comment = "These SCCM GUIDs are duplicated, usually caused by bad imaging practices"

• Query Limited to = "All Systems"

• Query Statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = "0" and SMS_R_System.Client = "1" and SMS_R_System.Active = "1" and SMS_R_System.Name in (select r.Name from  SMS_R_System as r full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId full join SMS_R_System as s2 on s2.Name = s1.Name where s1.Client = 1 and s1.Obsolete = "0" and s1.SMSUniqueIdentifier = s2.SMSUniqueIdentifier and s1.ResourceId != s2.ResourceId)

7. Hardware Inventory

Healthy Collection

• Name = "07. Healthy - HINV - Hardware Inventoried in last 14 days"

• Comment = "These devices have had their hardware inventoried recently."
• Query Limited to = "06. Healthy - Duplicate GUID - GUID Not Duplicated"

• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd,-14,GetDate())

Unhealthy Collection

• Name = "07. Unhealthy - HINV - Hardware Not Inventoried in last 14 days"

• Comment = "These devices have not had their hardware inventoried recently.  May indicate stale accounts, communication issues, or client issues."

• Query Limited to = "06. Healthy - Duplicate GUID - GUID Not Duplicated"

• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan < DateAdd(dd,-14,GetDate())

8. Software Inventory

Healthy Collection

• Name = "08. Healthy - SINV - Software Inventoried in last 45 days"

• Comment = "Software inventory is recent"
• Query Limited to = "07. Healthy - HINV - Hardware Inventoried in last 14 days"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId where SMS_G_System_LastSoftwareScan.LastScanDate >= DateAdd(dd,-45,GetDate())

Unhealthy Collection

• Name = "08. Unhealthy - SINV - Software Not Inventoried in last 45 days"

• Comment = "Software has not been inventoried recently."
• Query Limited to = "07. Healthy - HINV - Hardware Inventoried in last 14 days"

• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId where SMS_G_System_LastSoftwareScan.LastScanDate < DateAdd(dd,-45,GetDate())

9. Heartbeat

Healthy Collection

• Name = "09. Healthy - Heartbeat - Heartbeat is recent"
• Comment = "These clients have responded to heartbeat discovery in the last 23 days"
• Query Limited to = "08. Healthy - SINV - Software Inventoried in last 45 days"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where AgentName in ("Heartbeat Discovery") and DATEDIFF(day,AgentTime,GetDate())<=23

Unhealthy Collection

• Name = "09. Unhealthy - Heartbeat - No Heartbeat in 23 days"

• Comment = "These clients have not responded to heartbeat discovery in over 23 days"

• Query Limited to = "08. Healthy - SINV - Software Inventoried in last 45 days"

• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where AgentName in ("Heartbeat Discovery") and DATEDIFF(day,AgentTime,GetDate())>23

10. SCCM Client Version

Healthy Collection (the exact version needs to be maintained)

• Name = "10. Healthy - Client Version - Client is current"
• Comment = "The SCCM client is on a recent version"

• Query Limited to = "09. Healthy - Heartbeat - Heartbeat is recent"

• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = "0" and SMS_R_System.Client = "1" and SMS_R_System.Active = "1" and SMS_R_System.ClientVersion >= "5.00.9068.1008"

Unhealthy Collection

• Name = "10. Unhealthy - Client Version - Client is old"
• Comment = "The SCCM client is on an old version.  This can cause issues with deployments and can be an indicator of more severe underlying problems."
• Query Limited to = "09. Healthy - Heartbeat - Heartbeat is recent"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Obsolete = "0" and SMS_R_System.Client = "1" and SMS_R_System.Active = "1" and SMS_R_System.ClientVersion < "5.00.9068.1008"

11. WUAU Service

Healthy Collection

• Name = "11. Healthy - WUAU Service - WUAU enabled"

• Comment = "WUAU Service is not disabled."
• Query Limited to = "10. Healthy - Client Version - Client is current"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = "1" and SMS_R_System.Obsolete = "0" and SMS_R_System.Active = "1" and SMS_G_System_SERVICE.Name = "wuauserv" and SMS_G_System_SERVICE.StartMode != "Disabled"

Unhealthy Collection

• Name = "11. Unhealthy - WUAU Service - WUAU disabled"

• Comment = "WUAU Service is set to disabled.  Software updates cannot be installed on these devices."
• Query Limited to = "10. Healthy - Client Version - Client is current"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = "1" and SMS_R_System.Obsolete = "0" and SMS_R_System.Active = "1" and SMS_G_System_SERVICE.Name = "wuauserv" and SMS_G_System_SERVICE.StartMode = "Disabled"

12. WUAU Version

Healthy Collection

• Name = "12. Healthy - WUAU Version - WUAU Version Current"
• Comment = "The WUAU service has been updated within the last six months"
• Query Limited to = "11. Healthy - WUAU Service - WUAU enabled"

• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_WINDOWSUPDATEAGENTVERSION on SMS_G_System_WINDOWSUPDATEAGENTVERSION.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = "1" and SMS_R_System.Obsolete = "0" and SMS_R_System.Active = "1" and SMS_G_System_WINDOWSUPDATEAGENTVERSION.Version is not null  and SMS_G_System_WINDOWSUPDATEAGENTVERSION.TimeStamp >= DateAdd(dd,-185,GetDate())

Unhealthy Collection

• Name = "12. Unhealthy - WUAU Version - WUAU Version Old"

• Comment = "WUAU service has not been updated in over six months.  This means that they may need a servicing stack update.  If the servicing stack is too far behind they will not be able to apply updates."
• Query Limited to = "11. Healthy - WUAU Service - WUAU enabled"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_WINDOWSUPDATEAGENTVERSION on SMS_G_System_WINDOWSUPDATEAGENTVERSION.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = "1" and SMS_R_System.Obsolete = "0" and SMS_R_System.Active = "1" and SMS_G_System_WINDOWSUPDATEAGENTVERSION.Version is not null  and SMS_G_System_WINDOWSUPDATEAGENTVERSION.TimeStamp < DateAdd(dd,-185,GetDate())

13. MSIExec Service

Healthy Collection

• Name = "13. Healthy - Windows Installer - Windows Installer enabled"

• Comment = "Windows Installer service is not disabled"
• Query Limited to = "10. Healthy - Client Version - Client is current"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = "1" and SMS_R_System.Obsolete = "0" and SMS_R_System.Active = "1" and SMS_G_System_SERVICE.Name = "msiserver" and SMS_G_System_SERVICE.StartMode != "Disabled"

Unhealthy Collection

• Name = "13. Unhealthy - Windows Installer - Windows Installer disabled"

• Comment = "Windows Installer Service is disabled.  This means that anything that uses MSIExec.exe will be unable to run (like any .msi, .msu or .msp file)."
• Query Limited to = "10. Healthy - Client Version - Client is current"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = "1" and SMS_R_System.Obsolete = "0" and SMS_R_System.Active = "1" and SMS_G_System_SERVICE.Name = "msiserver" and SMS_G_System_SERVICE.StartMode = "Disabled"

14. Pending Reboot

Healthy Collection

• Name = "14. Healthy - Pending Reboot - Compliant"
• Comment = "These clients are compliant with the pending reboot CI.    Whenever the CI's version changes it will receive a new CIID and that new CIID must be updated in this collection's query."
• Query Limited to = "10. Healthy - Client Version - Client is current"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System join sms_combineddeviceresources on sms_combineddeviceresources.resourceid = sms_r_system.resourceid where sms_combineddeviceresources.clientstate = 0

Unhealthy Collection

• Name = "14. Unhealthy - Pending Reboot - Not Compliant or Error"
• Comment = "These clients are non-compliant, errored, or not detected for the pending reboot CI.  Whenever the CI's version changes it will receive a new CIID and that new CIID must be updated in this collection's query."

• Query Limited to = "10. Healthy - Client Version - Client is current"
• Query statement
• select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System join sms_combineddeviceresources on sms_combineddeviceresources.resourceid = sms_r_system.resourceid where sms_combineddeviceresources.clientstate != 0