Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.
Backup and Recovery Community Blog

Creating Media Usage reports

Created: 19 Jul 2010 • 4 comments
Andy C's picture
+1 1 Vote
Login to vote

Like most of you I have found it somewhat frustrating that Ops-Center does not come with a Proper Media Used report.angry

The Media used report is a particular type of report that is very useful if not invaluable, when informing the operation teams with the correct tapes to change when off-siting, If we could'nt get this report working we would be better of just sticking with the FREE scripts that we currently used to send out automated reports and ditch ops-center.

After consulting Symantec support i was pointed in the direction of a ODBC connection so that I could extract the schema myself the ODBC connector can be found here

Once the schema was extracted I had at first a few issue's reformatting the Date fields, conveniently these are stored in an 18digit format, for those that are interested it's a measurement of the amount of 100th's of a nano second that have passed since the 1st January 1601 ... Yeah just what I thought surprise.

OK so how to I format this damn time / date field, well as luck would have it I stumbled across the UTCBigintToUTCTime function within the schema export that formats the time to a recognizable / usable format. 

So here's the SQL transcript that I used :

Select MediaID,  Kbytes / 1073741824 AS "GB on Tape", VolumePoolName as "Volume Pool", CAST(UTCBigintToUTCTime(AllocatedDate) as varchar(19)) as "Allocated Date", CAST(UTCBigintToUTCTime(LastWrittenDate) as varchar(19)) as "Last Written Date" from nom_NBMedia where "Last Written Date" > dateadd(day,-1, getdate()) And ServerName = 'Master Servername'

I have aliased alot of the column names so that they are readable and just look alot prettier than the default names smiley

The above query will output a report for tapes used within the last 24hours just change the -1 to any number of days that you would like the report to run back too, also change the Master Server in quotes to the name of your master server that you wish to run the reports on.

Hope the above helps,

Comments 4 CommentsJump to latest comment

ozwebuk's picture

We downloaded the ODBC driver and installed on an XP client. havign difficulty configuring the ODBC link, not tangible help and no instructions on what to use where on the ODB settings. Did you get access to some other information to enable you to hook in using ODBC?

Login to vote
Filip Hasa's picture

It is easy. See TechNote

Login to vote
Flako's picture

How about a :     "bpimagelist -media -L -hoursago 24"    ???

(NetBackup on Solaris 10)

cu Ingo

Login to vote
jdr6382's picture

I am using this exact query to generate "tapes written" report.

Everything I need is here, except a "media retention" column.

Is it possible to get a line added that will produce a column showing the retention period of the tapes?

Also, can the date be edited to reflect a range, rather than only be able to go a specified amount of days back?

Login to vote