Willing2Learn_More
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,Interva lStart,DAT EADD(month ,1,Interva lStart))
from v_SummarizationInterval where TimeKey=@TimeKey
if IsNull(@days,0) > 0
select mu.FullName,
DATEADD(ss,@__timezoneoffs et,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(Usage Count) + 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.Meter edUserID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileI D
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
Can you paste the schema of the table that are being used here in the query?
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.
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.
ASKER
It appears to be "v_R_System" is the SQL Schema and the resource id is "Netbios_Name0"
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,Interva lStart,DAT EADD(month ,1,Interva lStart))
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 et,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(Usage Count) + 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.Meter edUserID
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileI D
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.