asked on

SQL Canned Report for SCCM -Add Host Name

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

TempDBA

Can you paste the schema of the table that are being used here in the query?

Willing2Learn_More

ASKER

Reference---Configuration-Manage.vsd Reference---Configuration-Manage.xlsx Reference---Configuration-Manage.xlsx

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.

Willing2Learn_More

ASKER

It appears to be "v_R_System" is the SQL Schema and the resource id is "Netbios_Name0"

Willing2Learn_More

ASKER

I tried this with no luck...Anyone out there have a suggestion or solution???????

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
select * from v_R_System where v_R_System.Netbios_Name0 = 'computer_name'

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

ASKER CERTIFIED SOLUTION

Willing2Learn_More

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.