Forum Discussion

AlanTLR's avatar
AlanTLR
Level 5
12 years ago

Symantec OpsCenter Analytics Custom SQL Query Paging Not Populating

Hi all.  I'm trying to create a custom SQL query to list all my scratchpool tapes, but it returns a result of over 300  results in 7 pages.  The SQL is correct and gives the correct results in dbisqlc.  In the GUI, the first page is correct, but when I click on all the other pages, they have the same content, plus a few extra lines.

The query I'm trying to run is generated from the Available Media Report, which works as intended (i.e., each page does not show the same list of tapes).

Here is my query:

 

SELECT TOP 1000 START AT 1 domain_Media.barcode AS "Barcode", domain_Media.volumePoolName AS 

"Volume Pool" FROM domain_Media

WHERE ( ( domain_Media.volumePoolName IN ( 'ScratchPool' ) ) AND ( domain_Media.isValid = 1 ) AND ( ( domain_Media.id LIKE '002%' )  ) );
 
As you can see, I'm trying to get barcodes of all my scratchpool tapes that start with a certain series of numbers.
 
Any Ideas?   I'm currently running 7.5.0.4 Solaris SPARC on the Master and Media Servers, and 7.5.0.0 on OpsCenter Analytics on Solaris x86.
Also note that I can also generate this list by going to the "Monitor->Media" tab, and works correctly, but is neither printable nor exportable.
Backup and Recovery
OpsCenter
  • inn_kam's avatar
    inn_kam
    Level 6
    12 years ago

     

    I have not much experience with OpsCenter or the older Backup Reporter, what we used to do with keeping track of media usage is by writing script. 

    Use of bpmedialist or bpimagelist can tell us what mediaID are being used for daily or weekly backup etc.

     

    Sorry Vaibhav, I can't share with you the entire script, but I can provide you what commands we used in it:

    # bpimagelist -media -idonly -hoursago 144 -st full    ==> Retrieve all full backup mediaID used in last 144 hours (6 days).

    For daily report, you can use -hoursago 24 and exclude -st, that would give you all full/incr/cinc. Depend on your requirement.

    In my script, as we want to offiste the full backup media, we use:

    # vmquery  -l -m <mediaID>    

    for those mediaID we found in first command output, that would give us the slot number in the library and what volume pool they belong.

    Hope this helps.

     

    COPIED from watson link

    https://www-secure.symantec.com/connect/forums/use-scratch-pool#comment-5451361

    https://www-secure.symantec.com/connect/forums/use-scratch-pool#comment-5453751

  • AlanTLR's avatar
    AlanTLR
    Level 5
    12 years ago

    I have no problem writing the script, but I want to be able to give the Ops/Report access without shell access, nor having to create another web page for them to log in.  I want my Report/Ops people to be able to log into one interface (OpsCenter) to find what they need.

  • sgt_why's avatar
    sgt_why
    Level 3
    12 years ago

    AlanTLR --

    We are running OpsCenter 7.5.0 on linux with (2) Master servers running 7.5.0.1 on Solaris and Linux.

    I have seen the same issues you just mentioned ... create SQL query which generates any more than a page of data. And the tabs are created properly, but clicking on the page numbers just redisplays the same data as was shown on page 1.

    A few weeks ago, it would work if we emailed it as a CSV ... but now even the "email as" or export options seem to also truncate the data when exporting.

    So, I would be very interested in any resolution you may find -

     

     

     

  • MilesVScott's avatar
    MilesVScott
    Level 6
    12 years ago

    I altered your query a bit but I see everything fine and am able to export. However we are using a windows box.

    SELECT  dm.barcode AS 'Barcode'
            , dm.volumePoolName AS 'Volume Pool'
           FROM domain_Media dm
    WHERE dm.isValid = 1
            AND dm.volumePoolName = 'ScratchPool'
            AND dm.id LIKE '002%'
    ORDER BY Barcode
     

     

  • MilesVScott's avatar
    MilesVScott
    Level 6
    12 years ago

    Oh wait.... I see this now.

     

    You have to actually save the report. The preview will show the same data on all pages, however after the report has been saved it should return only unique data.

  • tom_sprouse's avatar
    tom_sprouse
    Level 6
    12 years ago

    AlanTLR, 

    First, please upgrade your OpsCenter host to 7.5.0.5 as your OpsCenter host should be at or higher then your master server.

    OpsCenter 7.5.0.5 - Solaris x86 - http://www.symantec.com/docs/TECH199264

    There were previous versions of OspCenter that had issues with custom SQL not using pagination properly.

    If 7.5.0.5 does not correct this issue... please let me know.

    --Tom

     

  • tom_sprouse's avatar
    tom_sprouse
    Level 6
    12 years ago

    AlanTLR,

    I actually just tried the following in a 7.5.0.5 lab and it is correctly paginating the data... 

    This is a slight variation from you script - just for testing in my lab...

    SELECT TOP 1000 START AT 1 

domain_Media.barcode AS "Barcode",
domain_Media.id "id",
domain_Media.volumePoolName AS "Volume Pool" 

FROM domain_Media
WHERE domain_Media.volumePoolName LIKE 'Scratch%'
AND domain_Media.isValid = 1
ORDER BY id

     

  • Wilhelm_Olander's avatar
    Wilhelm_Olander
    Level 2
    12 years ago

    I had a similar problem using a group by clause on OpsCenter 7.5.0.6.
    Only the first page was correct. All the other pages were copies of the first page.

    The problem seems to be the trailing semi-colon (;)

    I removed it and everything was fine.

  • sgt_why's avatar
    sgt_why
    Level 3
    12 years ago

    CONFIRMED --- Have had the problem with custom SQL reports not paginating properly for awhile now. And as Wilhelm suggested, the solution was to remove the trailer semicolon (;) at the end of the query.

    Funny thing is, I had opened (3) separate tickets with support on this and never got a clear answer. When I mentioned the solution found here, I was told "oh, we don't support custom queries."

    Although my intention was to actually point out the "bug" in the system ... it should either work with the trailing semicolon, or check for it and generate an error when you try to run the query.