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>