Hi Guys,
Im so glad i have found this post! we are currently using this to run a daily report for our media servers.
The only issue im having at the moment is that every time i run this report for a particular media server the report wont show any details :( how ever in activity monitor you can see the job has ran.
i have even tried the like %servername%
the below is what we are using...
SELECT
domain_MediaServer.Name as [Media Server Name]
,domain_JobArchive.clientName as "Client Name"
,adjust_timestamp(domain_ScheduledJob.scheduledTime,10800000) as "Job Scheduled Time"
,UTCBigIntToNomTime(domain_JobArchive.startTime) as "Job Start Time"
,UTCBigIntToNomTime(domain_JobArchive.endTime) as "Job End Time"
,DATEDIFF(minute,UTCBigIntToNomTime(domain_JobArchive.startTime)
,UTCBigIntToNomTime(domain_JobArchive.endTime)) as "Job Duration(Minutes)"
,nb_JobFilesArchive.fileinfo as "Files"
,(CASE
WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 0 THEN 'Successful'
WHEN domain_JobArchive.state=3 AND domain_JobArchive.statusCode=1 THEN 'Partial'
WHEN domain_JobArchive.state=3 AND domain_JobArchive.statusCode>1 THEN 'Failed'
ELSE 'Failed'
END) as "Job Status"
//,CAST(domain_JobArchive.bytesWritten/1024/1024/1024 as NUMERIC(20,2)) as "Job Size(MB)",
//,domain_JobArchive.statusCode as "Status Code"
FROM domain_JobArchive LEFT JOIN domain_MediaServer on domain_JobArchive.MediaServerId = domain_MediaServer.Id,
lookup_JobState, domain_ScheduledJob, nb_JobFilesArchive
WHERE domain_ScheduledJob.jobid = domain_JobArchive.id
AND domain_JobArchive.masterserverId = nb_JobFilesArchive.masterserverId
AND domain_JobArchive.clientName = nb_JobFilesArchive.clientName
AND domain_JobArchive.id = nb_JobFilesArchive.jobID
AND domain_JobArchive.state =lookup_JobState.id AND DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <=48
AND domain_JobArchive.policyName != ''
AND domain_MediaServer.Name like '%SERVERNAME%'
