I currently am on Version 220.127.116.11 (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.
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
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.
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?
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"
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
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.