Wednesday, August 7, 2013

Tracking BSOD under SCCM 2007

I just ran into a problem for a client who started seeing a number of blue screens of death (BSOD) after upgrading to a particular version of Websense Endpoint that interacts with McAfee.  The actual error seems to be a problem with MUP.SYS based off of a couple knowledge base articles (Microsoft's and Websense's article)


These 2 reports allow analysis of file modification dates based on Software Scan Inventory information.

Here is the SQL query of the top level view.  I usually build my reports in SQL Server Studio Management and then copy them into a report.  You can see where I separate out the parameter variables in the top few lines of the query.  These top few lines are not copied into the report.

<code>
declare @variable varchar(20)
set @variable = 'Mini%.dmp'
declare @datelimit date
set @datelimit = '2013'

select 
DATEADD(day, datediff(day, 0, SF.FileModifiedDate), 0) as 'Day'
, COUNT(distinct sys.Name0) as 'Count of Computers'
, COUNT(distinct SF.FilemodifiedDate) as 'Count of Files'
, (COUNT(distinct SF.FilemodifiedDate) + 0.00) / COUNT(distinct sys.Name0) as 'FilesPerComputer'
, @variable as [File Search Term]
from v_GS_SoftwareFile SF
inner join v_R_System sys on SF.ResourceID=sys.ResourceID
where FileName Like @variable and dateadd(YEAR, datediff(YEAR, 0, SF.FileModifiedDate),0) = @datelimit
GROUP BY 
DATEADD(day, datediff(day, 0, SF.FileModifiedDate), 0)
Order By Day
</code>

Here is the SQL query of the drill down or daily view.  I usually build my reports in SQL Server Studio Management and then copy them into a report.

<code>
declare @variable varchar(20)
set @variable = 'Mini%.dmp'
declare @datelimit date
set @datelimit = '7/15/2013'


select
'Scanned Computers' as Label
, COUNT(ResourceID) as Total
from v_GS_LastSoftwareScan
where LastScanDate >= @datelimit

Union

select
'Total Computers' as Label
, COUNT(ResourceID) as Total
from v_GS_LastSoftwareScan

select 
DATEADD(day, datediff(day, 0, SF.FileModifiedDate), 0) as 'Day'
, sys.Name0
, COUNT(distinct SF.FilemodifiedDate) as 'Count of Files'
, lss.LastScanDate
from v_GS_SoftwareFile SF
inner join v_R_System sys on SF.ResourceID=sys.ResourceID
inner join v_GS_LastSoftwareScan lss on SF.ResourceID=lss.ResourceID
where FileName Like @variable and 
dateadd(day, datediff(DAY, 0, SF.FileModifiedDate),0)  = @datelimit
GROUP BY 
DATEADD(day, datediff(day, 0, SF.FileModifiedDate), 0)
, sys.Name0
, lss.LastScanDate
Order By [Count of Files] desc
</code>


I don't know if there is an easy way to upload this file here but if you copy between the mof tags, you can create a text document with a mof extention
<mof>
// *********************************************************************************
//
// Created by SMS Export object wizard
//
// Wednesday, July 17, 2013 created
//
// File Name: BSOD Reports.MOF
//
// Comments :
//
// These 2 reports allow analysis of file modification dates based on Software Scan
//  Inventory information.
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Software - Files";
Comment = "Displays the number of computers that a single specified file is inventoried on";
DrillThroughColumns = {};


GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "JJR - Count computers with a filename";
NumPrompts = 2;
RefreshInterval = 0;
ReportGUID = "{CAB3BCC6-FEC0-4BE9-8BD0-BD703D89E8B1}";
ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "Mini%.dmp";
PromptText = "File Name (Like Mini%.dmp)";
SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct FileName from v_SoftwareFile order by FileName
\n else
\n  select distinct FileName from v_SoftwareFile
\n  WHERE FileName like @__filterwildcard
\n  order by FileName
\nend";
VariableName = "variable";
}, 
instance of SMS_ReportParameter
{
AllowEmpty = TRUE;
DefaultValue = "2013";
PromptText = "Year (i.e. 2013)";
SampleValueSQL = "Select distinct
\nYEAR(FileModifiedDate) 
\nfrom v_GS_SoftwareFile
\ngroup by YEAR(FileModifiedDate)";
VariableName = "datelimit";
}};
SecurityKey = "";
SQLQuery = "select 
\n\tDATEADD(day, datediff(day, 0, SF.FileModifiedDate), 0) as 'Day'
\n\t, COUNT(distinct sys.Name0) as 'Count of Computers'
\n\t, COUNT(distinct SF.FilemodifiedDate) as 'Count of Files'
\n\t, (COUNT(distinct SF.FilemodifiedDate) + 0.00) / COUNT(distinct sys.Name0) as 'FilesPerComputer'
\n\t, @variable as [File Search Term]
\nfrom v_GS_SoftwareFile SF
\ninner join v_R_System sys on SF.ResourceID=sys.ResourceID
\nwhere FileName Like @variable and dateadd(YEAR, datediff(YEAR, 0, SF.FileModifiedDate),0) = @datelimit
\nGROUP BY 
\n\tDATEADD(day, datediff(day, 0, SF.FileModifiedDate), 0)
\nOrder By Day";
StatusMessageDetailSource = FALSE;
UnicodeData = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Software - Files";
Comment = "Displays the number of computers that a single specified file is inventoried on";
DrillThroughColumns = {};
GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "JJR - Count computers with a filename drilldown";
NumPrompts = 2;
RefreshInterval = 0;
ReportGUID = "{E99EFE30-27DA-4594-8105-A37E2568E4B3}";
ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = TRUE;
DefaultValue = "";
PromptText = "Day Limit (Like 2/25/2013)";
SampleValueSQL = "";
VariableName = "datelimit";
}, 
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "Mini%.dmp";
PromptText = "File Name (Like Mini%.dmp";
SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct FileName from v_SoftwareFile order by FileName
\n else
\n  select distinct FileName from v_SoftwareFile
\n  WHERE FileName like @__filterwildcard
\n  order by FileName
\nend";
VariableName = "variable";
}};
SecurityKey = "";
SQLQuery = "select
\n\t'Scanned Computers' as Label
\n\t, COUNT(ResourceID) as Total
\nfrom v_GS_LastSoftwareScan
\nwhere LastScanDate >= @datelimit
\n
\nUnion
\n
\nselect
\n\t'Total Computers' as Label
\n\t, COUNT(ResourceID) as Total
\nfrom v_GS_LastSoftwareScan
\n
\nselect 
\n\tDATEADD(day, datediff(day, 0, SF.FileModifiedDate), 0) as 'Day'
\n\t, sys.Name0
\n\t, COUNT(distinct SF.FilemodifiedDate) as 'Count of Files'
\n\t, lss.LastScanDate
\nfrom v_GS_SoftwareFile SF
\ninner join v_R_System sys on SF.ResourceID=sys.ResourceID
\ninner join v_GS_LastSoftwareScan lss on SF.ResourceID=lss.ResourceID
\nwhere FileName Like @variable and 
\n\tdateadd(day, datediff(DAY, 0, SF.FileModifiedDate),0)  = @datelimit
\nGROUP BY 
\n\tDATEADD(day, datediff(day, 0, SF.FileModifiedDate), 0)
\n\t, sys.Name0
\n\t, lss.LastScanDate
\nOrder By [Count of Files] desc";
StatusMessageDetailSource = FALSE;
UnicodeData = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****
</mof>

Thursday, July 11, 2013

WSUS Bug in Downstream Repllicas

A co-worker found this on one of our servers and was able to follow the SQL commands to fix an issue we have been fighting with for a while.

http://www.flexecom.com/wsus-replica-server-fails-to-synchronize/

This was a really good find.

On an unrelated note, I helped another co-worker rebuild his SUSDB because it was originally used to deploy WSUS approvals and migrated to SMS approvals without cleaning the database.

Wednesday, January 30, 2013

Slightly Modified Report

Here is a modified Site system roles and servers report that can show all servers:
if @variable != ''
BEGIN
SELECT sys.SiteCode, sys.ServerName,
case when sys.RoleName='SMS Distribution Point'
then case when dp.IsPeerDP=1 then 'SMS Branch Distribution Point'
else 'SMS Standard Distribution Point' end
else RoleName end as 'RoleName'
FROM v_SystemResourceList as sys
left join v_DistributionPointInfo as dp
on sys.NALPath = dp.NALPath
WHERE sys.SiteCode  LIKE @variable
Order by sys.SiteCode, sys.ServerName, RoleName
END
ELSE
BEGIN
SELECT sys.SiteCode, sys.ServerName,
case when sys.RoleName='SMS Distribution Point'
then case when dp.IsPeerDP=1 then 'SMS Branch Distribution Point'
else 'SMS Standard Distribution Point' end
else RoleName end as 'RoleName'
FROM v_SystemResourceList as sys
left join v_DistributionPointInfo as dp
on sys.NALPath = dp.NALPath
--WHERE sys.SiteCode  LIKE @variable
Order by sys.SiteCode, sys.ServerName, RoleName END
To create a modified report, it always helps to build the report in SQL Management Studio. Most of the time, you just need to add 2 lines at the top of the query to accomidate for the SCCM prompt for some reports like the one shown above.

DECLARE @variable varchar(30)
SET @variable = ''
Then you can add whatever information to your variable (in this case called @variable)

Thursday, January 17, 2013

Quick and Dirty Powershell

I didn't want to use psexec and when I tried to use it, I failed my first attempt.

Non-Destructive test to make sure I have the correct syntax:

Get-Content .\SMSReportResults.csv | ForEach-Object {If (test-connection $_ -Count 1 -quiet) {get-childitem \\$_\c$\windows\system32\grouppolicy\machine\registry.pol}}
Destructive command that will delete the registry.pol file which will be recreated next time SCCM checks for updates
Get-Content .\SMSReportResults.csv | ForEach-Object {If (test-connection $_ -Count 1 -quiet) {remove-item \\$_\c$\windows\system32\grouppolicy\machine\registry.pol}}

Quick explanation on what every step does:

  • Get-Content: Reads the lines from file as an array
  • ForEach-Object: Takes the array and does {}
  • If (test-connection $_ -Count 1 -quiet): Quick ping test (1 ping) to see if device is online returns true if online
  • get-childitem: is equivalent to dir or ls
  • remove-item is equivalent to del or rm
Curious to see if there is a way to create a wrapper powershell script that will allow someone used to psexec to use the same context for powershell equivalents.

Friday, January 4, 2013

Poor Man's MRTG on Windows 2008

I'm trying to see what impact the "Configuration Manager 2007 Client Status Reporting" tool may give if it is enabled for 20K+ workstations running from a central site with 2 other primary sites and loads of secondary sites beneath the primaries.  When I came on board, the tool was already configured to ping all inactive clients for the entire site hierarchy without any perceived negative impact.

Once the data is collected don't forget to click on the graph tool
My question and purpose was to figure out if we could enable the policy request from the management points without greatly impacting the environment or if we needed to re-architect the tool to install on each primary site and have the tool only focus on it's own site.  This is the recommended design if you have slow network connectivity between your sites.

Of course slow network connectivity between sites is always subjective and changes over time.  Today a 128k WAN link is slow, whereas it was screaming fast in the 1990s.  What I'm trying to say, is that it always depends on what else is trying to be pushed through your network connection. That means that it is time to measure network bandwidth usage before and after implementation.

I don't have a working relationship with anyone on the networking team, otherwise I would ask someone there to show me the link usage using their reports.  What I really wanted to do was run my own MRTG on just the server to see if I could find out how much more bandwidth this new setting may inflict.  Since MRTG is a perl script and this is on a Windows computer, I wanted to see if I could somehow make Performance Monitor work the same way as MRTG which checks the SNMP settings on routers every 5 minutes by default.

I was lucky and found out that the server already had the SNMP service installed which is necessary in order to monitor the Network Interfaces.  I then created a new Data Collector Set which you can see from the steps that I took below.  I found the Performance tools to watch from the Microsoft site: Monitor and Tune Network Bandwidth and Connectivity

What I like best about this method was that it only took 704 KB to monitor overnight.  This is something you could set to run for 3 days at a time without any concern.

Make sure to choose: Create manually (Advanced)

These options are only available once you install Microsoft's SNMP service

I set the collection interval to 5 minutes which matches MTRG's default

I added a default maximum of 3 day collection