Video Screencast Help

A simple SQL sample for a report

Created: 31 Mar 2013 • Updated: 01 Apr 2013

This is a simple example of an SQL statement for generationg a report on current active jobs.  Something that you not want during business hours.  There seems to be nothing on SQL, it is assumede that people already know it.  Feel free to add more examples

TRhis is a continuation of   How to create oint in time status report on OpsCenter  https://www-secure.symantec.com/connect/forums/how-create-point-time-status-report-opscenter

In full

SELECT clientName AS Client,policyName AS Policy,scheduleName AS Schedule,bytesWritten/1024/1024/1024 AS 'GiB Written',throughput/1024 AS 'MB/s' ,utcbiginttonomtime(startTime) AS 'Start Time',utcbiginttonomtime(endTime) AS 'End Time',mediaServerName AS 'Media Server',id AS 'Job ID' FROM domain_job WHERE state = 1 AND policyName NOT LIKE '%RMAN%' ORDER by id

Dissected

  • clientName AS Client,policyName AS Policy,   Examples of how to change column titles - single words only
  • bytesWritten/1024/1024/1024 AS 'GiB Written' Converts the numeric (bytes) the GiB with the column title containing multiple words.
  • utcbiginttonomtime(startTime) AS 'Start Time'  Converts time into something that people can read, its the only SQL function doumented in the manual. 
  • FROM domain_job WHERE state = 1  This documented in How to create oint in time status report on OpsCenter
  • AND policyName NOT LIKE '%RMAN%'   Used to exclude an item, exludes any policy name that has RMAN, % is the SQL wildcard, % location is important
  • ORDER by id  Order the rows ASC ascending is the default, probably can to DESC  for descending if its standard SQL.

 

Operating Systems: