Forum Discussion

Bharath_Achar's avatar
Bharath_Achar
Level 6
11 years ago
Solved

sql query for opscentre to fetch week at a glance report template.

sql query for opscentre to fetch week at a glance report template. At least how to fetch information for File system or directory or drive information of each backup job.
7.6
Backup and Recovery
Linux
NetBackup
  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    11 years ago

    Thanks,

     

    I see you're monitoring 2 masters? One has ID 61 and the 696. So the above query should work as below.

    But I also notice you're using 7.5, and I'm using 7.6. So that could be an issue as the schema's might be slightly different.

    Sorry, should have asked earlier. You can aways upgrade, as the opscenter can be higher than the master :)

     

    select
    domain_jobarchive.id as "Job ID",
    domain_jobarchive.PolicyName as "policy name",
    domain_jobarchive.scheduleName as "scedule name",
    DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'yyyy-mm-dd hh-mm-ss') as myDateTime,
    (domain_JobArchive.bytesWritten)/1024 as "KBytes Written",
    domain_JobArchive.filesBackedUp as "Files Backed up",
    nb_jobfiles.fileinfo as "file list",
    domain_Entity.name as "domain_Entity.name",#entity1.name as "#entity1.name" from domain_MasterServer , domain_Client , domain_JobArchive , domain_Entity, nb_jobfiles , (
    select * from domain_entity)#entity1 where domain_MasterServer.id = domain_JobArchive.masterServerId and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName
    and ( (domain_MasterServer.id = domain_Entity.id) )
    AND ( (domain_Client.id = #entity1.id) )
    AND ( ( (domain_JobArchive.isValid = '1') AND (domain_JobArchive.filesBackedUp NOT IN (0 ) AND (domain_JobArchive.policyType NOT IN (-1, 34 )) AND (domain_JobArchive.type NOT IN (30, 31, 32, 33, 34 )) ) AND ( (domain_JobArchive.type IN (1, 0, 7, 6 )) ) AND ( ( (domain_JobArchive.masterServerId IN (61, 696 )) ) ) ) )
    AND domain_jobarchive.id = nb_jobfiles.jobid
    AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 30

18 Replies

Sort By
Show More
  • Bharath_Achar's avatar
    Bharath_Achar
    Level 6
    11 years ago

    Hi Riaan,

     

    I didnt understand your above post. could you please let me know in detail.

  • Bharath_Achar's avatar
    Bharath_Achar
    Level 6
    11 years ago
    id networkName friendlyName GUID pbxPort preferredNetworkAddress osType hardware product versionLabel offsetFromGMT lastContact status valid catalogLastBackedUp clientsNotBackedUp downDrives catalogSize successfulJobs dataBackedUp failedJobs percentSuccess agentConfigId dataCollectorId isImageCollectionEnabled isAdvancedCollectionEnabled isErrorLogCollectionEnabled bpjavaUserName bpjavaPassword homeDirectory volumeMgrHome isLicenseDataCollectionEnabled isSubJobsCollectionEnabled versionNumber oldGuid isTraditionalLicenseDataCollectionEnabled reason osDescription hardwareDescription attemptNumber
    61 slcnpaws001.ppp.com slcnpaws001.ppp.com 5CC49AB8-E3C7-11E3-800085AEE753EAEE 1556 localhost/127.0.0.1 41 0 1 7.6.0.2 -240 1.36E+17 1 1 null null null null null null null null 1 1 0 0 0 null null null null 0 0 7060002 5CC49AB8-E3C7-11E3-800085AEE753EAEE 0 - Linux null 0
    696 slcnpny009.ppp.com slcnpny009.ppp.com BC0D78AA-C4E4-11E0-8000FD5EFB491295 1556 localhost/127.0.0.1 41 0 1 7.5.0.6 -240 1.36E+17 1 1 null null null null null null null null 1 2 0 0 0 null null null null 0 0 7050006 BC0D78AA-C4E4-11E0-8000FD5EFB491295 0 - Linux null 0
  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    11 years ago

    Thanks,

     

    I see you're monitoring 2 masters? One has ID 61 and the 696. So the above query should work as below.

    But I also notice you're using 7.5, and I'm using 7.6. So that could be an issue as the schema's might be slightly different.

    Sorry, should have asked earlier. You can aways upgrade, as the opscenter can be higher than the master :)

     

    select
    domain_jobarchive.id as "Job ID",
    domain_jobarchive.PolicyName as "policy name",
    domain_jobarchive.scheduleName as "scedule name",
    DATEFORMAT(cast(UTCBigintToNOMTime(domain_JobArchive.endTime) as DATETIME), 'yyyy-mm-dd hh-mm-ss') as myDateTime,
    (domain_JobArchive.bytesWritten)/1024 as "KBytes Written",
    domain_JobArchive.filesBackedUp as "Files Backed up",
    nb_jobfiles.fileinfo as "file list",
    domain_Entity.name as "domain_Entity.name",#entity1.name as "#entity1.name" from domain_MasterServer , domain_Client , domain_JobArchive , domain_Entity, nb_jobfiles , (
    select * from domain_entity)#entity1 where domain_MasterServer.id = domain_JobArchive.masterServerId and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName
    and ( (domain_MasterServer.id = domain_Entity.id) )
    AND ( (domain_Client.id = #entity1.id) )
    AND ( ( (domain_JobArchive.isValid = '1') AND (domain_JobArchive.filesBackedUp NOT IN (0 ) AND (domain_JobArchive.policyType NOT IN (-1, 34 )) AND (domain_JobArchive.type NOT IN (30, 31, 32, 33, 34 )) ) AND ( (domain_JobArchive.type IN (1, 0, 7, 6 )) ) AND ( ( (domain_JobArchive.masterServerId IN (61, 696 )) ) ) ) )
    AND domain_jobarchive.id = nb_jobfiles.jobid
    AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 30

  • Bharath_Achar's avatar
    Bharath_Achar
    Level 6
    11 years ago

    Am getting the o/p however am lookin for an output in similar foramt like week at a glance but for one month.

     

    Please help me on this.

  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    11 years ago
    I dont understand what you want. The week at glance run this as sql query, i added the detail about the file list.