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>

2 comments:

  1. The best no deposit bonuses 2021 - DrmCD
    These 경기도 출장마사지 casinos offer 부산광역 출장샵 generous no 보령 출장마사지 deposit bonus and you won't lose 안성 출장마사지 any cash if you play slots, blackjack, or other table games 안산 출장마사지 at the casino.

    ReplyDelete
  2. Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place.. gps equipment tracking

    ReplyDelete