0 Replies Latest reply on Apr 18, 2014 10:22 AM by owlx

    Subqueries in a URL using core.executeQuery

    owlx

      I'm trying to figure out how to form a URL to get the results I want using core.executeQuery.

       

      What I'm trying to find is all the ServerTasks that have:

      1. Specific Names (the ones I'm looking for start with UDT)

      2. Run in the past 24-hours

      3. not completed (failed, terminated, etc)

      4. do not have a completed task in the past 24-hours

       

      Basically, if a task fails 5 times and completes 1 time in a 24-hour period, I don't want that in the list.

       

      I can do this through SQL with this:

       

      select distinct name

      from OrionTaskLogTask

      where Name like N'UDT%'

          and StartDate >= DATEADD(hh,-24,getdate())

          and (Status is null or Status <> 0)

          and Name not in (select distinct name

              from OrionTaskLogTask

              where Name like N'UDT%'

                  and StartDate >= DATEADD(hh,-24,getdate())

                  and Status = 0)

       

      I can get a list of all the not completed tasks:

      https://<server>:<port>/remote/core.executeQuery?

      target=OrionTaskLogTask&select=(select (distinct) OrionTaskLogTask.Name)&where=(where (and (startsWith OrionTaskLogTask.Name "UDT") (newerThan OrionTaskLogTask.StartDate 86400000) (ne OrionTaskLogTask.Status 0)))

       

      The problem with this is it doesn't remove the Name from the list if it HAS completed at least once in the past 24 hours.

       

      Is there some way possible to add in the "where" condition, a "not in" operator, on a subquery?

       

      Something like (not in OrionTaskLogTask.Name <SUBQUERY>)