I am trying to figure out how OpsCenter combines reports such as Master Server Throughput and Total Jobs of a Server. I need to make the Media Server Throughput report have the same colunms as the Master Server Throughput report.
I am running version 7.7.
Let me put it this way.....
OpsCenter && Throughput ==
Do you have OpsCenter Analytics? We can maybe make a custom SQL query.
Yes I have Analytics. I have been trying to break down the Master Server throughput report and recreate it with a Distinct Master and each Media Server so the report would be something like this
Media Server Name Total Job Count Volume(MB) Throughput(KB/sec) Number of successful Jobs Success Rate Last Job
I can create Views for each Master server (there are multiple for this customer).
I have been able to create a SQl script that currently pulls Media Server, Total Job Count, Volume and Throughput. Here is what I have:
dmj.name as "Media Server",
CAST(SUM(dj.bytesWritten/1024/1024) as decimal(10,2)) as "Volume(MB)",
count(distinct dj.id) AS "Total Number of Jobs",
CAST(AVG(dj.throughput) as decimal(10,2)) AS "Throughput (KB/sec)"
FROM domain_Job dj
INNER JOIN domain_MediaServer dmj
ON dj.mediaServerName = dmj.name
WHERE DATEDIFF(day, UtcBigIntToNomTime(dj.endTime), GETDATE()) <= 7
AND dj.Type != '17'
GROUP BY dmj.name
I am confused as to how I can Number of Successfull jobs (I assume Count up jobs that their Status Code = 0) and calculate Success Rate in the query.
Sorry I don't have an opscenter with data in it to test with. You should change byteswritten to presissize in case you're using appliances. And then yes, you'll have to look for status code 0 & 1 in dj.statuscode.
IF you want you can just open the stored procedure and check whats going on. I suspect thought that the logic behind it might be a bit more complex than what you've got up there (which I'm not saying is correct as most of the times the opscenter figures look a bit weird)
Yeah I know, you'd need to log into opscenter via isql and then do it from there. I googled it once when I was looking to see how it worked ;)