Reporting on PST Files for Outlook using SCCM

This is an update to this: https://www.mnscug.org/blogs/sherry-kissinger/249-pstfinder .  The reason for the update is the old method (from 2013) worked for older versions of Outlook; but not for 2013 or newer.

With some clever scripting from different people, notably https://social.technet.microsoft.com/Forums/en-US/7ff6821c-b7cc-46e0-bc5a-342cfd9c0bf9/display-outlook-pst-file-location-on-remote-machines?forum=winserverpowershell , John Marcum and Sherry Kissinger, and we've got a routine that will, for the most part, answer those three questions.  The basics of how it works is this.  There are two vbscripts that run.  One runs as SYSTEM, and it's only purpose is to create a custom namespace in WMI, and grant permissions to all of your domain users to that custom namespace--so they can populate it with the results of script #2.  Script #2 runs, only when a user is logged in, with user rights.  That's because the majority of what the script needs to do is read information about that specific logged-in users Outlook configuration, and (potentially) any mapped drive information which may be referenced by the PST file location.

The results of the 2nd script end up in that custom WMI namespace, and will have the following information:

DateScriptRan = the exact date and time that the script ran to gather this user-specific information.
FileSizeinMB = If it could be detected, and the file size was 1mb or larger, the size of the PST.  If it's less than 1mb, or for whatever reason could not be detected, the value will be 0.
PSTFile = The DisplayName in Outlook
PSTLocation = The location as known to Outlook
Type = If it could figure out that Q: was a mapped network drive, it'll say 'Remote', otherwise it'll say local
UserDomain = whomever is logged in, what their domain is.
UserName = whomever is logged in, what their username is.
Location = This will either be a drive letter, or if it was possible to determine if that drive letter was really a mapped drive to a network location, the \\Server\Share will be populated.

End result:  After deploying these two scripts, you will be able to answer those pesky questions from your Exchange team about who, where, and how large, are referenced PST files.  Of course, the main limitation is this is per-user information.  If you have a lot of shared machines, or the same user has multiple computers (and connects to the same PST files on those multiple computers) you'll have to do some creative reporting to ensure you don't double-count the same PST files.

Ok, enough of how it works.  You really want to know *exactly* what to do, right?  Let's start!
 
Your Source folder for the package will contain 2 things:
CreateCustomCMClasses-RunAsSystem.ps1
PopulateWMI-RunAsUser.ps1

The .ps1 files are at this  -->link<--. 

You will need to make 1 change to "CreateCustomCMClasses-RunAsSystem.ps1", this line:
    [String]$Account   = 'YourDomainHere\Domain Users',
Modify that to be your domain (the domain your users are in that will be logging in and running script #2).

Create two programs; the first runs Powershell.exe -ExecutionPolicy Bypass -File CreateCustomCMClasses-RunAsSystem.ps1, whether or not a user is logged in, with Administrator rights.  The second runs Powershell.exe -ExecutionPolicy Bypass -File PopulateWMI-RunAsUser.ps1, only when a user is logged in, with user rights.  The 2nd one; you want to "run another program first", and have it run the first one.  It only needs to run the 1st program once, per computer; it doesn't need to re-run.

Advertise the 2nd program to a collection (I recommend a test/pilot first), and confirm that it works as you expect.  If you want to confirm the data is there, look in root\CustomCMClasses  (not root\cimv2) for cm_PSTFileInfo, that there are instances there for any Outlook-attached PST files for that user.

If you are satisfied it's there locally, import the below into Default Client Agent Settings, Hardware Inventory

[SMS_Report(TRUE),
 SMS_Group_Name("PSTFileInfo"),
 SMS_Class_ID("PSTFileInfo"),
 SMS_Namespace(FALSE),
 Namespace("\\\\\\\\localhost\\\\root\\\\CustomCMClasses")]

class cm_pstfileinfo : SMS_Class_Template
{
  [SMS_Report(TRUE)] string DateScriptRan;
  [SMS_Report(TRUE)] uint32 FileSizeinMB;
  [SMS_Report(TRUE)] string Location;
  [SMS_Report(TRUE)] string PSTFile;
  [SMS_Report(TRUE),key] string PSTLocation;
  [SMS_Report(TRUE)] string Type;
  [SMS_Report(TRUE)] string UserDomain;
  [SMS_Report(TRUE)] string UserName;
};


sit back, relax for a bit... then invoke a hardware inventory on your test boxes, and see if the data shows up in your database in v_gs_pstfileinfo0.  If so, deploy the advert to your real target collection of users or computers, and wait for the data to show up.  Depending upon your need for this information; you may or may not want to have the advert run on a recurring basis (weekly? monthly?) or just gather it for a week or so (just enough to answer the question) then delete the advert and change the Inventory from TRUE to FALSE (until the next time they ask).

Here's a potential sql report to get you started:

select sys.Name0 as [Computer Name],
pst.UserName0 as [User],
pst.PSTFile0 as [File Name],
pst.PSTLocation0 as [File Location],
pst.Type0 as [Local/Remote],
pst.Location0 as [Location],
pst.FileSizeinMB0 as [Size in MB],
pst.DateScriptRan0 as [Date Collected]
from v_R_System sys
Inner Join v_GS_PSTFileInfo0 pst on sys.ResourceID = pst.ResourceID
order by sys.Name0
 

  • Created on .

ConfigMgr RefreshServerComplianceState as a Configuration Item

State messages are great, because they are quickly processed.  However, it can (and does) occasionally happen where for network reasons, corrupt data, or other influences, some State Messages from your ConfigMgr Clients never make it from the client into your database.  Normally, that isn't a big deal--however, what does sometimes happen is those state messages are for Software Updates.  If you have people who look at reports for Software Updates, and some clients locally say they are Compliant for Software Update KB123456, but when you look at reports based on your database, for that same client, the database says KB123456 on that client is non-Compliant.  Read this: https://blogs.msdn.microsoft.com/steverac/2011/01/07/sccm-state-messagingin-depth/ for a much better explanation of why and how; but the short conclusion to that situation is you want to ask your clients to occasionally run what is referred to as a "RefreshServerComplianceState" locally.  Basically, you are asking your clients to resend the state messages about compliant/non-compliant for all existing Software Updates they are aware of locally, to ConfigMgr, your database.  aka.. exactly what it says on the tin.  Refresh Server Compliance State.

The short and sweet is that it's really just a line or two of vbscript or powershell code.  But if you are in a large environment, you often don't want to tell every single client to all send up state messages all on the same day.  It could POTENTIALLY be a lot of data, and backlog your servers' SQL processing.  It would eventually catch up... but why create a headache for yourself?

Below is a Powershell Script that you COULD choose to run... as a set-it-and-forget-it type of thing.  As-is, if you took the below and deployed it as a Powershell Script in a Configuration Item, and the Baseline were to run daily, your clients would around about 2x a year or so randomly decide to RefreshServerComplianceState .  If you want it more frequent; change the 180 to say... 90  (to be about every 3 months, or 60 to be about every 2 months). 

The below is just a suggestion, and you can take it and break it as you like.

<#

.SYNOPSIS

This routine is to generate a random number between 1 and "MaximumRandom". In general, it a Maximum Random

number will likely be 180; if the Configuration Item is run daily, approximately twice a year it is expected

that a client will randomly pick a value of 1, and trigger a RefreshServercomplianceState

.DESCRIPTION

- This script would likely be used by a Configuration Manager Administrator as a 'Configuration Item', as the

"Detection" script in that Configuration Item. The Administrator would set it up as a detect-only script, where

the "what means compliant" is that any value at all is returned.

- The Configuration Manager Administrator would likely add this to a baseline, and deploy that baseline to run

on a Daily basis to their windows-os based devices, which scan for or deploy patches using the Software Updates Feature.

- Using the MaximumRandom number of 180, presuming the baseline runs daily, approximately twice a year based on

random probabilities, a client will trigger to run the "ResetServerComplianceState". See the blow mentioned

below for why this is something a Configuration Manager Administrator might want to do this.

- If the Configuration Manager Administrator wants to make it randomly occur more frequently or less frequently,

they would either adjust the $MaximumRandom number higher or lower, or modify the frequency of the Baseline evaluation

schedule.

- For interactive testing, modify $VerbosePreference to 'Continue' to see what action was taken. Remember to change

it back to SilentlyContinue for live deployments.

- If a client does trigger, an EventLog entry in the ApplicationLog with an Information EventId of 555 from SyncStateScript

will be created. You can add or modify the -Message entry for the EventLog to be as verbose as you need it to be for

your own potential future tracking purposes. Perhaps you might want to add in specifics like "Configuration Item

Named <whatever> in the Baseline <whatever> triggered this action, this was originally deployed on <Date>"

Credits: Garth Jones for the idea.

https://blogs.msdn.microsoft.com/steverac/2011/01/07/sccm-state-messagingin-depth

for the reasons why it's a good idea to do so occasionally.

.NOTES

2018-05-06 Sherry Kissinger

$VerbosePreference options are

'Continue' (show the messages)

'SilentlyContinue' (do not show the message, this is the default if not set at all)

'Stop' Show the message and halt (use for debugging)

'Inquire' Prompt the user if ok to continue

#>

Param (

$VerbosePreference = 'SilentlyContinue',

$ErrorActionPreference = 'SilentlyContinue',

$MaximumRandom = 180,

$ValueExpected = 1

#ValueExpected Will likely always be 1, and never change; set as a parameter for ease of reporting.

)

$RandomValue = Get-Random -Maximum $MaximumRandom -Minimum 1

if ($RandomValue -eq $ValueExpected ) {

Write-Verbose "Random generated value of $RandomValue equals $ValueExpected, therefore RefreshServerComplianceState for ConfigMgr Client State Messages for Updates will be triggered."

$SCCMUpdatesStore = New-Object -ComObject Microsoft.CCM.UpdatesStore

$SCCMUpdatesStore.RefreshServerComplianceState()

New-EventLog -LogName Application -Source SyncStateScript -ErrorAction SilentlyContinue

Write-EventLog -LogName Application -Source SyncStateScript -EventId 555 -EntryType Information -Message "Configuration Manager RefreshServerComplianceState Triggered to Run. If questions on what this is for, refer to https://blogs.msdn.microsoft.com/steverac/2011/01/07/sccm-state-messagingin-depth/ "

}

else

{

Write-Verbose "Random generated value was $RandomValue, which does not equal $ValueExpected, RefreshServerComplianceState for ConfigMgr Client State Messages for Updates was not triggered. "

}

Write-Host 'Compliant'

Tags: CM12

  • Created on .

Just in case you got here via other means, I recommend checking out this information as well, with links to more maintenance routines and information--> https://deploymentresearch.com/Research/Post/665/Fixing-WSUS-When-the-Best-Defense-is-a-Good-Offense

A recent new storm (see here, Jeff Carreon's blog) of Clients’ software update scanning issues had our team re-evaluating all of our existing wsuspool , web.config, and wsus administration settings on our Software Update points. As some of you CM Admins may recall, there has been multiple times over the last few years where due to various and assorted things outside of our control, like metadata size for Windows Updates, we as CM admins have had to manually adjust settings in WSUS configuration. At my company, we had a few Configuration Items enforcing and monitoring those settings, but not all of them.

The below settings may or may not be the ones which are the settings YOU have in your environment. You are free to take them and modify them for whatever makes the most sense for your particular configuration. For us, these are the settings we’ve determined we want to monitor and enforce… at least at this time. It seems like the settings required for a healthy SU scanning environment seem to change every 18 months—at least here—so don’t presume these settings are the end-all be-all of golden configuration. Everyone is different, and things change. But hopefully, if you do need to monitor or set these in your environment, these examples can assist you in getting to your Golden Configuration.

Keep in mind this blog is written from the standpoint of a System Center Configuration Manager person, who has created these rules using what is called “Configuration Items”. The way I structured it was One Rule Per Configuration Item, at my company. However, one could just as easily have created one Configuration Item—and have multiple rules inside it. It’s more personal preference than anything else in this case. In my case, since I was building and testing the rules one by one, I wanted a clear separation in case I messed something up while testing in the lab.

“The Easy Button” – I exported the rules from my lab, and they are available --> here <--. You can import them into your System Center Configuration Management console, and adjust or review them there, and test them.  The exported rules are 14 CIs. To deploy them, you'd create a Baseline, add all 14 CIs to the Baseline.  Prior to saving the Baseline, modify each rule to be "optional" instead of "required".  Once saved, deploy the baseline to a test machine in "monitor" mode only--just to see what differences you might have.  You may want to modify multiple rules, or discard some as not necessary for you to monitor/remediate.  Once you are comfortable, deploy with 'remediation' and test again.

Occasionally, I’ve had requests to list out exactly what is inside each rule, usually because for whatever reason the import isn’t working as expected so the recipients can’t see what’s inside. So here is what is inside each Configuration Item… this will be long, but hopefully useful!

First of all, for each Configuration Item, I elected to make it an “Application” type. That because even though I’ve targeted the baseline just to the servers which I ‘know’ have the WSUS Feature installed, if someone were to accidentally deploy a baseline containing these rules to other devices, I want the CI to ‘not bother’ to run the CI test or remediation inside. It would be pointless and potentially confusing to people looking at reports. The “Detection” script for the Application is a Powershell Script type, this one line:

(Get-WmiObject -Namespace root\cimv2 -class win32_serverfeature -Filter "Name = 'WINDOWS SERVER UPDATE SERVICES'").Name

If anything is returned (the name), then the CI will assume the application is installed, and it will continue. If nothing is returned, it won’t continue.

Second, just about every single test requires the powershell model webadministration, using import-Module webadministration

Hopefully, your WSUS / Software Update point servers by default were installed with the add-windowsFeature Web-Server.Web-Scripting-Tools, but if you try to run these scripts interactively on a Software Update Point server, you get ‘failed to import webadministration module’, you’ll want to confirm that the web-scripting-tools component is installed—otherwise none of this will work.

Below are 14 Configuration Items we’re currently testing in our lab environment (will be moved to production once testing is complete). If you can’t import the .cab file attachment, worst case you can create your own Configuration Items using the below information.

 

CI 1

 

Title:

WSUS Administration Max Connections Should be Unlimited

Detection:

import-Module webadministration ; (get-itemproperty IIS:\Sites\'WSUS Administration' -name limits.maxConnections.Value)

Remediation Script:

import-Module webadministration ; set-Itemproperty IIS:\Sites\'WSUS Administration' -Name limits.maxConnections -Value 4294967295

Compliance Rule:

4294967295

CI 2

 

Title:

WSUS Administration MaxBandwidth should be unlimited

Detection:

import-Module webadministration ; (get-itemproperty IIS:\Sites\'WSUS Administration' -name limits.maxbandwidth.Value)

Remediation Script:

import-Module webadministration ; set-Itemproperty IIS:\Sites\'WSUS Administration' -Name limits.maxBandwidth -Value 4294967295

Compliance Rule:

4294967295

CI 3

 

Title:

WSUS Administration TimeOut should be 320

Detection:

import-Module webadministration

(get-itemproperty IIS:\Sites\'WSUS Administration' -Name limits.connectionTimeout.value).TotalSeconds

Remediation Script:

import-Module webadministration ; set-Itemproperty IIS:\Sites\'WSUS Administration' -Name limits.connectionTimeout -Value 00:05:20

Compliance Rule:

320

CI 4

 

Title:

WSUS ClientWebService web.config executionTimeout should be 7200

Detection:

import-Module webadministration

$FullFileName = (Get-WebConfigFile 'IIS:\Sites\WSUS Administration\ClientWebService').fullname

[XML]$xml = Get-Content $FullFileName

((($xml.configuration).'system.web').httpRunTime).executionTimeout

Remediation Script:

import-Module webadministration

$FullFileName = (Get-WebConfigFile 'IIS:\Sites\WSUS Administration\ClientWebService').fullname

$acl = get-acl $FullFileName

$ar = NewObject system.security.accesscontrol.filesystemaccessrule("NT Authority\SYSTEM","FullControl","Allow")

$acl.SetAccessRule($Ar)

Set-ACL $FullFileName $acl

[XML]$xml = Get-Content $FullFileName

$ChangeThis = ((($xml.configuration).'system.web').httpRunTime)

$ChangeThis.SetAttribute('executionTimeout', '7200')

$xml.Save($FullFileName)

Compliance Rule:

7200

 

NOTE: when setting the Compliance Rule, also check the box for “Report NonCompliance if this setting instance is not found”, because in some default installs of wsus, the web.config file doesn’t have this particular value at all. Checking that box there would ensure that it would be added if missing completely.

CI 5

 

Title:

WSUS ClientWebService web.config maxRequestLength should be 20480

Detection:

import-Module webadministration

$FullFileName = (Get-WebConfigFile 'IIS:\Sites\WSUS Administration\ClientWebService').fullname

[XML]$xml = Get-Content $FullFileName

((($xml.configuration).'system.web').httpRunTime).maxRequestLength

 

Remediation Script:

import-Module webadministration

$FullFileName = (Get-WebConfigFile 'IIS:\Sites\WSUS Administration\ClientWebService').fullname

$acl = get-acl $FullFileName

$ar = NewObject system.security.accesscontrol.filesystemaccessrule("NT Authority\SYSTEM","FullControl","Allow")

$acl.SetAccessRule($Ar)

Set-ACL $FullFileName $acl

[XML]$xml = Get-Content $FullFileName

$ChangeThis = ((($xml.configuration).'system.web').httpRunTime)

$ChangeThis.maxRequestLength = "20480"

$xml.Save($FullFileName)

Compliance Rule:

20480

CI 6

 

Title:

WSUS Service Should Be Running

Detection:

(Get-Service "WSUS Service").Status

Remediation Script:

Start-Service "WSUS Service"

Compliance Rule:

Running

CI 7

 

Title:

WSUSPool Application Pool Should be Started

Detection:

import-Module webadministration ; (Get-WebAppPoolState WSUSPool).Value

Remediation Script:

import-Module webadministration ; Start-WebAppPool -Name "WSUSPool"

Compliance Rule:

Started

CI 8

 

Title:

WSUSPool CPU ResetInterval should be 15 min

Detection:

import-Module webadministration ; (get-itemproperty IIS:\AppPools\Wsuspool -Name cpu.resetInterval.value).minutes

Remediation Script:

import-Module webadministration ; set-Itemproperty IIS:\AppPools\Wsuspool -Name cpu -Value @{resetInterval="00:15:00"}

Compliance Rule:

15

CI 9

 

Title:

WSUSPool Ping Disabled

Detection:

import-Module webadministration ; (get-itemproperty IIS:\AppPools\Wsuspool -Name processmodel.pingingEnabled).value

Remediation Script:

import-Module webadministration ; set-Itemproperty IIS:\AppPools\Wsuspool -Name processmodel.pingingEnabled False

Compliance Rule:

False

CI 10

 

Title:

WSUSPool Private Memory Limit should be 0

Detection:

import-module webadministration

$applicationPoolsPath = "/system.applicationHost/applicationPools"

$appPoolPath = "$applicationPoolsPath/add[@name='WsusPool']"

(Get-WebConfiguration "$appPoolPath/recycling/periodicRestart/@privateMemory").Value

 

Remediation Script:

import-module webadministration

$applicationPoolsPath = "/system.applicationHost/applicationPools"

$appPoolPath = "$applicationPoolsPath/add[@name='WsusPool']"

Set-WebConfiguration "$appPoolPath/recycling/periodicRestart/@privateMemory" -Value 0

 

Compliance Rule:

0

CI 11

 

Title:

WSUSPool queueLength should be 30000

Detection:

import-Module webadministration ; (get-itemproperty IIS:\AppPools\Wsuspool | Select queuelength).queueLength

Remediation Script:

import-Module webadministration ; set-Itemproperty IIS:\AppPools\Wsuspool -name queueLength 30000

Compliance Rule:

30000

CI 12

 

Title:

WSUSPool RapidFail Should be Disabled

Detection:

import-Module webadministration ; (get-itemproperty IIS:\AppPools\Wsuspool -name failure.rapidFailProtection).Value

Remediation Script:

import-Module webadministration ; set-Itemproperty IIS:\AppPools\Wsuspool -name failure.rapidFailProtection False

Compliance Rule:

False

CI 13

 

Title:

WSUSPool Recycling Regular Time interval should be 0

Detection:

import-Module webadministration ; ((get-itemproperty IIS:\AppPools\Wsuspool -name recycling.periodicRestart.time).Value).TotalMinutes

Remediation Script:

import-Module webadministration ; set-Itemproperty IIS:\AppPools\Wsuspool recycling.periodicRestart.time -Value 00:00:00

Compliance Rule:

0

CI 14

 

Title:

WSUSPool requests should be 0

Detection:

import-module webadministration

$applicationPoolsPath = "/system.applicationHost/applicationPools"

$appPoolPath = "$applicationPoolsPath/add[@name='WsusPool']"

(Get-WebConfiguration "$appPoolPath/recycling/periodicRestart/@requests").Value

 

Remediation Script:

import-module webadministration

$applicationPoolsPath = "/system.applicationHost/applicationPools"

$appPoolPath = "$applicationPoolsPath/add[@name='WsusPool']"

Set-WebConfiguration "$appPoolPath/recycling/periodicRestart/@requests" -Value 0

 

Compliance Rule:

0

 

 

  • Created on .

ConfigMgr Windows Update Agent Scan Results SQL queries

During the storms we received for WSUS / WUA Scanning in ConfigMgr during the last few weeks, in addition to Network traffic results, another way we could almost quickly tell if our Software Update Points were getting overwhelmed was by monitoring Scan Failures.  Attached --> Here <-- are a couple SQL Queries that should work in any environment.  Although the attached do not cover every possible scan result error code, it includes some error codes I've gleaned over the years from various sources.

If you have multiple Software Update Point servers, one of the reports inside can help you see if perhaps 1 particular server is being swamped but others are OK.  You can also adjust that report slightly--what is attached defaults to "client scan results in the last 24 hours".  When we were actively working the various issues--during several attempts where we thought 'A-ha! this might be it as the fix!', we could change it to "scan results in the last 1 hour" and see if successful scans were rising and failures decreasing.

Hopefully these sql queries might be a starting point for others caught in the same struggles of maintaining their WSUS / Software Update Points with all the challenges that seem to have been occurring in the last several months.

  • Created on .

Example of Custom SQL Job to log to Application Event Viewer for ConfigMgr

This issue:  https://mnscug.org/blogs/sherry-kissinger/477-configmgr-current-branch-topic-id-611-swd-state-messages-flood, where Console admins accidentally checking the box for "Use Server Groups", where all members of that collection then start sending state messages every 1 minute, failing to patch, and potentially affecting State Message processing ... that occurred again in our environment.  We got lax in interactively checking for whether or not a collection accidentally had that done.  Since we have System Center Operations Manager for monitoring, and SCOM can trigger on EventIDs, we made a custom SQL Job to run multiple times a day, and if any collections (not already exempted/expected to have that setting) were to get that setting, that job will drop an Error into the Application Event Log on the Server.  A custom SCOM rule, and we'll now get alerted if this happens again.

In case someone else might find this useful, here is what we did. 

1) TEST first.  Create a collection with 1 member in it, and set that checkbox (so that you can test).  In SSRS itself, as a new query run this...modifying both of the "c3.name not in " entries for any known exceptions your environment already has.  Use Server Groups might be a valid and expected setting for servers in a Server Cluster, for example.  This is only to help assist when that setting is accidentally checked.

if (select case when count(c1.UseCluster) > 0 then 0 else 1 end as [result]
from CEP_CollectionExtendedProperties c1
join collections_g c2 on c2.collectionid=c1.collectionid
join v_collection c3 on c3.collectionid=c2.siteid
where c1.UseCluster=1
and c3.name not in ('Known Collection Name that Should Have that option checked','Another Collection that should be OK'))=0
BEGIN
 DECLARE @CollectionIDAndName nvarchar(200) = (
  select top 1 c2.siteid + ', ' + c3.name as [ValueToLog] from CEP_CollectionExtendedProperties c1
   join collections_g c2 on c2.collectionid=c1.collectionid
   join v_collection c3 on c3.collectionid=c2.siteid
   where c1.UseCluster=1
   and c3.name not in ('Known Collection Name that Should Have that option checked','Another Collection that should be OK')
)
 DECLARE @VALUE nvarchar(MAX) = ('At least 1 collection has Use Server Group Enabled: ' + @CollectionIDAndName + ' To resolve, either edit the SQL Job on the server to include this collection as a known exception, or edit that collection and uncheck Use Server Group. A possible consequence of that setting is machines in that collection may be unable to patch, and may also cause a state message backlog due to submitting state messages every minute')
 RAISERROR (@Value, 16, 1) with LOG
END

When you run that against your cm_xxx database in SSRS, because you created a test collection with that option checked, you should get an Error message in the Application Event Log on the server holding the cm_xxx database.  To test again, edit the properties of the collection and uncheck the box.  Then re-run the query to confirm it does NOT create an Application Event Log error message.  Once you have confirmed that an Event Log Error entry is created and works as you expect it to work, you can continue.

2) In SQL Server Management Studio, Create a new job, give it any name; but make it one you might understand when you or a coworker looks at sql jobs and is trying to make sense of it a year from now.  The Owner is your known standard owner, if you have a standard.  If you do not have a specific standard, try using sa or NT Authority\System.  (You just don't want to use your own individual personal ID--that is poor practice) You may also want to input as much of a description as possible.  For Steps, there will be only 1 step.
Type:  Transact-SQL script (T-SQL)
Run as:  <blank>
Database:  Your CM_xxx database
into the Command: area, input your entire tested sql script you tested successfully.

Under Advanced--use whatever Actions are your standards.  If you don't have a specific standard, we have "on success, quit the job reporting success", on Failure "Quit the job reporting failure".  0 retries, no output file, no log to table, run as user is blank.

Save the job (with no schedules for now).

3) test your job by editing the properties on a test collection and setting the "use Server Group" enabled.  Then in SQL SSRS, Jobs, right-click your job and  Run the Job from Step... and confirm you do get a new EventLog entry.  Remove the setting, and test again--ensuring you do NOT get a new EventLogEntry

4) Once you are satisfied it works as expected, using your favorite monitoring tool, whatever that might be (for us it was SCOM), set it up to monitor for Application Event Log, SOURCE: MSSQLSERVER  EventID: 17063  (that's what we got... confirm that is what you get in Application Event Log--I assume that is universal... but I've been wrong before, many many times)

5) Confirm your monitoring tool correctly reports on any new events, by again enabling Use Server Group on a test collection, run the job, removing, run the job.

6) Once confirmed; edit the job and add a Schedule or Schedules.  How frequently you would like this to run, and potentially alert on the issue, is up to your own standards and discretion.

Done!  With this in place, we hope we will get monitoring alerts about this situation... instead of alerts about a state message backlog.

  • Created on .

Copyright © 2018 - The Minnesota System Center User Group