Creating Media Usage reports
Like most of you I have found it somewhat frustrating that Ops-Center does not come with a Proper Media Used report.
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 ftp://ftp.veritas.com/pub/opscenter/.
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 .
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
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,