Need help with OpsCenter report > Amount of data backed up by media server

[Frank666]

Hi all

I am new to OpsCenter.   I want to know how many TB my media server backed up during the last X days

So in short I want to reproduce the query that is populated in the "Job Summary by Job Status" dashboard.     That dashboard only shows the last 3 days.  I want to use the same quesry but to show the last X days and use it to go back in time.

Anybody know how I can achieve this?

I looked through the canned reports but no luck so far

Thanks!

[RonCaplinger]

Are you using OpsCenter (the free version that comes with NetBackup), or OpsCenter Analytics (separately licensed, extra cost)?  I have the Analytics option and I don't know what the free version includes or lacks, but I could try to help.  Also, make sure you are using the most current version of OpsCenter, even if it is a higher version than your NetBackup installation.

[MilesVScott · Accepted solution]

I believe this will work(I also included a file count but you can remove that part if unneeded):

SELECT dmj.name, SUM(dj.bytesWritten), SUM(dj.filesbackedup)

FROM domain_Job dj

INNER JOIN domain_MediaServer dmj

ON dj.mediaServerName = dmj.name

WHERE dmj.name = '[MediaServer]' AND DATEDIFF(day, UtcBigIntToNomTime(dj.endTime), GETDATE()) <= [Number Of Days to go back]

GROUP BY dmj.name

the media server is case sensative so if you cant fiure out what to use just execute this to get a list:

SELECT name

FROM domain_MediaServer

Also the [Number Of Days to go back]'s value does not need to be wrapped in single quotes.

This is a query to get the same data for all media servers in one result set:

SELECT dmj.name, SUM(dj.bytesWritten), SUM(dj.filesbackedup)

FROM domain_Job dj

INNER JOIN domain_MediaServer dmj

ON dj.mediaServerName = dmj.name

WHERE DATEDIFF(day, UtcBigIntToNomTime(dj.endTime), GETDATE()) <= 3

GROUP BY dmj.name

I hope that helps!

[MilesVScott]

I guess I could have been nice and converted the bytes to TB for you.... But what fun would that be ;)

[Frank666]

@Ron : For your info I am using OpsCenter Analytics 7.5

@Miles: That worked like a charm.  Don't worry about the TB conversion, you did all the hard work I can take care of converting the results :)

Question for you: where did you get the table names and field names?   I usually like to build queries (with MS SQL) but with OpsCenter I hit a wall.   I don't know how to get the table names like I would usually do with ManagementStudio for SQL.

Thanks a lot for your help, I have the reports I needed!

[MilesVScott]

Follow this guide to install Sybase Anywhere:

http://www.symantec.com/business/support/index?page=content&id=TECH141827&key=58330&actp=LIST

Once you map the ODBC conenction and connect you will be able to view all of the tables and other objects.

Hint: look at the view's to get some of the relationships/joins as a template.

[Frank666]

Excellent, you have been a great help!

Thanks again!

[tom_sprouse]

Frank and Miles...

If you would like to generate an output / list of the tables... you should be able to run the following

SELECT * from SP_TABLES();

You may need to execute this from DBISQLC, as the WebUI sometimes shows the same data on each page... or save the query and try executing as a CSV.

--Tom