RE: Report to list date of installation\deployment computer by mon
- From: Bruno <Bruno@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 29 Aug 2006 02:39:01 -0700
Hi Paul,
Your post made me think a bit more about the way to present this information
in a better way, ie via a Chart, so i created another query and associated it
with the current year by default. Here is the mof file content. you only have
to import it
Note that i used the Datepart to get the month names (april, instead of 4)
but kept grouping by dateadd otherwise the months would be listed
alphabetically.
This means there is a column with the month number. (i would like to remove
it but do not know how to do it without creating a specific view)
Hope it helps
bruno
// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "SMS Site - Client Information";
Comment = "";
GraphCaption = "Computers Installed per Month";
GraphType = 0;
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "Count Computers Installed per Month";
NumPrompts = 1;
RefreshInterval = 0;
ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = TRUE;
DefaultValue = "2006";
PromptText = "Please Enter the Year";
SampleValueSQL = "Select Distinct Datepart(yy, creation_Date0) From
v_R_System";
VariableName = "ChoosenYear";
}};
SecurityKey = "";
SQLQuery = "select Datename(m, Creation_Date0) As Month, count(resourceId)
As count, Datepart(mm, Creation_date0) As 'Month Number' from v_R_System
where Datepart(yy, Creation_Date0) = @choosenyear group by Datename(m,
Creation_Date0), Datepart(mm, Creation_date0)";
StatusMessageDetailSource = FALSE;
XColLabel = "Month";
YColLabel = "Number of Computers";
};
// ***** End *****
"Bruno" wrote:
Hi,.
the best way to lean to make reports or queries are the sms console (check
what reports exist and see how they were created), as well as Sql Query tool,
to test the result of tsql queries.
This one is indeed a bit more complex, as you need to use a built in
function to get your result, Datepart()
Create a new report with this query
select * from v_R_System where DATEPART(MM, Creation_Date0) = @mymonth
in the query prompt for the mymonth variable, enter the following query to
subselect only the month to choose from:
Select Distinct DATEPART(MM, Creation_Date0) from v_R_System
You will probably very soon need a further prompt for the Year, but i guess
a closer look at the Datepart function in sql help will bring you the
solution.
Hope that it helps
Bruno
"Paul" wrote:
We are testing SMS, one report the power to be would like to pull how many
and list of computer deployed in a month. I have tried to create this report
myself by coping and paste from default report with no luck. Does anybody
have a report like this? also can anybody point me some place so I can learn
to write a statement.
Thanks
- Prev by Date: Re: software inventory problem !
- Next by Date: sms2003 problem!
- Previous by thread: Software Inventory Not Collecting Data
- Next by thread: sms2003 problem!
- Index(es):
Relevant Pages
|
