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%' ) ) );
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
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.
We are running OpsCenter 7.5.0 on linux with (2) Master servers running 18.104.22.168 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 -
I altered your query a bit but I see everything fine and am able to export. However we are using a windows box.
First, please upgrade your OpsCenter host to 22.214.171.124 as your OpsCenter host should be at or higher then your master server.
OpsCenter 126.96.36.199 - 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 188.8.131.52 does not correct this issue... please let me know.
I actually just tried the following in a 184.108.40.206 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
I had a similar problem using a group by clause on OpsCenter 220.127.116.11.
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.
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.