organic fertilizer

system center advisor released: what's it mean?

2012-01-26T15:12:00.001-05:00

since I live in atlanta, it seems totally relevant to at least give a little blurb about a product release that went by the codename 'Atlanta'.

so what is advisor? it's basically a cloud service that "advises" on your server configuration. essentially you get data that's been scrubbed or normalized so that it's relevant to the alert you receive. anyway, it's designed to catch potential problems basically. it has some alerting features (email) that you can set up so that you don't have to go through the portal... though it does look kind of sharp. :)

if you have system center operations manager (opsmgr), you can think of this as kind of a complementary product. what advisor is not is a monitoring system. it's not real time. it doesn't handle the basic monitoring requirements that you get out of opsmgr.

if you want more information, check out this blog post: http://blogs.technet.com/b/server-cloud/archive/2012/01/26/system-center-advisor-released.aspx

there's also a 3-minute primer that you might find useful: http://www.microsoft.com/en-us/showcase/details.aspx?uuid=32e32209-71c4-43d2-b2ae-015598bf5b7d

and lastly, you can sign up for your own 60-day trial if you want to give it a spin: http://bit.ly/wK8iMn

if you have software assurance on sql server or windows server, you get the service for free.

listing the group membership of a computer in opsmgr [part 3]

2011-12-28T11:34:00.001-05:00

a long time ago, I posted about this stuff...

http://marcusoh.blogspot.com/2010/01/listing-group-membership-of-computer-in.html
http://marcusoh.blogspot.com/2010/01/listing-group-membership-of-computer-in_06.html

both of which were just works in progress... and as it turns out, completely wrong! I ran into this post this morning that simplified what I was doing down to a few lines. here it is:

$group = Get-MonitoringObject | Where { $_.DisplayName -eq "YourGroupName"}
$MonitoringClass = Get-MonitoringClass -Name "Microsoft.Windows.Computer"
$group.GetRelatedMonitoringObjects($MonitoringClass,"Recursive") | Select DisplayName

source: http://michielw.blogspot.com/2010/12/scom-get-nested-group-members.html

my 15 most popular posts of 2011

2011-12-27T20:34:00.000-05:00

in the interest of full disclosure, at some point, blogger or analytics did something... but I wasn't tracking page hits for the majority of the year so it's actually only about the last four months. unfortunately, blogger stats provide limited filtering for dates. I was hoping to have some good information to post something like this. oh well. it'll have to do. :)

moving to endpoint protection

2011-12-27T10:38:00.001-05:00

made the switch this morning to forefront endpoint protection -- or what will be known as system center endpoint protection. most of it went okay, but there were a couple of mcafee components that made the process PAINFUL! believe it or not, the antivirus component was not it.

the removal of the host intrusion prevention system (hips) and the mcafee agent itself were both more time-consuming than required, each with its own peculiarity. :/

removing hips

attempting to remove the hips agent may produce an error about needing to "disable self-protect mode." I am shamelessly stealing this from the site kmit4u because the instructions are quite near perfect and don't need revising:

Click Start, Run, type explorer and click OK.
Navigate to: C:\Program Files\McAfee\Host Intrusion Prevention\
Double-click McAfeeFire.exe.
Click Task, Unlock User Interface.
Type the unlock code, and select Administrator Password.
NOTE: By default, the unlock code is abcde12345.
After the user interface is unlocked, click the IPS Policy tab.
Deselect Enable Host IPS and Enable Network IPS. (The Firewall Policy can be disabled on its own tab.)
Select Task, Exit.
Credit: Knowledge Management IT for you: How to disable the Host Intrusion Prevention(IDS) Mcafee disable self-protect mode
Under Creative Commons License: Attribution

after following those steps, i was able to remove the hips agent!

removing the mcafee agent

while attempting to remove the mcafee agent, it kept telling me that "other products are still using it." uh no. I removed all other products! here's how you force it:

open up a cmd prompt.
navigate to the directory where frminst.exe is located. generally this is in the "common framework" directory of mcafee.
run the following:

frminst.exe /forceuninstall

now, smile happily as you have slain the hideous beast!

sccm: computers with names greater than 15 characters

2011-11-23T15:16:00.001-05:00

and coincidentally, blog posts with really long titles.

if you run into scenarios where you find that computers with longer than 15 characters are exhibiting strange issues in an application, you can root out these computers with configmgr or AD. while the computer itself may show a longer than 15 character machine name, the records for it in AD and configmgr show a truncated value.

this is interesting because the computer is registered with its long name in DNS. it can "interesting" when you see a truncated name that doesn't resolve (especially where WINS is involved and handles the resolution for netbios lookup adding further confusion.)

funny enough, where you root out this problem exists kind of on the same plane for both AD and configmgr. it's the DNS host name that gives it away! take a look a these screenshots -- from SCCM and AD, respectively:

(removed the computer names out of this screenshot)

so what do you do with this information, now that you know? well, let's look at ways of identifying it.

first of all, you can count out the characters to see how many are in the name. since dnsname and dnshostname have the same thing, we could use something like this:

((Get-QADComputer myComputer).dnshostname.split(".")[0]) | Measure-object -Character
Characters
----------
        16

now we can loop that into a general search for the entire domain:

Get-QADComputer -SearchRoot 'whatev you use' -SizeLimit 0 | where {($_.dnshostname.split(".")[0] | Measure-Object -Character).characters -gt 15}

if sql is your thing and you prefer to query configmgr, here's the sql query:

select distinct dnshostname0 
from dbo.v_GS_NETWORK_ADAPTER_CONFIGUR
where LEN(dnshostname0) > 15

hope that helps!

sccm: how old is my data?

2011-11-16T17:24:00.001-05:00

here is something I wrote up not too long ago for the benefit of some of my coworkers. it's nothing new and might be what most of you know, but I wanted to post it up just in case. it's a question I'm asked often... so it seemed like something worthy of sharing.

How old is my data?

Let me first summarize a few things. SCCM is generally configured to hold a set amount of data per given data type. In some cases, you may find that you have data that is older than the specified age value (90 today, 30 sometime soon). So what’s going on here? The short answer is, the data is still current. There are many things that comprise the composite object record. Let’s take my client for example. Here is a screen shot:

As you will notice, Agent Name, Agent Site, and Agent Time all contain numbers in brackets. The [#] is an index of the property, so to speak. For example, if I were interested in Heartbeat Discovery, I would read the values this way:

Agent Name[0] :      Heartbeat Discovery
Agent Site[0] :      XYZ
Agent Time[0] :      10/28/2011 7:31:05 AM

As you can see, this discovery record for my client actually contains three distinct discoveries that continue to supply it with fresh discovery information.

When will my data age out?

Data ages out of SCCM when all of the discoveries cease to meet the criteria. For example, in the screenshot above, if Heartbeat Discovery no longer provided discovery data, the agent time would continue to get older and older while the other two discoveries would continue to stay up to date. As long as there are up to date discoveries for a composite record, the entire record is considered fresh.

Why does this matter?

If you look at the agent names, you’ll notice three. Heartbeat Discovery is the agent itself providing discovery data once a day. As the name implies, it’s a heartbeat basically saying – I AM ALIVE! The other two discoveries are Active Directory based. Normally, you will see that old data persists because of the AD discoveries.

As long as the computer account persists in AD, the AD discovery will pick it up and post the new agent time. As you see, the time value for AD discovery is very close to 12:00:00 AM when most of the AD discoveries are kicked off. The heartbeat data is usually started on machine startup, on the other hand and every 24 hours after that.

enable verbose logging on a sccm server

2011-10-18T18:41:00.000-05:00

lots of posts about how to do this for clients. i found a few places where it indicated how to do this for servers.

(unfortunately, it wasn't on the holy grail of screen real estate for searches -- which is right before I have to scroll, ensuring i will never find it again. :] )

component registry path

navigate to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\COMPONENTS
under this path, select the component key name you're interested in such as SMS_DISCOVERY_DATA_MANAGER.
locate the dword value "Verbose Logging" and set the value to 1.

tracing registry path (turns on sql tracing)

navigate to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SMS\Tracing
locate the dword value "SqlEnabled" and set the value to 1.

under the path in step 1, select the component key name you're interested in such as SMS_DISCOVERY_DATA_MANAGER.
ensure the value "Enabled" is set to 1.

don't forget to cycle the SMS_EXECUTIVE service.

how to verify a server is running r2

2011-09-26T13:01:00.001-05:00

locating windows server 2003 running r2 is a bit of an obscure value. with windows server 2008, you can simply check the build number. unfortunately, in 2003, it's a little more hidden. here's a couple of ways of getting the information.

using configmgr

in configmgr, one place the information is contained is in the view v_gs_operating_system in the name0 column:

select sys.Name0, os.Name0
from v_r_system sys
inner join v_GS_OPERATING_SYSTEM os on os.ResourceID = sys.ResourceID
where os.Name0 like '%2003%R2%'
order by sys.Name0

using wmi

in wmi, the information is stored in win32_operatingsystem in the attribute "OtherTypeDescription" as indicated here in this msdn article.

OtherTypeDescription
Data type: string
Access type: Read-only
Additional description for the current operating system version.

Windows Server 2003 R2:  Contains the string "R2".
Windows Server 2003, Windows XP, Windows 2000, and Windows NT 4.0:  OtherTypeDescription is null.

you could use a powershell command like this to check a server:

Get-WmiObject -ComputerName myComputer -Class win32_operatingsystem | fl __server, caption, othertypedescription

resulting in this:

__SERVER             : myComputer
caption              : Microsoft(R) Windows(R) Server 2003 Standard x64 Edition
othertypedescription : R2

thanks erik and chip. :)

windows anti-virus exclusion list

2011-08-16T15:51:00.001-05:00

if you manage anti-virus in any form, you are already familiar with exclusion lists. this wiki article supplies links to just about any microsoft product you can think of and all of the exclusions you may need.

http://social.technet.microsoft.com/wiki/contents/articles/953.aspx

pro tip: if you subscribe to the article, you can get updates whenever more products are added.

sccm: integrating dell warranty data into configmgr

2011-08-10T08:19:00.001-05:00

UPDATE: forgot to add the preparatory step to randomize the datescriptran values.
UPDATE: greg ramsey asked me to post the script so here it is. click HERE. it's called MCO_DellWarranty.zip

this is a process for joining dell warranty information to your configmgr discovery and inventory database. challenges in the past have been that dell was not very forthcoming with providing an easy to query, easy to access warranty database. often times it had to be delivered to you in a spreadsheet or scraped off a webpage which made for inconsistent data updates and constant script failures.

history

in my time as an administrator, there have been many iterations of this that i have used. the first one was a little vbscript i wrote which would take a text dump i would get from dell, read in the values, and insert the records into a table. while this worked, it was most cumbersome getting dell to send me the files on a routine basis.

the second one, a bit more elaborate, was a powershell script i wrote which would go to a webpage with the specified serial number and scrape the information. this process worked relatively well, except that their webpage format changed constantly causing the script to error out more often than not.

background

hopefully this is the last iteration. this version is a play on the version created by scott keiffer. he created a module which utilizes a web service that dell set up. (you didn't know there was a web service?) along with the module, scott wrote up a powershell script that retrieves warranty data and inserts it into a sql table.

http://xserv.dell.com/services/assetservice.asmx

there's two ways to get the data included in his solution. one is done client side, the other server side. my preference is the server side method, but i wanted to change things up a bit so that it worked in a manner that i was more comfortable with. in particular, i wanted to implement it so that it would do the following:

do not drop the database table on every execution
only create the table when it doesn't already exist
update records that exist and insert new ones that do not
update stale records

almost every change i had to make was inside of the sql query statements so for the most part, it would have been fairly basic for most sql dbas. i, however, am NO dba hence the effort required for me was much greater than i care to admit. :-|

script changes

my script alterations were minor. i think you'll agree. here's a short run down with the why:

$tablequery (62) - this is a variable in which i'm holding a sql statement that creates the table IF the table DOES NOT already exist. this is handled in a sql if statement. (the original script dropped the table and created a new one each time.)
$dellquery (81) - this variable also stores a sql statement. the query looks for any system that is matches a dell profile AND does not have a warranty end date in the dellwarrantyinfo table OR has a datescriptran value older than a randomized number. (the original script simply pulled back any system that matched a dell profile.)
check for null value or empty arrays (102) - this is just a slight modification to adjust to the kind of data that is pulled in from the $dellquery change. i didn't correct the error statements yet when no systems are detected for updating. i guess i'm being lazy since it works just fine.
update statement instead of insert (114) - i added an if statement here to handle how sql manages the record. if it doesn't exist, insert it. if it does, update it. i figure the tradeoff in not having to gather the warranty data and create a new record each time it runs (in environments with a lot of systems) is fair for having to lookup whether the record exists or not.

preparatory step

as you can imagine, all of the datescriptran values would be identical for anything that ran during the first execution. this doesn't bode well for updating a group of systems at a time since they would all evaluate to having old warranty data at some point in the future. so... after your first execution, you will need to run the following sql statement which will randomize the date value to some degree:

UPDATE DellWarrantyInfo
SET [DateScriptRan] = DateAdd(dd, -(ABS(CHECKSUM(NEWID())) % 180 + 23), GetDate())

this makes the values somewhere less than or equal to 180 and greater than or equal to 23. you can move the values around to whatever is appropriate for you.

the script

ready to play? i cannot reiterate how IMPORTANT it is to test, test, test in a lab environment. there are too many environmental differences to run things blindly. i don't even know if it runs in my environment correctly. ;)

<#
DELL Warrenty Info - v1.0
Scott Keiffer, 2011 (skeiffer_A_T_cm_D_O_T_utexas_D_O_T_edu)
Marcus C. Oh, 8/10/2011 (marcus.oh_at_g_mail_dot_com)
    A few minor changes for the script to work in update mode

License: GPL v2 or later

Notes:
This script uses the get-dellwarranty function and SQL to get and store the warranty information for all dell systems in a specified ConfigMgr site. 

Disclaimer:
I am not responsible with any problems this script may cause you. 
#>
param ([parameter(Mandatory=$true)][String]$SQLServer, [parameter(Mandatory=$true)][String]$SiteCode)


# SQL function, 1 connection per command, may want to break that up but too lazy.
function Invoke-SqlQuery
{
    param(
    [Parameter(Mandatory=$true)] [string]$ServerInstance,
    [string]$Database,
    [Parameter(Mandatory=$true)] [string]$Query,
    [Int32]$QueryTimeout=600,
    [Int32]$ConnectionTimeout=15
    )

    try {
        $ConnectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;Connect Timeout=$ConnectionTimeout"
        $conn=new-object System.Data.SqlClient.SQLConnection
        $conn.ConnectionString=$ConnectionString
        $conn.Open()
        $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
        $cmd.CommandTimeout=$QueryTimeout
        $ds=New-Object system.Data.DataSet
        $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
        [void]$da.fill($ds)
        Write-Output ($ds.Tables[0])
    }
    finally {
        $conn.Dispose()
    }
}


# ---- Main ----

$ConfigMgrDatabase = "SMS_$SiteCode"
$warrantyDatabase = "DellWarrantyInfo"
$tableName = "DellWarrantyInfo"
$scriptPath=Split-Path -parent $MyInvocation.MyCommand.Definition

#Import warranty function
Import-Module "$scriptPath\DellWarrantyInfoFunction.psm1"

Write-Host "--- Dell Warranty Info SQL population script ---"

# create or recreate main table
Write-Verbose "Recreating main table..."


$TableQuery = @"
IF 
    Not Exists ( select * from sys.tables where name = 'DellWarrantyInfo' )     
BEGIN
    create table DellWarrantyInfo ( ResourceID int, ComputerName varchar(40), 
    DateScriptRan datetime, DaysLeft int, DellIBU varchar(16), 
    [Description] varchar(40), EndDate datetime, Provider varchar(16), 
    ServiceTag varchar(16), ShipDate datetime, StartDate datetime, 
    SystemType varchar(40), WarrantyExtended int); 
    grant select on DellWarrantyInfo to smsschm_users,webreport_approle
END
"@

Invoke-SqlQuery -ServerInstance $SQLServer -Database $ConfigMgrDatabase -Query $TableQuery
if(!$?) { Write-Error "There was a problem creating or recreating the main table" }

# get a list of dell computers in the site
Write-Host "Obtaining list of Dell systems..."



$DellQuery = @"
SELECT DISTINCT sys.netbios_name0 as ComputerName, 
    sys.ResourceID, bios.SerialNumber0 as ServiceTag
FROM v_R_System sys 
    LEFT OUTER JOIN DellWarrantyInfo as dw on sys.ResourceID = dw.resourceid    
    INNER JOIN v_GS_PC_BIOS as bios on bios.ResourceID = sys.ResourceID 
WHERE bios.Manufacturer0 like '%Dell%'
AND    ( dw.EndDate IS NULL
      OR dw.EndDate = '' 
      OR dw.DateScriptRan < DateAdd(dd, -(Round((407-322) * RAND() + 322,0)), GetDate()) )
"@

$dellSystems = Invoke-SqlQuery -ServerInstance $SQLServer -Database $ConfigMgrDatabase -Query $DellQuery
if(!$? -or !$dellSystems) { Write-Error "There was a problem receiving the list of Dell systems." }

#progressbar variables
$length = $dellSystems.count / 100
if ($length -eq 0) { $length=1/100 }
$count=1

#if array is of length 0 the foreach clause still runs with a null value. If check to fix.
if($dellSystems.count -gt 0 -OR $dellSystems.IsNull("ServiceTag") -eq $false -OR $dellSystems -ne $null)
{
    Write-Host "Gathering warranty information..."
    foreach ($dellSystem in $dellSystems)
    {
        #draws the progressbar based on the current count / (length/100)
        Write-Progress "Processing..." "$($dellSystem.ComputerName)" -perc ([Int]$count++/$length) 
        $WarrantyInfo = Get-DellWarranty $dellSystem.ServiceTag -ServiceTagInput
        #insert info into database
        if ($WarrantyInfo) {
        Write-Verbose "Issuing update on $($dellSystem.ComputerName)..."
        Invoke-SqlQuery -ServerInstance $SQLServer -Database $ConfigMgrDatabase -Query "

        IF
            Not Exists (select ResourceID from DellWarrantyInfo 
            where ResourceID = '$($dellSystem.ResourceID)')
        BEGIN
            INSERT INTO $tableName VALUES (
            '$($dellSystem.ResourceID)',
            '$($dellSystem.ComputerName)', 
            '$(Get-Date)',
            '$($WarrantyInfo.DaysLeft)',
            '$($WarrantyInfo.Region)',
            '$($WarrantyInfo.Description)',
            '$($WarrantyInfo.EndDate)',
            '$($WarrantyInfo.Provider)',
            '$($WarrantyInfo.ServiceTag)',
            '$($WarrantyInfo.ShipDate)',
            '$($WarrantyInfo.StartDate)',
            '$($WarrantyInfo.SystemType)',
            '$(if($WarrantyInfo.WarrantyExtended){1}else{0})' )

        END
        ELSE
            UPDATE $tableName
            SET [ComputerName] = '$($dellSystem.ComputerName)', 
                [DateScriptRan] = '$(Get-Date)',
                [DaysLeft] = '$($WarrantyInfo.DaysLeft)',
                [DellIBU] = '$($WarrantyInfo.Region)',
                [Description] = '$($WarrantyInfo.Description)',
                [EndDate] = '$($WarrantyInfo.EndDate)',
                [Provider] = '$($WarrantyInfo.Provider)',
                [ServiceTag] = '$($WarrantyInfo.ServiceTag)',
                [ShipDate] = '$($WarrantyInfo.ShipDate)',
                [StartDate] = '$($WarrantyInfo.StartDate)',
                [SystemType] = '$($WarrantyInfo.SystemType)',
                [WarrantyExtended] = '$(if($WarrantyInfo.WarrantyExtended){1}else{0})'
            WHERE [ResourceID] = '$($dellSystem.ResourceID)'"
        if(!$?) { Write-Error "There was a problem adding $($dellSystem.ComputerName) to the database" }
        }
    }
}
Write-Host "Script Complete."

the reports

thank you anonymous for pointing out that i completely forgot to include how to access the data. the script will create a database table inside of your sccm database. the reports included in the original script (noted under the background section) function exactly as indicated in scott's original post. just import them and use it.

have fun!

ds: logon request fails when groups > 1024

2011-07-26T10:51:00.001-05:00

it's probably not logical for you to do this to yourself and thus there is not much to worry about. however, through a series of nesting groups, you can very well do this without thinking much about it. anyway, by the nature of the fact that I am posting this ... means I ran into it. :(

for clarity, the group limitation is actually 1015 when you factor in well-known SIDs.

the error message

this is what you will see when attempting to log in:

The system cannot log you on due to the following error: During a logon attempt, the user’s security context accumulated too many security IDs. Please try again or consult your system administrator.

detecting the problem

if you want to see how many groups you (or some other user account) is a member of, use the following kinds of commands (may produce different results*):

powershell

Get-QADUser myuserid | Select-Object -ExpandProperty allmemberof | measure

cmd shell

dsquery user -samid myuserid | dsget user -memberof -expand | find /c /v ""

* when I ran them side by side, I had different counts. using dsquery, it lists all security groups. using get-qaduser, it seems to list only the groups by which you don't already have membership. basically, if someone created a group and added domain users as a nested group, get-qaduser doesn't show it.

more information

here are two great articles for this information:

http://support.microsoft.com/kb/906208

http://support.microsoft.com/kb/328889

getting the first and last day of the month in sql

2011-07-06T14:01:00.001-05:00

i was putting this together for a friend of mine so i thought i would post it since it seems like a pretty useful thing to have. should be self-explanatory.

-- first and last for previous month
DECLARE @FirstDayPrev DATETIME
DECLARE @LastDayPrev DATETIME
-- first and last for current month
DECLARE @FirstDayCurr DATETIME
DECLARE @LastDayCurr DATETIME
Set @FirstDayPrev = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0),101)
Set @LastDayPrev = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm, 0,getdate())+0, -1),101)
Set @FirstDayCurr = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm, 0,getdate())+0, 0),101)
Set @LastDayCurr = CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(mm, 0,getdate())+1, -1),101)
Select @FirstDayPrev, @LastDayPrev, @FirstDayCurr, @LastDayCurr

viewing internet headers of emails in outlook 2010

2011-06-10T08:06:00.001-05:00

this is so annoyingly obscure that I figured I'd point it out.

in outlook, open the message you're interested in. in the ribbon, locate the "tags" section and click the little arrow.

and there you have it...

opalis: operator console installation files

2011-06-06T14:32:00.001-05:00

with products like kelverion, you can make short work of installing the operator console. if you have gone through this, you know what an enormous amount of time this process takes.

if, however, you need all of the download files, I captured them and made them available here on my skydrive. the files are located under the OpalisOpConsole folder, split into 6 zip files. for this reason, make sure you are using an unzipping utility capable of piecing the content back together. beware! it's quite large and may take some time to download in its entirety.

open a command prompt to the directory in explorer

2011-06-02T09:09:00.001-05:00

this is a cool trick I picked up from my coworker who picked up from a presentation. basically, you can open a command prompt to the exact folder where your explorer is pointing to. here's how.

navigate to the folder you're interested in.

in the address bar of explorer, type "cmd" and hit enter.

this will launch a command prompt directly to the folder. perfect!

atlanta smug (atlsmug) coming up 4/22/11

2011-04-15T09:00:00.001-05:00

hi all.

in case you're not on the mailing list, I just wanted to let you know that we have another user group meeting coming up april 22, 2011. yes, that's good friday. no, we didn't realize it. :)

at any rate, if you can make it out, we'd be happy as punch to have you. if not, you can always join virtually. if you're so inspired, you can come out for part of it and join virtually for the other part. anyway, all the details and registration links are at http://www.atlsmug.org/1/post/2011/04/atlanta-systems-management-user-group-4222011.html.

here's a real quick agenda:

Presenters	Agenda	Time Start	Time End
	Breakfast	9:30	9:50
ATLSMUG	Opening	9:50	10:00
Dan Newton	v.Next Year – What You Need to Know About the Upcoming 2012 System Center Releases	10:05	11:00
Greg Cameron	Delivering and Managing the Private Cloud with System Center 2012	11:05	12:00
	Lunch Break	12:05	12:25
John Rush (Shavlik)	3rd-Party Software Patching Using SCCM and Shavlik SCUPdates	12:30	1:30
Duncan McAlynn	DCM vs ACS: An Audit Compliance Comparison	1:35	2:25
	Break	2:30	2:40
Baelson Duque / Barry Shilmover	Operations Manager 2012 Overview	2:45	4:05
ATLSMUG	Closing	4:05	4:15

sccm: client stuck downloading package with bit .tmp files in cache directory

2011-04-15T08:37:00.001-05:00

honestly, it's very early (by my standards). my creativity is not quite awake yet hence the very bad subject name of this post. I can't really find a good error message that would capture the essence of this problem. so... I guess you'll just have to read my rambling instead.

let's get started. when this problem spurs up it looks as if the client is attempting to download the package but never gets anywhere with it. what's the first thing any sccm admin does? read logs, yes. one of the best ways I've found of reading logs is to start by running a search against the logs directory and dumping out anything matching the package id, advertisement id, etc to a new txt file. this is what I found.

in the cas.log, the client is clearly getting the policy and location of the package.

Matching DP Location found 0 - \\mySMSServer\SMSPKGC$\XYZ00017\
Requesting content XYZ00017.1, size(KB) 60833, under context S-0-0-00-1111111111-1111111111-111111111-111111 with priority Low
Target location for content XYZ00017.1 is C:\WINDOWS\system32\CCM\Cache\XYZ00017.1.S-0-0-00-1111111111-1111111111-111111111-111111

datatransferservice.log is pretty busy too setting up directories.

Created directory 'C:\WINDOWS\system32\CCM\Cache\XYZ00017.1.S-0-0-00-1111111111-1111111111-111111111-111111\Lang/zh-TW'.

I think that gives us enough to go on. there's stuff in execmgr.log and policyevaluator.log, but we're all familiar with those by now. instead, I started looking at the conditions about this problem. so far I knew that the client was getting the policy and beginning the process to the point that it started creating directories but never going any further. I went to the directory highlighted above and noticed that the entire directory structure was there but no actual content. instead, there were a bunch of BIT*.TMP files.

I happened across this post on technet which explained quite vividly the problem at hand. I looked at the iis logs to determine if there was a transfer problem to the client. I found this in the logs (again by searching for the package id):

2011-04-14 15:16:28 192.168.0.2 HEAD /SMS_DP_SMSPKGC$/XYZ00017/Graphics/Gfxres.he-IL.resources - 80 - 192.168.0.3 Microsoft+BITS/6.7 404 7 0 60

okay, now we're getting somewhere. what do we with that though? well, the http codes specific to iis7 are in this kb article. in this case, I was looking for 404.7. here's how it maps back:

...
404.3 - MIME type restriction.
404.4 - No handler configured.
404.5 - Denied by request filtering configuration.
404.6 - Verb denied.
404.7 - File extension denied.
404.8 - Hidden namespace.
404.9 - File attribute hidden.
...

so, it's not your usual 404 (not talking about the ATL either).

as it turns out, the applicationHost.config file contains a section called <requestFiltering> that blocks certain extension types. by modifying this section of the .config file, it will allow the transfer of these file types over http -- exactly what BITS is using to transfer.

...
<requestFiltering>
                <fileExtensions allowUnlisted="true" applyToWebDAV="true">
                    <add fileExtension=".asax" allowed="false" />
                    <add fileExtension=".ascx" allowed="false" />
                    <add fileExtension=".master" allowed="false" />
                    <add fileExtension=".skin" allowed="false" />
                    <add fileExtension=".browser" allowed="false" />
                    <add fileExtension=".sitemap" allowed="false" />
                    <add fileExtension=".config" allowed="false" />
                    <add fileExtension=".cs" allowed="false" />
                    <add fileExtension=".csproj" allowed="false" />
                    <add fileExtension=".vb" allowed="false" />
                    <add fileExtension=".vbproj" allowed="false" />
                    <add fileExtension=".webinfo" allowed="false" />
                    <add fileExtension=".licx" allowed="false" />
                    <add fileExtension=".resx" allowed="false" />
                    <add fileExtension=".resources" allowed="false" />
                    <add fileExtension=".mdb" allowed="false" />
                    <add fileExtension=".vjsproj" allowed="false" />
                    <add fileExtension=".java" allowed="false" />
                    <add fileExtension=".jsl" allowed="false" />
                    <add fileExtension=".ldb" allowed="false" />
                    <add fileExtension=".dsdgm" allowed="false" />
                    <add fileExtension=".ssdgm" allowed="false" />
                    <add fileExtension=".lsad" allowed="false" />
                    <add fileExtension=".ssmap" allowed="false" />
                    <add fileExtension=".cd" allowed="false" />
                    <add fileExtension=".dsprototype" allowed="false" />
                    <add fileExtension=".lsaprototype" allowed="false" />
                    <add fileExtension=".sdm" allowed="false" />
                    <add fileExtension=".sdmDocument" allowed="false" />
                    <add fileExtension=".mdf" allowed="false" />
                    <add fileExtension=".ldf" allowed="false" />
   <add fileExtension=".ad" allowed="false" />
                    <add fileExtension=".dd" allowed="false" />
                    <add fileExtension=".ldd" allowed="false" />
                    <add fileExtension=".sd" allowed="false" />
                    <add fileExtension=".adprototype" allowed="false" />
                    <add fileExtension=".lddprototype" allowed="false" />
                    <add fileExtension=".exclude" allowed="false" />
                    <add fileExtension=".refresh" allowed="false" />
                    <add fileExtension=".compiled" allowed="false" />
                    <add fileExtension=".msgx" allowed="false" />
                    <add fileExtension=".vsdisco" allowed="false" />
                    <add fileExtension=".asa" allowed="false" />
...

in my case, it was .resources. make the change, initiate iis reset, and away you go.

misc: offering remote assistance in windows 7

2011-04-07T09:07:00.001-05:00

if you're looking for how to offer remote assistance in windows 7, here's a command line you can issue to do this:

%windir%\system32\msra.exe /offerra

sccm: content hash fails to match

2011-04-06T14:34:00.001-05:00

back in 2008, I wrote up a little thing about how distribution manager fails to send a package to a distribution point. even though a lot of what I wrote that for was the failure of packages to get delivered to child sites, the result was pretty much the same. when the client tries to run the advertisement with an old package, the result was a failure because of content mismatch.

I went through an ordeal recently capturing these exact kinds of failures and corrected quite a number of problems with these packages. the resulting blog post is my effort to capture how these problems were resolved. if nothing else, it's a basic checklist of things you can use.

detection

status messages

take a look at your status messages. this has to be the easiest way to determine where these problems exist. unfortunately, it requires that a client is already experiencing problems. there are client logs you can examine as well such as cas, but I wasn't even sure I was going to have enough material to blog so I didn't bother to capture the exact errors. :/ anyway, here are the specific elements:

type: error
component: software distribution
message id: 10057
detail: The program for advertisement "<advertisement id>" has failed because download of the content "<package id> - "<program name>" has failed. The download failed because the content downloaded to the client does not match the content specified in the content source. Possible causes: The content on the distribution point has been manually modified, or a local administrator on the computer has modified the content in the computer's hash. Solution: Refresh the content on the distribution point and retry the download.

and one more...

type: error
component: software distribution content access
message id: 10030
detail: Download of the content "<package id>" - "4" has failed. The download failed because the content downloaded to the client does not match the content specified in the source. Possible causes: The content on the distribution point has been manually modified, or a local administrator on the computer has modified the content in the computer's hash. Solution: Refresh the content on the distribution point and retry the download.

hashdir

ah, the elusive tool known as hashdir.exe. this tool analyzes a directory and returns the hash value. I used this to analyze the source directory and checked against a child site to determine if the hash result was properly returned. this is the usage info:

Usage: HashDir DirectoryName HashVersion [/FileList]
Example: HashDir d:\smspkgd$\FS100003 2
HashVersion: RTM = 1 and SP1 = 2

I assume anyone reading this post is on configmgr 2007 sp1 or greater, in which case, you want to use the value of 2 as shown in the example. by checking the source directory against a smspkg directory, you can get a very good sense of whether or not your files are out of sync. if you need hashdir, click HERE.

sql query

if you refer to the post I mentioned at the beginning of this one, you'll find a sql query that will (hopefully) enumerate where problems may exist. I referenced the dx21 method, but in truth, as it turns out after much later discovery, I should have credited the genius steve rachui.

SELECT * FROM PkgStatus WHERE Type=1 AND Status=1 AND ID='XYZ00234'

correction

hidden files

I wasn't aware that this could occur but there are enough posts that seem to indicate that hidden files will cause the hash to potentially mismatch. make sure you examine your source files to ensure they are not set with a hidden flag.

binary delta replication

sometimes this setting doesn't seem to play well with certain files. one quick remediation would be to turn this off and then update the package.

remove the pck file

at the child site, if you manually delete the pck file, the child site will have no choice but to bring down the pck file at the parent site. it's okay if you have ONE child site but loses its fun factor when you multiply it by a number of systems. for this reason, I do not recommend this... but alas it works.

modify pkgstatus and sourceversion in pkgstatus table

I mentioned that earlier post a couple of times. there's a sql query in there that will update the required fields for a package server. don't bother trying to set the value against a lot of different servers at once because it will fail. :) I don't know how I know this. I just do. trust me. so what do you do? well, I wrote this sql query that I kind of borrowed the guts out of from a post about opalis. it's a while loop that will go through a list of servers. it's kind of cool.

declare @str_objname sysname,
        @str_execsql nvarchar(256),
        @str_packageid nvarchar(10)

set     @str_packageid = '<package id here>'

declare cur_SCCMDPs cursor for
SELECT PkgServer FROM PkgStatus WHERE ID=''+@str_packageid+'' AND TYPE=1

open cur_sccmdps

fetch cur_sccmdps into @str_objname

while @@FETCH_STATUS = 0
begin
    set @str_execsql = 'WAITFOR DELAY ''00:00:02'' update PkgStatus set Status=2, SourceVersion=0 where ID= '''+@str_packageid+''' and Type=1 and PkgServer = '''+@str_objname+''''
    print @str_execsql
    exec sp_executesql @statement = @str_execsql
    fetch cur_sccmdps into @str_objname
end


deallocate cur_sccmdps

there's a wait delay on it so that while it's updating the records for each row, it'll have a chance to process it. I don't know if it's required but seemed like a good idea.

notes

it's critical to understand when to update versus refresh a package. if you make source file changes, you should be updating the package as refreshing will only refresh the contents to distribution points from the existing compressed files (.pck). refreshing is really only useful when you need to "repair" a distribution point. you'll notice that only updating will increment the package source version. more details are in this technet article.

another import thing to understand is what happens when you change a package from using compressed copy to source folder or vice versa. while the distribution points at the parent site will update, the child sites will not. this will most certainly cause a hash mismatch problem. any change like this requires that you update the distribution points. it's detailed in this technet article.

finally, if you are interested in what sccm is telling the world is the hash of the source, you can run this sql query to return the results of a package:

select PkgID, Name, Hash, NewHash from SMSPackages where PkgID = '<packageid>'

don't forget to TEST, TEST, TEST. what works for me may not work for you.

how to use dropbox to synchronize windows 7 sticky notes

2011-02-09T13:24:00.001-05:00

you may remember awhile back, I wrote up some steps on how to use windows live mesh to achieve sticky notes synchronization. live mesh, sync, and now mesh again, was a great product to use for this purpose because you could point to the folder and tell it to sync it. unfortunately, I didn't find it very reliable and by the comments I read, neither did a lot of my readers.

this morning, I got quite frustrated by a couple of things going on. first of all, no sync! second, digsby! sticky notes and digsby are two things I've come to rely on. there's some talk about a protocol change (or using old protocols or something like that) I read that pointed to digsby's msn having connection problems while using windows live mesh. while live mesh was running, msn would drop off. according to their blog, it's a known issue and will be revised in a future release.

anyway, so here I am. I decided to get rid of mesh in favor of something I've been using awhile and have come to love: dropbox. the challenge with dropbox was that I had to find a way to synchronize the content (yeah that's easy I know) without having the content only in the dropbox folder.

the first thing I thought of doing was writing a script to copy the dropbox content on sync detection. this seemed like OVERKILL when I found someone else's brilliant solution: ntfs junctions.

configuring dropbox and junctions

this is a rundown of the steps involved, which is pretty dang easy. so let's get started...

close your sticky notes application. make sure it's closed. if it's still running, you'll see the StikyNot.exe process running.
create a new directory called "sticky notes" somewhere in your dropbox folder. I just put mine in the root for ease.
navigate to the "sticky notes" directory on your computer, where you'll find a file called StickyNotes.snt. (the directory path is c:\users\<userid>\appdata\roaming\microsoft\sticky notes)
copy the StickyNotes.snt file to the dropbox folder you created.
now get rid of the original "sticky notes" directory.
from a cmd prompt, type the following to create your junction:

mklink /J "%APPDATA%\Microsoft\Sticky Notes" "<Full Dropbox Path>\Sticky Notes"

once you open up sticky notes again, it'll be pointing to the file in your dropbox folder courtesy of the junction you just created. :)

if you open the path to the original stick notes directory, you'll notice there's a shortcut icon on the folder to indicate it's a junction. if you open up the folder, you'll find yourself in your dropbox folder.

okay, now you're syncing sticky notes to dropbox... but what about another computer? simple. just run through the same steps above, except this time, don't worry about copying your stickynotes.snt file since it already exists in dropbox.

notes

there's one caveat I should mention. the synchronization can't take place while sticky notes is running. you'll want to close your sticky notes application every now and then so that dropbox has a chance to sync your changes.

this applies to computers receiving the files from dropbox as well. as long as sticky notes is running, it won't be able to sync the contents.

powershell: naming functions and cmdlets

2011-01-07T11:25:00.001-05:00

creating stuff in powershell and are curious about how to name it? you already know the verb-noun format, but what are the right verbs to use?

use the get-verb cmdlet!

Verb                            Group
----                            -----
Add                             Common
Clear                           Common
Close                           Common
Copy                            Common
Enter                           Common
Exit                            Common
Find                            Common
Format                          Common
Get                             Common
Hide                            Common
Join                            Common
Lock                            Common
Move                            Common
New                             Common
Open                            Common
Pop                             Common
Push                            Common
Redo                            Common
Remove                          Common
Rename                          Common
Reset                           Common
Search                          Common
Select                          Common
Set                             Common
Show                            Common
Skip                            Common
Split                           Common
Step                            Common
Switch                          Common
Undo                            Common
Unlock                          Common
Watch                           Common
Backup                          Data
Checkpoint                      Data
Compare                         Data
Compress                        Data
Convert                         Data
ConvertFrom                     Data
ConvertTo                       Data
Dismount                        Data
Edit                            Data
Expand                          Data
Export                          Data
Group                           Data
Import                          Data
Initialize                      Data
Limit                           Data
Merge                           Data
Mount                           Data
Out                             Data
Publish                         Data
Restore                         Data
Save                            Data
Sync                            Data
Unpublish                       Data
Update                          Data
Approve                         Lifecycle
Assert                          Lifecycle
Complete                        Lifecycle
Confirm                         Lifecycle
Deny                            Lifecycle
Disable                         Lifecycle
Enable                          Lifecycle
Install                         Lifecycle
Invoke                          Lifecycle
Register                        Lifecycle
Request                         Lifecycle
Restart                         Lifecycle
Resume                          Lifecycle
Start                           Lifecycle
Stop                            Lifecycle
Submit                          Lifecycle
Suspend                         Lifecycle
Uninstall                       Lifecycle
Unregister                      Lifecycle
Wait                            Lifecycle
Debug                           Diagnostic
Measure                         Diagnostic
Ping                            Diagnostic
Repair                          Diagnostic
Resolve                         Diagnostic
Test                            Diagnostic
Trace                           Diagnostic
Connect                         Communications
Disconnect                      Communications
Read                            Communications
Receive                         Communications
Send                            Communications
Write                           Communications
Block                           Security
Grant                           Security
Protect                         Security
Revoke                          Security
Unblock                         Security
Unprotect                       Security
Use                             Other

opalis: guidance on troubleshooting failed workflows

2011-01-05T18:21:00.001-05:00

as you move into deeper integration stories with opalis, it’s probable that you’re going to run into situations where the expected outcome isn’t quite as you dreamed.

now why is this? it’s usually because as you write the process, it isn’t completely determined what you’ll need. this manifests itself often for me (anyway) as security related problems. the most common reason this occurs is that you are designing as your own account and running your workflows as the opalis action service account.

so, if you would, allow me to offer a little guidance on this.

repeat to yourself: i am not the opalis action account. this has a profound effect in separating you from your delusion that the universe does not want you to succeed today. the point really is to remember that that testing console runs as the user launching the opalis client. here's a demonstration by pete.
review your audit history. it's object specific but sometimes you can glean problems occurring by viewing the audit history such as access denied errors when using the AD object.

check the system logs. while the logs can be quite noisy, it can provide a lot of value if the object problems are being logged. how do you know if they are? check! this post has the log locations. the policymodule*.log files are the ones to read.
minimize confusion by documentation. document the permissions required for the action server with passionate fervor! (duh) i can't even begin to explain how many times i was changing things in the wrong place.
measure once, cut twice or measure twice, cut once – just measure. go back and assess the various places where a credential conflict might cause problems. generally, when connections are defined, you define the credentials as well to connect to those environments. while these may execute as the defined account (connections to opsmgr for example), other objects that were not defined will execute as you (in the testing console) and as the opalis action service account (in running policies).
put on your running shoes. if you’re working with nested workflows, chase your log histories! review the log histories of all your workflows. while not as valuable as being able to run them in the testing console, it still pulls back some data that can be useful.
execute each workflow individually if required. sometimes log history isn't enough. it can be useful to separate the workflows and run them through the testing console. remember your identity crisis. you're running these tests as YOU, not the action server.
append line object is your friend. back when we scripted in that archaic language known as vbscript, there was this concept of echoing. you chiseled commands like wscript.echo "i am so cool" into a rock wall and inside of your cmd shell, out it would come (and coincidentally, inside the cmd shell is also where you're cool). this is akin to using echo statements to capture places that you are in your script. write to log. log often. log is your copilot.
be something you’re not. try running the opalis client as the action account. this presents its own challenges though as you will be required to grant the action account access to your policies.
violate all security principles. where at all possible, just put the action account into the domain administrators and rid yourself of all the pesky permissions issues. I AM JOKING! do not ever do this.

for real world context, i wish to say that i ran into every one of these things. and probably twice. (except the last one. thought about it. a lot. not dumb enough to do it though).

opalis: working around limitations with workflow objects and link operators

2010-12-14T16:30:00.000-05:00

UPDATE: pete zerger was kind enough to point out that sometimes i don't make sense, and i missed a very obvious point in the documentation. since the post itself is still useful, i didn't just scrap it. :) instead, i added an addendum.

if you've been working with opalis long enough, you ~~might~~ will find that there are moments when hacks are required to get you from one point to the next point. i've been experimenting a lot with nested workflows. it's like evolving from inline scripting to scripting with functions and/or subs.

i discovered that when using trigger policy to run a nested workflow, a bizarre thing happens. even if the nested workflow executes with an error, the status returned by the calling trigger policy object is "success". it didn't make sense at first until i realized that by all accounts, the trigger policy did execute successfully.

well, there's a problem with this. if it comes back as success, even though something failed, the policy will continue on down the path unless you tell it otherwise. enough of that. let's talk specifics about my scenario.

the workflow i created was designed to do one thing: usher alerts from opsmgr into tickets in remedy. since remedy is divided into many different operating queues, i had to consider how to create tickets into the correct queues. i decided to try it based on computer group membership.

in order to get the group membership, i had to query the opsmgr database. i decided to push that into a nested workflow so that it could be reused in other workflows at some later point. the information retrieved from the nested workflow would be the basis of information fed to a text file. the master workflow could reference the text file to search through for cross checking names.

now what would happen if the database failed to query, and the associated text file never filled with any data? if you're cross checking the alerts against an empty text file, chances are you will never have a match and as such no tickets generated.

but if opalis is returning a success on the nested workflow, how do you know the query is failing? that seems simple. if the published data returned from the nested workflow is empty, then obviously the query failed. too bad the link operators don't have any filters for stuff like "is empty" or "is not empty".

all isn't lost though. to get the effect that we want, we simply have to know what to look for. going to the nested workflow, we can use the query database object status as our criteria to branch appropriately. if successful, the publish policy data object writes the expected server list. if it runs into a warning or failure, we publish static text to a different publish policy data object in the form of "FAILED".

back in the master workflow, we can now use the link operator to cull out anything that tries to come through with "FAILED". if it matches the include filter, the policy processing stops.

addendum:

keep in mind that link operators do not have an "AND" operation. instead the filters are evaluated as "OR" expressions. however, the include/exclude tabs are separate so mixing and matching is a possibility, assuming you have the right content coming through.

in the opalis client user guide, the trigger policy object section has a table that states this description for the child policy status: the status that was returned by the child policy. it's important to clarify that the default behavior of a link operator coming from the trigger policy object is to set the filter to look for anything coming from trigger policy itself to "success".

if you're looking for the status coming from the child policy, you should change the link operator filter to look for something like this:

opalis: multiple policy instances running in the operator console

2010-12-08T05:08:00.000-05:00

here is an interesting and rather complex problem i experienced with opalis. honestly, this thing has probably been going on since i built the environment. however, i have only begun working with nested workflows which really helped flush it out.

summary

essentially, when a master workflow launches a sub workflow, the policy spins in the background and never does anything. another indication of the problem is that the summary of policies indicate a high number of running instances. lastly, the problem appears to be environmental since only one opalis instance has the problem.

here is a view of the operator console with this problem.

troubleshooting

the first thing i prefer to do in scenarios like these is to try to recreate the problem. that wasn't difficult on the server having the problem. the operator console shows a high number of instances, sub-workflows hang indefinitely, etc. when i tried it in a different environment, replicating the same master/sub nested test, it worked fine. i decided it must be my policy and exported the test version into my broken opalis instance, ran it, and got the same result -- it hung.

i decided to look down another path into the running instances count to see if that was part of the problem. i began by restarting the opalis service, hoping instances that were trapped somewhere would free themselves and disappear. not a chance! next, i started looking through the process list and found policymodule.exe which appears to be the running process that a policy resides in. i figured with that many policy instances running, surely, i was way over the concurrent policy instance maximum. unfortunately, i only found two of these processes which matched up to the two running policies as seen in the screenshot above.

since that looked fine, i went back to sifting the logs (yeah, i mistakenly dismissed it the first time around).i found this statement in the logs (full snip below)[3] which had me scratching my head:

2010-12-07 15:12:38 [1640] 1 Opalis Event: Frequent DB errors

that information wasn't very telling but eventually a little further down in the logs, i found this error:

<Param>The EXECUTE permission was denied on the object 'sp_UnpublishPolicyRequest', database 'Opalis', schema 'dbo'.</Param>

apparently, i had the wrong set of permissions. according to the administrator guide, the action server requires "part of database users group on the datastore computer". going by this guidance, the action server service account was granted db_datareader and db_datawriter.

this made perfect sense. all of my other environments were ones i set up simply to test. with this broken one, it was set up as a dev environment utilizing best practices such as using databases from people who know how to run them (not me). adding to that, using the minimal level of rights required is followed. by the log output, you can see i missed one of those such rights, however. i did not grant the "execute" permissions that the account required.

this seems to speak to the root of the problem but in my research found that it did not correct the running instances count that appears in the operator console. looking through the database views, i found dbo.policies_view (which details a lot more than what you see in the operator console, by the way). i opened this view and found "runningpolicyinstances" column which contained the exact counts i saw in the operator console:

the design of the dbo.policies_view indicated very clearly how this column was being constructed.

(SELECT COUNT(*) AS Expr1 FROM dbo.POLICYINSTANCES WHERE (dbo.POLICIES.UniqueID = PolicyID) AND (TimeEnded IS NULL))

so basically, if the timeended value in the dbo.policyinstances table is null, it counts them up and displays it here. sure enough, the table contained a high number of rows with null values. i searched through all of the stored procedures to try to locate an entry that indicated inserting values into policyinstances but in almost every case, data was drawn from the table and rarely written to. to add to this problem, log purging doesn't delete entries where the timeended value is null. i suspect this is because it believes the policies haven't finished executing yet. thus, these counts would have never gone away on their end.

resolution

:: database ::

the problem with the database permissions can be fixed in one of two ways:

grant the account db_owner rights
grant the account execute rights on all stored procedures [1]

once either one of these is done, the error in the opalisactionservice logs stop generating. i would be cautious with this. first, granting db_owner is probably granting rights beyond what the account actually needs. second, if you choose to just grant rights to the stored procedures, it may not be the entire set of rights required. my testing has been limited so far, and thus i may find in doing so that i run into other issues [2].

:: operator console ::

correcting the summary count will require some changes to the policyinstances table. obviously going in and messing around with tables is not going to be supported by microsoft so proceed at your own risk. my environment is a testing environment so it makes sense for me. you may want to call microsoft support.

to begin, i shutdown all running policies so that any timeended date stamps would write in as necessary (allowing me to avoid unnecessarily changing data that didn't need modification). afterwards, i ran the following sql query to set the timeended value to the current timestamp.

update dbo.policyinstances 
set [timeended] = getdate()
where timeended is null

this simple query adds the current datetime to the timeended field where the value is null. as shown in the screenshot below, the instance summary now displays the correct count.

addendum

[1] if you're interested in the sql query to provide execute permissions for the action server service account, here it is. many thanks to patrick for whipping this up!

declare @str_objname sysname,
        @str_execsql nvarchar(256)
 
declare cur_spname cursor for 
select name from sys.procedures
 
--select schema_name(schema_id), name from sys.procedures
 
open cur_spname 
 
fetch cur_spname into @str_objname
 
while @@fetch_status = 0
begin 
    set @str_execsql = 'grant execute on '+@str_objname+' to [myServiceAccount];'
    print @str_execsql 
    exec sp_executesql @statement = @str_execsql
    fetch cur_spname into @str_objname
 
end
 
deallocate cur_spname

here is what the query ended up executing:

grant execute on sp_insertevent to [myServiceAccount];
grant execute on sp_GetLogEntriesForDelete_FilterByEntries to [myServiceAccount];
grant execute on sp_GetLogEntriesForDelete_FilterByDays to [myServiceAccount];
grant execute on sp_GetLogEntriesForDelete_FilterByEntriesAndDays to [myServiceAccount];
grant execute on sp_CustomLogCleanup to [myServiceAccount];
grant execute on sp_PublishPolicy to [myServiceAccount];
grant execute on sp_UnpublishPolicyRequest to [myServiceAccount];
grant execute on sp_UnpublishPolicy to [myServiceAccount];
grant execute on sp_DeleteTreeData to [myServiceAccount];
grant execute on sp_FindTreeInsertionPoint to [myServiceAccount];
grant execute on sp_InsertTreeData to [myServiceAccount];
grant execute on sp_MoveTreeBranch to [myServiceAccount];
grant execute on sp_StopAllRequestsForPolicy to [myServiceAccount];
grant execute on sp_StopAllRequests to [myServiceAccount];

[2] if you want, you can follow this forum post. looking for the complete list of database rights required by the action server service account: http://social.technet.microsoft.com/Forums/en-US/opalisv5v6/thread/faf9c8a0-a1b2-4442-8391-1ff738826f28

[3] here's the full log snippet:

2010-12-07 15:12:38 [1640] 1 Opalis Event: Frequent DB errors

2010-12-07 15:12:38 [1640] 1 Exception caught in void __cdecl OpalisEventDeliveryStrategyComposite::sendAndTraceExceptions(const class OpalisEventDeliveryStrategy &,const class OpalisEvent &,const class std::basic_string<unsigned short,struct std::char_traits<unsigned short>,class std::allocator<unsigned short> > &)
C:\AutomatedBuild\IS5.Platform\Branches\6.2_Sanitized\Platform\OpalisEventDelivery\OpalisEventDeliveryStrategyComposite.cpp(83):
<Exception>
<Type>Opalis::Exception</Type>
<Location>
void __cdecl OpalisEventDeliveryStrategyComposite::sendAndTraceExceptions(const class OpalisEventDeliveryStrategy &,const class OpalisEvent &,const class std::basic_string<unsigned short,struct std::char_traits<unsigned short>,class std::allocator<unsigned short> > &)
C:\AutomatedBuild\IS5.Platform\Branches\6.2_Sanitized\Platform\OpalisEventDelivery\OpalisEventDeliveryStrategyComposite.cpp(80)
</Location>
<MsgCode>Cannot deliver Opalis Event</MsgCode>
<Params>
<Param>EW</Param>
</Params>
<Prev><Exception>
<Type>Opalis::Exception</Type>
<Location>
void __cdecl StorageCallExecutor::throwChained(const class Opalis::Exception &)
C:\AutomatedBuild\IS5.Platform\Branches\6.2_Sanitized\Platform\OpalisActionService2\StorageCallExecutor.cpp(47)
</Location>
<MsgCode>SCE: ActionServerStorage call failed</MsgCode>
<Prev><Exception>
<Type>Opalis::Exception</Type>
<Location>
long __stdcall CODBDataStore::ReportEventW(struct tagVARIANT)
C:\AutomatedBuild\IS5.Platform\Branches\6.2_Sanitized\Platform\DBDataStore\ODBDataStore.cpp(6002)
</Location>
<MsgCode>_com_error</MsgCode>
<Params>
<Param>Unknown error 0x800A0CC1</Param>
<Param>Item cannot be found in the collection corresponding to the requested name or ordinal.</Param>
<Param>-2146825023</Param>
</Params>
</Exception></Prev>
</Exception></Prev>
</Exception>

2010-12-07 15:12:38 [1640] 1 Exception caught in void __thiscall StorageCallExecutor::onStorageException(class Opalis::Exception &,bool)
C:\AutomatedBuild\IS5.Platform\Branches\6.2_Sanitized\Platform\OpalisActionService2\StorageCallExecutor.cpp(120):
<Exception>
<Type>Opalis::Exception</Type>
<Location>
void __thiscall PublishedPoliciesManager::removePolicyRequest(const class _bstr_t &,const __int64 &)
C:\AutomatedBuild\IS5.Platform\Branches\6.2_Sanitized\Platform\DBDataStore\PublishedPoliciesManager.cpp(850)
</Location>
<MsgCode>Cannot unpublish policy</MsgCode>
<Prev><Exception>
<Type>Opalis::Exception</Type>
<Location>
void __thiscall PublishedPoliciesManager::removePolicyRequest(const class _bstr_t &,const __int64 &)
C:\AutomatedBuild\IS5.Platform\Branches\6.2_Sanitized\Platform\DBDataStore\PublishedPoliciesManager.cpp(838)
</Location>
<MsgCode>No more attempts</MsgCode>
<Prev><Exception>
<Type>Opalis::Exception</Type>
<Location>
void __thiscall DBAccessor::execute(const class std::basic_string<unsigned short,struct std::char_traits<unsigned short>,class std::allocator<unsigned short> > &) const
C:\AutomatedBuild\IS5.Platform\Branches\6.2_Sanitized\Platform\DBDataStore\DBAccessor.cpp(37)
</Location>
<MsgCode>_com_error</MsgCode>
<Params>
<Param>IDispatch error #3081</Param>
<Param>The EXECUTE permission was denied on the object 'sp_UnpublishPolicyRequest', database 'Opalis', schema 'dbo'.</Param>
<Param>-2147217911</Param>
</Params>
</Exception></Prev>
</Exception></Prev>
</Exception>

powershell: using convertfrom-csv

2010-12-07T19:35:00.000-05:00

a friend of mine pointed out this really cool cmdlet called convertfrom-csv today. using it, you can immediately create a PSCustomObject. pretty cool! as a practical example, you can dump out repadmin and use the object to work with data any way you see fit.

[96] {D:\temp} > $foo = convertfrom-csv @(repadmin /showrepl * /csv)
[96] {D:\temp} > $foo | gm
  TypeName: System.Management.Automation.PSCustomObject

Name                MemberType   Definition
----                ----------   ----------
Equals              Method       bool Equals(System.Object obj)
GetHashCode         Method       int GetHashCode()
GetType             Method       type GetType()
ToString            Method       string ToString()
Destination DC      NoteProperty System.String Destination DC=myDC
Destination DC Site NoteProperty System.String Destination DC Site=mySite
Last Failure Status NoteProperty System.String Last Failure Status=0
Last Failure Time   NoteProperty System.String Last Failure Time=0
Last Success Time   NoteProperty System.String Last Success Time=2010-12-07 14:21:39
Naming Context      NoteProperty System.String Naming Context=DC=myDomain,DC=com
Number of Failures  NoteProperty System.String Number of Failures=0
showrepl_COLUMNS    NoteProperty System.String showrepl_COLUMNS=showrepl_INFO
Source DC           NoteProperty System.String Source DC=myDC2
Source DC Site      NoteProperty System.String Source DC Site=mySite
Transport Type      NoteProperty System.String Transport Type=RPC