Forum Discussion

ChrisWood214's avatar
9 years ago

How do I get User Input for a manual SQL Query

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

  • 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

  • You can add to browse based on job end time.

     

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

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

  • 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?

  • 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

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

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