Showing results for 
Search instead for 
Did you mean: 

How do I get User Input for a manual SQL Query

Level 2

I currently am on Version (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!!


Partner    VIP    Accredited Certified

PLease post what you have so far

Level 2

Select DMS.fullyQalifiedName as Server,  DJA.clientName as Client, as "Job ID", DJA.policyName as Policy, as "Job Type", as "Error Code", 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 = DJA.scheduleType
Inner join lookup_JobStatusCode as JSC on = DJA.statuscode

where  DMS.fullyQalifiedName is NOT NULL and not in (0,1) 
order by DJA.endTime, desc

Partner    VIP    Accredited Certified

You can add to browse based on job end time.


AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 30

Level 2

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. 

Partner    VIP    Accredited Certified



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?

   VIP    Certified

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_Job.masterserverId
and domain_Job.scheduleName not like '%hour'
and domain_Job.scheduleName not like '%ERROR'
and domain_Job.scheduleName != 'online'
and = '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


Level 5

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.