SMS 2003 report help

I have no idea how to do this, but hopefully someone out there can help. 
Running SMS 2003 and I have the two reports below that I would like to 
combine with a change.

I would like to be able to see the username and time of day usage for each 
user hit, not just the last usage along with everything in that first 
report.
I would also like to just pull all the data for all games and all dates 
instead of having to specify.  We aren't that big that I need to cut this 
list down.

Any ideas?  Thanks a bunch for any help!

Mark


"Users that have run a specific metered software program"

declare @TimeKey int
declare @days float

set @TimeKey=100*@Year+@Month

select @days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))
from v_SummarizationInterval where TimeKey=@TimeKey

if IsNull(@days,0) > 0
  select mu.FullName,
       DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as LastUsage,
       SUM(UsageCount) + SUM(TSUsageCount) as C021,
       ROUND((SUM(UsageCount) + SUM(TSUsageCount))/@days,2) as C022,
       ROUND(SUM(UsageTime)/60.0,2) as C023,
       ROUND(SUM(UsageTime)/60.0/(SUM(UsageCount) + SUM(TSUsageCount)),2) as 
C024,
       ROUND(SUM(UsageTime)/60.0/@days,2) as C025
  from v_MeteredUser mu
  join v_MonthlyUsageSummary mus on mu.MeteredUserID=mus.MeteredUserID
  join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
  where mf.ProductName = @RuleName
  and mus.TimeKey = @TimeKey
  group by mu.FullName
  having SUM(UsageCount) + SUM(TSUsageCount) > 0
  order by  mu.FullName

*************

"Time of day usage summary for a specific metered software program"

set nocount on
declare @hour int
create table #hours (Hour int)
set @hour=0
while @hour < 24
begin
  insert into #hours(Hour) values(@hour)
  set @hour = @hour + 1
end

create table #avgusage
(
  hour int,
  dow  int,
  ucount int
)

insert into #avgusage(hour,dow,ucount)
select DATEPART(hour,DATEADD(ss,@__timezoneoffset,fusi.IntervalStart)),
          DATEPART(dw,DATEADD(ss,@__timezoneoffset,fusi.IntervalStart)),
          AVG(IsNULL(UCount,0))
from v_FileUsageSummaryIntervals fusi
left join
  (
    select fus.IntervalStart, SUM(fus.DistinctUserCount) as UCount
    from v_FileUsageSummary fus
    join v_MeteredFiles mf on fus.FileID=mf.MeteredFileID
    where fus.IntervalStart >= DATEADD(day,-90,GetDate())
    and mf.SecurityKey = LEFT(@RuleName,8)
    and fus.IntervalWidth=60
    group by fus.IntervalStart
  ) as USums on fusi.IntervalStart=USums.IntervalStart
where fusi.IntervalWidth=60 and fusi.IntervalStart >= 
DATEADD(day,-90,GetDate())
group by DATEPART(hour,DATEADD(ss,@__timezoneoffset,fusi.IntervalStart)),
               DATEPART(dw,DATEADD(ss,@__timezoneoffset,fusi.IntervalStart))
select
  hrs.Hour,
  IsNULL((select ucount from #avgusage where dow=1 and hour=hrs.Hour),0) as 
C042,
  IsNULL((select ucount from #avgusage where dow=2 and hour=hrs.Hour),0) as 
C043,
  IsNULL((select ucount from #avgusage where dow=3 and hour=hrs.Hour),0) as 
C044,
  IsNULL((select ucount from #avgusage where dow=4 and hour=hrs.Hour),0) as 
C045,
  IsNULL((select ucount from #avgusage where dow=5 and hour=hrs.Hour),0) as 
C046,
  IsNULL((select ucount from #avgusage where dow=6 and hour=hrs.Hour),0) as 
C047,
  IsNULL((select ucount from #avgusage where dow=7 and hour=hrs.Hour),0) as 
C048
from #hours hrs
order by hrs.Hour


drop table #avgusage
drop table #hours 


0
Mark
2/25/2010 1:31:37 PM
sqlserver.programming 1873 articles. 0 followers. Follow

0 Replies
809 Views

Similar Articles

[PageSpeed] 52

Reply:

Similar Artilces:

SBS 2003 and Email Not Receiving
We have SBS 2003 Installed. We had a linkys router that we replaced with a small business cisco gigabit router. After the router changed, internet would work, we can send email to extrenal, send and receive intenral emails but receiving external wouldnt work. We ran the Email/Internet repair via SBS. Receiving external email worked. Few hours later it stoped. We ran it again and it works. This keeps going..it works for few hours and than stops. Is there something that we missed or a setting that we need to setup on the router so it keeps it up all the time. Thanks -- mal...

Report Security
I have some questions : 1. I have some custom reports, for example report A, B and C. But why there are some user CRM who can not view the custom reports, although they are CRM user and they are member of SQLReportingGroup in active directory. 2. Why there're some user that not automatically include of SQLReportingGroup member, although they are member of active directory & registered as user in Ms CRM? Thx before ^_^ ...

Office 2003 on WinXP is receiving an error...
This operating is not presently configured to run this application.' This nmessage keeps popping up after I install WInXP Sp2. Right after I install office 2003, and I try to open MS word 2003, the above message appears. Can anyone point me in the right direction? RodneyJ Insert office cd>repair "RodneyJ" <u58446@uwe> wrote in message news:a43130535cd3e@uwe... > This operating is not presently configured to run this application.' This > nmessage keeps popping up after I install WInXP Sp2. Right after I > install > office 2003, and I t...

Running Money 2003 in Vista
Hi I run this version perfectly satisfactorily in my Vista Ultimate desktop.with my normal log on. No fiddling was required to get it to start. It worked perfectly out of the box, just as in XP Referring to emails on earlier versions in the Newsgroup, my data is in a Documents folder on the D Drive well away from Vista, though it is secured. In this machine SSL2 is not enabled I have a newish laptop running Windows Home Premium with a similar configuration for all my data on the D drive. I recently tried to open Money for the first time since installation using a copy of the Money fil...

Customizing report in MS2005 causes data to vanish
While customizing a Report by Category, I clicked on the Details and Amount tabs and all my data vanished in the report. What'smore I cannot seem to restore the data in the report...? Bug? Or is there a workaround? Thanks. BTW: A feature(s) request: It would be great to be able to get category data grouped quarterly (not just monthly) It would be great to see a horizontal report with categories displayed monthly and quarterly (listed across the top) Finally, sorting a report by perhaps two or three criteria would be great (as I see it, it is only one creterion currently) Thanks ...

Monitor Server in 2003?
Hi all, After installing Exchange 2003 SP1, I'm having some issues monitoring exchange server via scripting. 1. I can no longer get any instances from "Win32_PerfRawData_ESE_DatabaseInstances" via scripting. 2. The property "LogGenerationCheckPointDepth" does not even appear as a property of "Win32_PerfRawData_ESE_DatabaseInstances". (I checked this with WMI CIM studio) I can still get the LogGenerationCheckPointDepth from performance monitor. The following code verifies what I've seen in WMI CIM studio: set RawProc = GetObject("winmgmts:\\r...

Outlook 2003
Just bought new PC. Everything is Microsoft. XP Home - Microsoft Works (came installed) - User like to use MSN for e-mail, Sent Microsoft $300.00 to activate Office that came installed on machine. Word will not allow MSN to be the e-mail client. System crashed and burned after activation of Office. Outlook 2003 seems a little unstable. Why would a new PC with Works, Office Express and Outlook 2003 be shipped? All these programs do the same thing and they don't seem to talk with each other well. On top of this all she wants to do is use Word and send the document through MSN nor...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

How do I get Powerpoint 2003 fade in 1 by 1 in 2007
I frequently used the 'fade in one by one' feature in Powerpoint 2003, which allowed me to fade in individual bullet points by way of a click of a mouse. Not only is this feature now not standard in 2007, but I can't work out how to create it: anything that I find, tends, instead, to fade the bullet points in on some kind of automatic time schedule, rather than - as I want - on a mouse click. I want only to use text, no sounds, no pictures, and nothing fancy. Currently, I am reduced to copying my material into an old Powerpoint 2003 presentation, but this is silly....

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Access 97/2000/2003 comparisons
I have a rather large application that uses an access 97 database (DAO). We also have a version that works with access 2000, Oracle, msde, and SQLServer (ADO). We would like to retire the DAO version of the product, but there are places where the ADO version is much slower compared to where we run DAO seeks (very noticeable when looping). So, I have a few questions. 1. Are there any tips/tricks to speed up ADO queries to compare with DAO seeks? 2. Would there be any benifit in using access 2003 over previous versions of the software besides the added features (xml support, etc). 3...

Reporting IRA distributions
I have an IRA account with corresponding cash account. I am trying to get the distribution from the IRA to show up as "Retirement Income" in the Tax-Related Transactions report. How to do it? The distributions are handled as a transfer from the IRA Investment cash acct to a checking account. Thanks for any help. Money 99, BTW. Do I need to upgrade to a later version? ...

SBS 2003 moving of users files
I run SBS 2003 and due to the amount of data on the users drive it has become chokers and have installed a new 1tb drive to keep up with demand for space. I need to move all the data to the new drive but unsure of the process. Is there an easy way of doing this? As it needs to be done asap Thanks -- JimmyJames ------------------------------------------------------------------------ JimmyJames's Profile: http://forums.techarena.in/members/255792.htm View this thread: http://forums.techarena.in/small-business-server/1357051.htm http://forums.techarena.in You c...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Exchange 2003 IMAP and Postini
Hello all -- I have a problem and I was wondering if the smart ppl out there could come up with a solution. I've enabled Secure IMAP for our users with cell phones with pocket PC installed on them... Basically, we are using Postini, and they want you to ONLY accept connections from their servers, so as to not allow SPAMers to directly connect to the exchange server and directory harvest attack. However, when Idid that, my IMAP ppl can't SEND email anymore because they aren't coming from the trusted IP range. Is there anyway around this? Probably not, but I thought I'...

Outlook 2003
In Outlook 2003, #1 Is there a way to refresh the unread folder so that read messages no longer apear? Right now I have to click closed the unread folder and click it again #2 Is there a way to create a toolbar button that goes directly to a subfolder? Thanks ...

Limit on Exchange 2003 version included with SBS 2003
Can anyone tell me if the version of Exchange 2003 included with Small Business Server 2003 has the 16GB limit that exists in the Exchange 2003 standard edition? Yes. >-----Original Message----- >Can anyone tell me if the version of Exchange 2003 >included with Small Business Server 2003 has the 16GB >limit that exists in the Exchange 2003 standard edition? >. > ...

Outlook 2003 rules question again
I have a rule so that after the e-mail comes in, it searches for specific words in the e-mail header. I set up this rule to search for sober.worm since I'm receiving a copious amount of spam e-mails which include the sober.worm attachment. I set the rule to delete the e-mails indefinately but it does not delete it. It just sends the e-mails to the junk mail folder. I would like to have it delete the e-mails forever. What can I do to achieve this? -- Nocturnal @ http://www.randomfix.com Also, I did get it to work a few times but the rule stops working. Another thing is if ...

Creating Exchange 2003 organization
Is it possible to have two Exchange Organizations in the same domain? I need to reproduce a problem and was hoping to install an Exchange server to an existing domain however I need the organization to be different. Is this possible? When I installed EX 2003 I wasn't prompted for information other than location of files. Thanks in advance On Mon, 28 Feb 2005 08:09:03 -0800, "RP" <RP@discussions.microsoft.com> wrote: >Is it possible to have two Exchange Organizations in the same domain? I need >to reproduce a problem and was hoping to install an Exchange server...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Why will my publisher 2003 not print graphics?
I have recently loaded windows 7, and since then I cannot print any graphics from any publisher document, text and infills print but lines do not! I have looked at the advance printer settings and the "Do not print any graphics" box is not ticked. I have also tried reloading publisher but this did nothing. Any ideas anyone? Might take a look at the web site of your printer model to see if there are Windows 7 drivers. Will the document print okay if you convert it to PDF? There are free converters around. www.primopdf.com is free. It is always wise to completely remove ...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...