Consultas SQL personalizadas en Opscenter (Ejemplo)

Enrique Pereira Calvo Opscenter

Introducción

 

En este post intentaré explicar como interpretar el documento que contiene el esquema de las bases de datos de Opscenter para poder establecer las relaciones necesarias para crear una consulta personalizada. Normalmente será necesario localizar los campos que queremos presentar, así como identificar las relaciones necesarias entre las tablas y como presentar los datos (especialmente las fechas), filtrarlos y ordenarlos para poder obtener el resultado deseado.

 

Identificación de las relaciones

 

En el documento del esquema de la base de datos de Opscenter (descargable desde el propio Opscenter al intentar crear una consulta personalizada) podemos ver en detalle todas las tablas, lo que significa cada campo y las relaciones entre ellas.

 

Para este ejemplo, haremos una consulta que muestre los siguientes campos: Master server, Política, Cliente, Backup id y las copias existentes de backup con su fecha de expiración:

 

blog1

 

Para poder hacer esta consulta, vamos a necesitar la siguientes tablas:

 

  • domain_MasterServer: contiene información sobre el master server. Utilizaremos el campo «friendlyName».
  • domain_Image: esta tabla contiene información genérica de las imágenes. Utilizaremos el campo «policyName».
  • domain_JobImage: utilizaremos el campo «clientName» e «imageId».
  • domain_ImageCopy: contiene los detalles de cada una de las copias de backup. Utilizaremos «id» y «expirationTime».

 

 

blog2

 

Ahora será necesario establecer la relación entre cada una de las tablas. Por ejemplo, para relacionar domain_Image con domain_ImageCopy podemos buscar en la guía que contiene el esquema. Lo primero que encontraremos es el listado de campos explicando para que sirve cada uno y, a continuación, los campos que relacionan cada una de las tablas. En el caso de domain_ImageCopy podemos observar:

 

  • Foreign Key Columns: masterServerId, imageid
  • Primary Table: domain_Image
  • Primaryt Key Columns: masterServerId, id

 

De esta forma, tendremos que modificar nuestra consulta para que se relacionen los campos de ambas tablas:

 

blog3

 

Y así sucesivamente hasta relacionar todas las tablas. La consulta completa sería la siguiente:

 

SELECT «DBA».»domain_MasterServer».»friendlyName» As Master,
«DBA».»domain_Image».»policyName» As Policy,
«DBA».»domain_JobImage».»clientName» As Client,
«DBA».»domain_JobImage».»imageId» As BackupId,
«DBA».»domain_ImageCopy».»id» As CopyId,
UTCBigintToNOMTime («DBA».»domain_ImageCopy».»expirationTime») As ExpirationTime
FROM ( ( «DBA».»domain_MasterServer» JOIN «DBA».»domain_Image» ON «DBA».»domain_MasterServer».»id» = «DBA».»domain_Image».»masterServerId» ) JOIN «DBA».»domain_JobImage» ON «DBA».»domain_Image».»masterServerId» = «DBA».»domain_JobImage».»masterServerId» AND «DBA».»domain_Image».»masterServerId» = «DBA».»domain_JobImage».»masterServerId» AND «DBA».»domain_Image».»id» = «DBA».»domain_JobImage».»imageId» ) JOIN «DBA».»domain_ImageCopy» ON «DBA».»domain_Image».»masterServerId» = «DBA».»domain_ImageCopy».»masterServerId» AND «DBA».»domain_ImageCopy».»imageId» = «DBA».»domain_Image».»id»
WHERE «DBA».»domain_ImageCopy».»expirationTime» > NOMTimeToUTCBigint(Getdate())

 

En esta consulta también podemos observar los siguientes detalles:

 

  • Netbackup utiliza las fechas en formato «unixtime». Para poder mostrarlas en un formato leíble se utiliza la función «UTCBigintToNOMTime».
  • Opscenter guarda información de todas las copias incluso de las que están expiradas. Por eso, para evitar mostrar más copias de las existentes en nuestro catálogo debemos incluir un filtro para que sólo muestre las imágenes que todavía no han expirado: WHERE «DBA».»domain_ImageCopy».»expirationTime» > NOMTimeToUTCBigint(Getdate())

 

Un ejemplo un poco más complejo

 

A continuación copio un ejemplo real con algo más de información. A la consulta anterior se le añaden 6 columnas para poder ver en cada línea las 3 primeras copias existentes en el catálogo junto a su fecha de expiración:

 

blog4

 

La consulta es la siguiente:

 

SELECT «DBA».»domain_MasterServer».»friendlyName» AS «Master»,

«DBA».»domain_Image».»policyName» AS «Policy»,

«DBA».»domain_JobImage».»clientName» AS «Client»,

«DBA».»domain_Image».»id» AS «BackupId»,

UTCBigintToNOMTime( «DBA».»domain_Image».»writeStartTime» ) AS BackupTime,

(CASE «DBA».»domain_ImageFragment».»mediaType» WHEN ‘2’ THEN «DBA».»domain_ImageFragment».»mediaId» ELSE «DBA».»nb_DiskPool».»name» END) as Copy1,

UTCBigintToNOMTime( «DBA».»domain_ImageFragment».»expirationTime» ) as Copy1Expiration,

(CASE «domain_ImageFragment2″.»mediaType» WHEN ‘2’ THEN «domain_ImageFragment2″.»mediaId» ELSE «nb_DiskPool2″.»name» END) as Copy2,

UTCBigintToNOMTime( «domain_ImageFragment2″.»expirationTime» ) as Copy2Expiration,

(CASE «domain_ImageFragment3″.»mediaType» WHEN ‘2’ THEN «domain_ImageFragment3″.»mediaId» ELSE «nb_DiskPool3″.»name» END) as Copy3,

UTCBigintToNOMTime( «domain_ImageFragment3″.»expirationTime» ) as Copy3Expiration

 

FROM ( ( ( ( ( ( ( ( ( ( ( «DBA».»domain_MasterServer» JOIN «DBA».»domain_Image» ON «DBA».»domain_MasterServer».»id» = «DBA».»domain_Image».»masterServerId» ) JOIN «DBA».»domain_ImageCopy» ON «DBA».»domain_Image».»masterServerId» = «DBA».»domain_ImageCopy».»masterServerId» AND «DBA».»domain_Image».»id» = «DBA».»domain_ImageCopy».»imageId» ) JOIN «DBA».»domain_ImageFragment» ON «DBA».»domain_ImageCopy».»masterServerId» = «DBA».»domain_ImageFragment».»masterServerId» AND «DBA».»domain_ImageFragment».»imageId» = «DBA».»domain_ImageCopy».»imageId» AND «DBA».»domain_ImageFragment».»copyId» = «DBA».»domain_ImageCopy».»id» ) JOIN «DBA».»domain_JobImage» ON «DBA».»domain_Image».»masterServerId» = «DBA».»domain_JobImage».»masterServerId» AND «DBA».»domain_Image».»id» = «DBA».»domain_JobImage».»imageId» ) LEFT OUTER JOIN «DBA».»domain_ImageFragment» AS «domain_ImageFragment2» ON «DBA».»domain_ImageCopy».»masterServerId» = «domain_ImageFragment2″.»masterServerId» AND «domain_ImageFragment2″.»imageId» = «DBA».»domain_ImageCopy».»imageId» AND «domain_ImageFragment2″.»copyId» = ‘2’ AND «domain_ImageFragment2″.»id» = ‘1’ ) LEFT OUTER JOIN «DBA».»domain_ImageFragment» AS «domain_ImageFragment3» ON «DBA».»domain_ImageCopy».»masterServerId» = «domain_ImageFragment3″.»masterServerId» AND «domain_ImageFragment3″.»imageId» = «DBA».»domain_ImageCopy».»imageId» AND «domain_ImageFragment3″.»copyId» = ‘3’ AND «domain_ImageFragment3″.»id» = ‘1’ ) JOIN «DBA».»nb_DiskVolume» ON «dba».»domain_ImageFragment».»masterServerId» = «DBA».»nb_DiskVolume».»masterServerId»

AND

«DBA».»domain_ImageFragment».»diskMediaId» = «DBA».»nb_DiskVolume».»diskMediaId» AND «dba».»domain_ImageFragment».»expirationTime» > NOMTimeToUTCBigint(Getdate()) ) LEFT OUTER JOIN «DBA».»nb_DiskVolume» AS «nb_DiskVolume2» ON «domain_ImageFragment2″.»masterServerId» =»nb_DiskVolume2″.»masterServerId»

AND

«domain_ImageFragment2″.»diskMediaId» = «nb_DiskVolume2″.»diskMediaId» AND «domain_ImageFragment2″.»expirationTime» > NOMTimeToUTCBigint(Getdate()) ) LEFT OUTER JOIN «DBA».»nb_DiskVolume» AS «nb_DiskVolume3» ON «domain_ImageFragment3″.»masterServerId» = «nb_DiskVolume3″.»masterServerId»

AND

«domain_ImageFragment3″.»diskMediaId» = «nb_DiskVolume3″.»diskMediaId» AND «domain_ImageFragment3″.»expirationTime» > NOMTimeToUTCBigint(Getdate()) ) LEFT OUTER JOIN «DBA».»nb_DiskPool» ON «dba».»nb_DiskVolume».»masterServerId» = «DBA».»nb_DiskPool».»masterServerId»

AND

«DBA».»nb_DiskVolume».»storageServerName» = «DBA».»nb_DiskPool».»storageServerName»

AND

«DBA».»nb_DiskVolume».»serverType» = «DBA».»nb_DiskPool».»serverType» ) LEFT OUTER JOIN «DBA».»nb_DiskPool» AS «nb_DiskPool2» ON «nb_DiskVolume2″.»masterServerId» = «nb_DiskPool2″.»masterServerId»

AND

«nb_DiskVolume2″.»storageServerName» = «nb_DiskPool2″.»storageServerName»

AND

«nb_DiskVolume2″.»serverType» = «nb_DiskPool2″.»serverType» ) LEFT OUTER JOIN «DBA».»nb_DiskPool» AS «nb_DiskPool3» ON «nb_DiskVolume3″.»masterServerId» = «nb_DiskPool3″.»masterServerId»

AND

«nb_DiskVolume3″.»storageServerName» = «nb_DiskPool3″.»storageServerName»

AND

«nb_DiskVolume3″.»serverType» = «nb_DiskPool3″.»serverType»

WHERE «DBA».»domain_ImageFragment».»id» = ‘1’ AND «DBA».»domain_ImageFragment».»copyId» = ‘1’ AND DATEDIFF(hour,BackupTime, GETDATE()) <=720

ORDER BY BackupTime DESC

 

Se puede destacar en esta consulta:

  • Forma de ordenar con la clausula ORDER BY.
  • Forma de mostrar sólo los trabajos de las últimas 720 horas (1 mes) con DATEDIFF(hour,BackupTime, GETDATE()) <=720

 

Referencias

Consultas SQL personalizadas en Opscenter

Symantec NetBackup OpsCenter 7.7 Reporting Guide

Symantec NetBackup OpsCenter Database Schema Document (descargable desde Opscenter)

 

 

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *