cancel
Showing results for 
Search instead for 
Did you mean: 

How do I get User Input for a manual SQL Query

ChrisWood214
Level 2

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!!

7 REPLIES 7

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

PLease post what you have so far

ChrisWood214
Level 2

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

You can add to browse based on job end time.

 

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

ChrisWood214
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. 

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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?

Dollypee
Moderator
Moderator
   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_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

==========================================================-----------------------

areznik
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.