Re: Report Question
- From: Kim Oppalfens <kim@xxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 14 Sep 2005 22:21:41 +0200
In article <9B33ECBF-6B86-43EF-A0CD-913B80A02433@xxxxxxxxxxxxx>,
ThreeToedSloth@xxxxxxxxxxxxxxxxxxxxxxxxx says...
> Is it possible to create a software metering report that accepts a begin and
> end date? I'd like to copy the 'users that have run a particular program'
> report and get it to report between 2 dates rather than monthly.
>
> Regards,
>
> Marc
>
Nope, can't be done, absolutely totally impossible. ;-)
//
************************************************************************
*********
//
// Created by SMS Export object wizard
//
// Monday, July 25, 2005 created
//
// File Name: swmtrreport.mof
//
// Comments :
//
//
//
************************************************************************
*********
// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Software Metering";
Comment = "Displays a list of computers that have run programs
matching the selected software metering rule within the specified month
and year.";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Computers that have run a specific metered software
program in a certain timeframe";
NumPrompts = 5;
RefreshInterval = 0;
ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Rule Name";
SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n select distinct ProductName as C018 from v_MeteredProductRule order
by ProductName
\n else
\n select distinct ProductName as C018 from v_MeteredProductRule
\n WHERE ProductName like @__filterwildcard
\n order by ProductName
\nend";
VariableName = "RuleName";
},
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Month (1-12)";
SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n select distinct DATEPART(month,IntervalStart) as Month, DATEPART
(year,IntervalStart) as Year from v_SummarizationInterval order by
DATEPART(year,IntervalStart), DATEPART(month,IntervalStart)
\n else
\n select distinct DATEPART(month,IntervalStart) as Month, DATEPART
(year,IntervalStart) as Year from v_SummarizationInterval
\n WHERE DATEPART(month,IntervalStart) like @__filterwildcard
\n order by DATEPART(year,IntervalStart), DATEPART
(month,IntervalStart)
\nend";
VariableName = "Month";
},
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Year";
SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n select distinct DATEPART(year,IntervalStart) as Year from
v_SummarizationInterval order by DATEPART(year,IntervalStart)
\n else
\n select distinct DATEPART(year,IntervalStart) as Year from
v_SummarizationInterval
\n WHERE DATEPART(year,IntervalStart) like @__filterwildcard
\n order by DATEPART(year,IntervalStart)
\nend";
VariableName = "Year";
},
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "EndMonth";
SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n select distinct DATEPART(month,IntervalStart) as Month, DATEPART
(year,IntervalStart) as Year from v_SummarizationInterval order by
DATEPART(year,IntervalStart), DATEPART(month,IntervalStart)
\n else
\n select distinct DATEPART(month,IntervalStart) as Month, DATEPART
(year,IntervalStart) as Year from v_SummarizationInterval
\n WHERE DATEPART(month,IntervalStart) like @__filterwildcard
\n order by DATEPART(year,IntervalStart), DATEPART
(month,IntervalStart)
\nend";
VariableName = "EndMonth";
},
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "EndYear";
SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n select distinct DATEPART(year,IntervalStart) as Year from
v_SummarizationInterval order by DATEPART(year,IntervalStart)
\n else
\n select distinct DATEPART(year,IntervalStart) as Year from
v_SummarizationInterval
\n WHERE DATEPART(year,IntervalStart) like @__filterwildcard
\n order by DATEPART(year,IntervalStart)
\nend";
VariableName = "EndYear";
}};
SecurityKey = "";
SQLQuery = "declare @TimeKey int
\ndeclare @TimeKey2 int
\ndeclare @days float
\n
\nset @TimeKey=100*@Year+@Month
\nset @TimeKey2=100*@EndYear+@EndMonth
\n
\nselect @days=DATEDIFF(day,IntervalStart,DATEADD
(month,1,IntervalStart))
\nfrom v_SummarizationInterval where TimeKey=@TimeKey
\n
\nif IsNULL(@days,0) > 0
\n select sys.Netbios_Name0, inst.SMS_Installed_Sites0,
\n DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as LastUsage,
\n SUM(UsageCount) + SUM(TSUsageCount) as C021,
\n ROUND((SUM(UsageCount) + SUM(TSUsageCount))/@days,2) as C022,
\n ROUND(SUM(UsageTime)/60.0,2) as C023,
\n ROUND(SUM(UsageTime)/60.0/(SUM(UsageCount) + SUM
(TSUsageCount)),2) as C024,
\n ROUND(SUM(UsageTime)/60.0/@days,2) as C025
\n from v_R_System sys
\n join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
\n join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
\n left join v_RA_System_SMSInstalledSites inst on
sys.ResourceID=inst.ResourceID
\n where mf.ProductName = @RuleName
\n and mus.TimeKey >= @TimeKey
\n and mus.TimeKey <= @TimeKey2
\n group by sys.Netbios_Name0, inst.SMS_Installed_Sites0
\n having SUM(UsageCount) + SUM(TSUsageCount) > 0
\n order by sys.Netbios_Name0";
StatusMessageDetailSource = FALSE;
};
// ***** End *****
--
Kim Oppalfens
MVP SMS
Computacenter Belgium
.
- Prev by Date: Re: How to remove Remote tools?
- Next by Date: Re: XP SP2
- Previous by thread: XP SP2
- Next by thread: Re: SMS_MP_CONTROL_MANAGER Status Messages
- Index(es):
Relevant Pages
|
