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

  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    11 years ago

    Do you want the full list of backed up files, as in bplist? Or the Incude lift of the policy?

  • Bharath_Achar's avatar
    Bharath_Achar
    Level 6
    11 years ago
    By default week at a glance gives information about each stream weather it is / or /var for Linux or c: \ or D:\ for windows. I want how to query the db to get this information. And not the contents of drive or directory.
  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    11 years ago

    Hello

     

    give this a try :)

     

    The last line is controlling how far back you look, in this case 4 days. If you want more fields let me know.

     

    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"
    from domain_jobarchive, nb_jobfiles
    where domain_jobarchive.id = nb_jobfiles.jobid
    AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 4

     

  • Bharath_Achar's avatar
    Bharath_Achar
    Level 6
    11 years ago

    Hi,

     

    Am looking for SQL query that gives exactly similar to week at a glance report but for one month in csv or tsv format.

    please help me on this.

    With first column as

    swcnpde119.advancemags.com->C:\
    swcnpde119.advancemags.com->E:\
    swcnpde250.advancemags.com->Shadow Copy Components:\
    swcnpde018.advancemags.com->D:\
    swcnpde317.advancemags.com->C:\
    swcnpde317.advancemags.com->D:\
    swcnpde317.advancemags.com->F:\
    swacsaws004.advancemags.com->Shadow Copy Components:\
    swcnpde295.advancemags.com->Shadow Copy Components:\
    swcnpde207.advancemags.com->C:\
    swcnpde119.advancemags.com->Shadow Copy Components:\
    swcnpde160.advancemags.com->Shadow Copy Components:\
    swcnpde219.advancemags.com->D:\
    swcnpde210.advancemags.com->E:\
    swcnpde317.advancemags.com->Shadow Copy Components:\
    swcnpde206.advancemags.com->C:\
    swcnpde210.advancemags.com->SET SNAP_ID=swcnpde210.advancemags.com_1408856521
    slcnpde198.advancemags.com->/oracle
    slacsaws001.advancemags.com->/nfs_shares/livelink_test
    slcnpaws004.advancemags.com->/oraexp/nss
    slnssde036.advancemags.com->/app
    slcnpaws004.advancemags.com->/oraexp/cnp
    slcnpde136.advancemags.com->/oramisc
    slcnpde093.advancemags.com->/app
    slcnpde163.advancemags.com->/oracle
    slcnpde208.advancemags.com->/oramisc
    slcnpde177.advancemags.com->/oramisc
    slnssde029.advancemags.com->/boot
    slcnpde099.advancemags.com->/app
    slnssde024.advancemags.com->/app
    slcnpaws004.advancemags.com->/oraexp/other
    slcnpde162.advancemags.com->/
    slcnpde162.advancemags.com->/app
    slcnpde102.advancemags.com->/
    slcnpde208.advancemags.com->/
    slcnpde178.advancemags.com->/data
    slcnpde081.advancemags.com->/app
  • Bharath_Achar's avatar
    Bharath_Achar
    Level 6
    11 years ago

    Thanks Riaan,

     

    Am looking for SQL query that gives similar to week at a glance report but for 1 month.

  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    11 years ago

    What else you want?

     

    Is this better?

     

    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 )) ) ) ) )
    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

    Hi Riaan, 

     

    Am getting error as below -

    An unknown exception has occurred.
    To continue, click on any tab.

  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    Level 6
    11 years ago

    Sorry, i put my master server id in there (61). Please run

     

    select * from domain_masterserver

     

    and send me the output.