Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Does anyone know how I can add the host name to the report listed below? This report is one of the canned reports from sccm called "Users that have run a specific metered software program" but it is missing the host name column that I need. Any help would be greatly appricated.

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

declare @TimeKey int

declare @days float

set @TimeKey=100*@Year+@Month

select @days=DATEDIFF(day,Interva

from v_SummarizationInterval where TimeKey=@TimeKey

if IsNull(@days,0) > 0

select mu.FullName,

DATEADD(ss,@__timezoneoffs

SUM(UsageCount) + SUM(TSUsageCount) as C021,

ROUND((SUM(UsageCount) + SUM(TSUsageCount))/@days,2

ROUND(SUM(UsageTime)/60.0,

ROUND(SUM(UsageTime)/60.0/

ROUND(SUM(UsageTime)/60.0/

from v_MeteredUser mu

join v_MonthlyUsageSummary mus on mu.MeteredUserID=mus.Meter

join v_MeteredFiles mf on mus.FileID=mf.MeteredFileI

where mf.ProductName = @RuleName

and mus.TimeKey = @TimeKey

group by mu.FullName

having SUM(UsageCount) + SUM(TSUsageCount) > 0

order by mu.FullName

Here are the files to tables. Not sure which one to use. I need it for a software metering report. All I need to do is add the host name to the report in a column. I need to know the name of each computer in addition to the other data that is spit out of the report.

declare @TimeKey int

declare @days float

set @TimeKey=100*@Year+@Month

select @days=DATEDIFF(day,Interva

from v_SummarizationInterval where TimeKey=@TimeKey

select * from v_R_System where v_R_System.Netbios_Name0 = 'computer_name'

if IsNull(@days,0) > 0

select mu.FullName,

DATEADD(ss,@__timezoneoffs

SUM(UsageCount) + SUM(TSUsageCount) as C021,

ROUND((SUM(UsageCount) + SUM(TSUsageCount))/@days,2

ROUND(SUM(UsageTime)/60.0,

ROUND(SUM(UsageTime)/60.0/

ROUND(SUM(UsageTime)/60.0/

from v_MeteredUser mu

join v_MonthlyUsageSummary mus on mu.MeteredUserID=mus.Meter

join v_MeteredFiles mf on mus.FileID=mf.MeteredFileI

where mf.ProductName = @RuleName

and mus.TimeKey = @TimeKey

group by mu.FullName

having SUM(UsageCount) + SUM(TSUsageCount) > 0

order by mu.FullName

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

-Thanks in advance!!

==========================

declare @TimeKey int

declare @days float

set @TimeKey=100*@Year+@Month

select @days=DATEDIFF(day,Interva

from v_SummarizationInterval where TimeKey=@TimeKey

if IsNull(@days,0) > 0

select mu.FullName,sys.Netbios_Na

DATEADD(ss,@__timezoneoffs

SUM(UsageCount) + SUM(TSUsageCount) as C021,

ROUND((SUM(UsageCount) + SUM(TSUsageCount))/@days,2

ROUND(SUM(UsageTime)/60.0,

ROUND(SUM(UsageTime)/60.0/

ROUND(SUM(UsageTime)/60.0/

from v_MeteredUser mu,v_R_System sys

join v_MonthlyUsageSummary mus on sys.ResourceID=mus.Resourc

join v_MeteredFiles mf on mus.FileID=mf.MeteredFileI

left join v_RA_System_SMSInstalledSi

where mf.ProductName = @RuleName

and mus.TimeKey = @TimeKey

group by mu.FullName,sys.Netbios_Na

having SUM(UsageCount) + SUM(TSUsageCount) > 0

order by mu.FullName,sys.Netbios_Na

==========================