10-14-2015 01:38 PM
I currently am on Version 7.6.0.4 (Build 20141024) of OpsCenter and I have a query that gives a list of failed backups. I need to give the user date range functionality but imbedding that code within the Query itself is yeilding only error messages :(
Can someone show me how to do this for my manual Queries. This report returns more than 4 million rows so the date range option is paramount.
Much Appreciated!!
10-14-2015 11:23 PM
PLease post what you have so far
10-15-2015 01:52 PM
Select DMS.fullyQalifiedName as Server, DJA.clientName as Client, DJA.id as "Job ID", DJA.policyName as Policy, LST.name as "Job Type", JSC.id as "Error Code", JSC.name as "Error Description"
From domain_JobArchive as DJA
Inner join domain_MediaServer as DMS on DMS.masterServerid = DJA.masterServerid
Inner join lookup_ScheduleType as LST on LST.id = DJA.scheduleType
Inner join lookup_JobStatusCode as JSC on JSC.id = DJA.statuscode
where DMS.fullyQalifiedName is NOT NULL and JSC.id not in (0,1)
order by DJA.endTime, JSC.name desc
10-15-2015 10:06 PM
You can add to browse based on job end time.
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 30
10-16-2015 07:42 AM
Thank you for your response, that is a big help.
Is there no way to prompt the user for input with the SQL Query system?
I am new to the product and need that functionality. I dont want to have to change the query everytime I want to change the date range.
10-17-2015 12:33 AM
Hi,
I'm not sure what you mean by "prompt the user for input". How are you executing the query, in OpsCenter or via some other application?
12-22-2015 10:22 AM
Hi Chris,
Try this - This query will give you detailed information of failed backups. Default here is "Last 24 hours". But you can always modify " AND DATEDIFF(hour" to either to days, months or year as you wish. Hope this help
========================================================================
SELECT networkName as 'Master Server',
max(UTCBigIntToNomTime(domain_Job.startTime)) as 'Start Time',
clientName as 'Client Name',
policyName as "Policy Name",
statusCode as "Status Code"
FROM domain_Job,domain_MasterServer,lookup_jobtype
WHERE domain_MasterServer.id = domain_Job.masterserverId
and domain_Job.scheduleName not like '%hour'
and domain_Job.scheduleName not like '%ERROR'
and domain_Job.scheduleName != 'online'
and lookup_jobtype.name = 'Backup'
and statusCode not in (0,1) and policyName NOT LIKE 'SLP_%' and type IN (0,22,28)
AND DATEDIFF(hour,UTCBigIntToNomTime(startTime), GETDATE()) <= 24
group by "Client Name","Status Code","Master Server","Policy Name"
order by networkName,clientName ASC
==========================================================-----------------------
12-28-2015 08:49 AM
If its a manually written SQL report, there is no way to ask the User executing the report for any input through OpsCenter. The report will only run with the parameters you have written in your SQL.
In order to do what you're asking, you would need to to do something like create a web page (outside OpsCenter) with an input screen. Then edit the SQL on the fly inserting the user defined parameters before running it against the OpsCenter DB remotely and showing the results.