Automic Workload Automation

Expand all | Collapse all

SQL query to list all objects (and folders) under a given folder

  • 1.  SQL query to list all objects (and folders) under a given folder

    Posted Nov 23, 2015 10:10 AM
    We discovered last week that there is no straightforward way to restrict the number of results normal users can retrieve from searches without also impacting searches performed by administrators and those performed by our own internal processes, The Automation Engine applies the same limit to all searches:
    • Searches performed in the Java user interface
    • Searches performed in the ECC
    • Searches performed by normal users
    • Searches performed by administrative users
    • Searches performed by an external program, using the SearchObjectclass of the Java Application Interface. 

    Regardless of who performs the search or how, the search is subject to the limit specified in GENERIC_SEARCH_LIMIT in UC_SYSTEM_SETTINGS in client 0.

    Because the Automation Engine provides no mechanism for finer-grained control, we are trying to find an alternative approach. My first idea was that we could perform our own internal searches using direct SQL queries of the Automation Engine database. I thought before I invested the effort in developing these queries , I should check here first to see if anyone had already done something similar. We need two queries:
    1. List allobjectsunder a specified folder, including under all sub-folders.
    2. List allfoldersunder a specified folder, including under all sub-folders.

    Has anyone else done this before? Thanks in advance for any help.


  • 2.  SQL query to list all objects (and folders) under a given folder
    Best Answer

    Posted Nov 23, 2015 10:38 AM
    Would either of these from Philipp Elmer's site be of use as a basis for your needs?

    Statistics of UC4-JOBS inside a certain folder

    SQL: Folder Path of an Object


  • 3.  SQL query to list all objects (and folders) under a given folder

    Posted Nov 24, 2015 04:22 AM
    Would either of these from Philipp Elmer's site be of use as a basis for your needs?

    Statistics of UC4-JOBS inside a certain folder

    SQL: Folder Path of an Object
    The queries in those two posts are certainly helpful, and a step in the right direction.

    I have now working queries that will recursively list all of the child objects (or folders) of a given folder, given the unique OH_Idnr of the folder. What I need now is a query that will get the unique OH_Idnr of a specified folder path.

    E.g, convert a path like
    • /0100/APPS/UC4/COMMON
    • \0100\APPS\UC4\COMMON
    to:
    • the exact OH_Idnr of that folder path, e.g., 153402.




  • 4.  SQL query to list all objects (and folders) under a given folder

    Posted Nov 27, 2015 10:41 AM
    Related question...

    I have a query given to me by Automic that will list all of the folder paths of an object.
    select path from
    (select level
    ,ofs_oh_idnr_f as parent
    ,ofs_oh_idnr_o as child
    ,oh_name
    ,substr(oh_name,10,99)
    ,SYS_CONNECT_BY_PATH( substr(oh_name,10,99) , '/' ) as path
    ,ofs.ofs_link
    from ofs, oh
    where oh.oh_idnr = ofs_oh_idnr_f
    and ofs_oh_idnr_o = (select oh_idnr from oh where oh_name = 'UC0.MAL.TEST1#1.JOBI')
    start with ofs_oh_idnr_f = (select oh_idnr from oh where oh_otype = 'CLNT' and oh_client = 110)
    connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f) where ofs_link = 1;
    As configured now, with the 'where ofs_link=1' predicate, it will list only the locations of links, and not the object’s home folder.

    The problem I am running into now is that I have begun to encounter some situations wherein the path returned by this query contains backslash characters. E.g., the object UC0.MAL.TEST1#1.JOBI has exactly one link, located in
    '/APPS/UC0/MAL/TEST1/1/2'. However, the path returned by the query is '/APPS/UC0/MAL/TEST1#A/\1/\2'. Note that the backslashes prefix only the two deepest levels in the folder hierarchy.

    I have only begun to see this problem recently, in the past month or so. It never happened before. Any ideas?


  • 5.  SQL query to list all objects (and folders) under a given folder

    Posted Nov 27, 2015 12:08 PM
    I figured out what’s going on. The object IDs in our system have recently grown beyond 7 digits. Automic’s query is based on the assumption that all OH_IDnr values are 7 digits in length. The SUBTR in the original query always starts at position 10.

    Comparing the values of OH_NAME for an older folder object and a subfolder created more recently, it’s clear that the assumption that the folder name begins at position 10 does not always hold:
                1234567890
    Old folder: 1713155k\TEST1#A
    New folder: 10760005k\2
    I modified the SQL query so that returns correct results reliably.
    select replace(path,'|', '/') from
    (
    select level
    ,ofs_oh_idnr_f as parent
    ,ofs_oh_idnr_o as child
    ,oh_name
    ,substr(oh_name,10,99)
    ,SYS_CONNECT_BY_PATH( substr(oh_name,instr(oh_name,'k\',1)+2) , '|' ) as path
    ,ofs.ofs_link
    from ofs, oh
    where oh.oh_idnr = ofs_oh_idnr_f
    and ofs_oh_idnr_o =
    (select oh_idnr from oh where oh_name = 'UC0.MAL.TEST1#1.CALL')
    start with ofs_oh_idnr_f =
    (select oh_idnr from oh where oh_otype = 'CLNT' and oh_client = 110)
    connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f
    )
    where ofs_link = 0;
    The changes, compared to the query provided by Automic are:
    1. Instead of starting the SUBSTR at position 10, the query starts two characters after the position of 'k\'.
    2. The SUBSTR is no longer constrained to 99 characters.
    3. The path is initially connected using the '|' character, and then this character is replaced with '/' in the outer query. This was necessary because if '/' was used as the separator character forSYS_CONNECT_BY_PATH, the following error was returned:
      ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have separator as part of column value
      I do not know why this error does not occur when using a fixed integer as the starting position for SUBSTR


  • 6.  SQL query to list all objects (and folders) under a given folder

    Posted Nov 29, 2015 04:10 AM
    I still need an SQL statement that will take a path like /0100/APPS/UC4/COMMON or \0100\APPS\UC4\COMMON and convert it to the exact OH_Idnr of that folder path, e.g., 153402. Can anyone help?

    Update: PhilippElmer answered the question above.


  • 7.  SQL query to list all objects (and folders) under a given folder

    Posted Dec 03, 2015 08:02 AM
    Hi Michael,

    here’s a query that will return a list of full folder paths and their OH_Idnrs, you can use it as a lookup-table (or inline view) to construct your final query.

    with AllFolderPaths (Idnr, FolderPath) AS (
          --find all valid Clients on the UC4 system
          select OH_IDnr,
          --T-SQL:
          --CAST(OH_Name as VARCHAR(MAX))
          --Oracle:
          OH_Name
          from OH
          where OH_OTYPE = 'CLNT'
          and OH_Name between '0000' and '9999'
          UNION ALL
          --find all FOLDers on all clients
          select OFS_OH_IDNR_O,
          --T-SQL:
          --FolderPath + SUBSTRING(OH_Name, CHARINDEX('\', OH_Name), 200)
          --Oracle:
          FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
          from AllFolderPaths
          inner join OFS on Idnr = OFS_OH_Idnr_F
          inner join OH on OFS_OH_Idnr_O = OH_IDNR
          where OH_OTYPE = 'FOLD'
    )
    select Idnr, FolderPath
    from AllFolderPaths
    order by FolderPath;

    Kind regards,

    Philipp



  • 8.  SQL query to list all objects (and folders) under a given folder

    Posted Dec 03, 2015 09:28 AM
    Thanks to PhilippElmer, I now have queries that will list all objects or folders under a specified folder path.
    with
    -- List all folder paths and their object IDs
    AllFolderPaths (Idnr, FolderPath) AS (
          --find all valid Clients on the UC4 system
          select OH_IDnr,
          --T-SQL:
          --CAST(OH_Name as VARCHAR(MAX))
          --Oracle:
          OH_Name
          from OH
          where OH_OTYPE = 'CLNT'
          and OH_Name between '0000' and '9999'
          UNION ALL
          --find all FOLDers on all clients
          select OFS_OH_IDNR_O,
          --T-SQL:
          --FolderPath + SUBSTRING(OH_Name, CHARINDEX('\', OH_Name), 200)
          --Oracle:
          FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
          from AllFolderPaths
          inner join OFS on Idnr = OFS_OH_Idnr_F
          inner join OH on OFS_OH_Idnr_O = OH_IDNR
          where OH_OTYPE = 'FOLD'
    ),

    -- List all child objects under a folder, given the folder OH_IDnr
    child_objects as
    (
    select OFS_OH_IDNR_O
    from OFS
    start with OFS_OH_IDNR_F =
      (
        select OH_IDNR from OH where OH_OTYPE='FOLD'
        and OH_Idnr = (
         select Idnr
         from AllFolderPaths
         where FolderPath = '0001\APPS\UC0\MAL'
         )
        and OH_CLIENT=0001
      )
    connect by prior OFS_OH_IDNR_O = OFS_OH_IDNR_F
    ),

    -- List child object names, IDs, and object types
    child_objects_with_names as
    (
    select OH_name, OH_Idnr, OH_OType from OH,child_objects
    where OH.OH_Idnr = child_objects.OFS_OH_IDNR_O
    )

    -- List all objects under a folder path
    select * from child_objects_with_names
    where OH_OType <> 'FOLD'
    To list all folders under a specified folder path, just replace the final SELECT statement with this:
    -- List all folders under a folder path
    select Idnr, FolderPath
    from AllFolderPaths
    where FolderPath like '0001\APPS\UC0\MAL\%'
    The bits in bold italics obviously need to be changed depending on the client number and path in which you want to search.

    Thanks again to Philipp for his valuable assistance.


  • 9.  SQL query to list all objects (and folders) under a given folder

    Posted May 11, 2016 09:55 AM
    This morning I resumed testing of the updated SQL query (above) for listing an object’s home folder location, and the locations of any links. I saw something I had not seen before:

                                                                         
    LEVELPARENTCHILDOH_NAMEOBJ_NAMEPATHOFS_LINK
    5158193723323101078961k\TEST#1TEST#1|100|APPS|UC0|MAL|TEST#10
    5267332423323101078961k\TEST_LINKSTEST_LINKS|100|APPS|UC0|MAL|TEST_LINKS1
    8257575723323102575755k\TEST1TEST1|100|SYSTEM|USERS|46JZ8/CORP|UC0|MAL|TEST|TEST11
    7257585823323102575856k\TEST1TEST1|100|SYSTEM|USERS|46JZ8/CORP|MAL|TEST|TEST11

    The first result is the home folder location of the object. The second result corresponds to a link. From what I have been able to discover, the third and fourth results correspond to favorites. (In the folder hierarchy, these links are located under my user ID, and I can see these folder structured replicated in the User Interface under my favorites.)

    The SYS_CONNECT_BY_PATH does not work as expected in situations wherein OH_Name does not contain the string  \k. Any ideas for a good way to exclude favorites from the results?


  • 10.  SQL query to list all objects (and folders) under a given folder

    Posted May 11, 2016 10:44 AM
    I modified the query a bit to build the path differently in the case of favorites, and to identify such links based on the path.
    select path, ofs_link,
    case when instr(path, 'SYSTEM|USERS',1) = 0
      then 0
      else 1
    end as favorite
    from (
    select level
    ,ofs_oh_idnr_f as parent
    ,ofs_oh_idnr_o as child
    ,oh_name
    ,case when oh_otype='FOLD'
       then substr(oh_name,instr(oh_name,'k\',1)+2)
       else oh_name
       end as obj_name
    ,SYS_CONNECT_BY_PATH(
       case when oh_otype='FOLD'
       then substr(oh_name,instr(oh_name,'k\',1)+2)
       else oh_name
       end,'|') as path
    ,ofs.ofs_link
    from ofs, oh
    where oh.oh_idnr = ofs_oh_idnr_f
    --and oh_otype = 'FOLD'
    and ofs_oh_idnr_o =
    (select oh_idnr from oh where oh_name = 'UC0.MAL.TEST1#1.CALL')
    start with ofs_oh_idnr_f =
    (select oh_idnr from oh where oh_otype = 'CLNT' and oh_client = 100)
    connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f
    )
    where ofs_link = 1
    --and favorite = 0
    This works until I uncomment the last line. For some reason, then Oracle returns the error:
    ORA-00904: "FAVORITE": invalid identifier

    I have no idea why this error is occurring — maybe anOracle bug related to SYS_CONNECT_BY_PATH. Next, I’ll try modifyingPhilipp Elmer’srecursive query, to see if I have more success. I’m sure there’s a more elegant way to do it!



  • 11.  SQL query to list all objects (and folders) under a given folder

    Posted May 11, 2016 12:02 PM
    Ok, this works:
    with AllFolderPaths (Idnr, FolderPath) AS (
      select OH_IDnr,
      OH_Name
      from OH
      where OH_OTYPE = 'CLNT'
      and OH_Name between '0000' and '9999'
      UNION ALL
      select OFS_OH_IDNR_O,
      FolderPath || SUBSTR(OH_Name, INSTR(OH_Name, '\'))
      from AllFolderPaths
      inner join OFS on Idnr = OFS_OH_Idnr_F
      inner join OH on OFS_OH_Idnr_O = OH_IDNR
      where OH_OTYPE = 'FOLD'
    )

    select * from AllFolderPaths
    where Idnr in
    ( select OFS_OH_IDNR_F
      from OFS
      where OFS_OH_Idnr_O =
      ( select OH_IDNR
        from oh
        where oh_name = 'UC0.MAL.TEST1#1.CALL'
      )
      and OFS_Link = 1
    )

    If anyone has suggestions for further improvements, please feel free to post them here.



  • 12.  SQL query to list all objects (and folders) under a given folder

    Posted May 12, 2016 05:50 AM
    Here is another query that works, based on Matthew Bailey’s very handy query from a couple of years ago.

    -- Based on query provided by Matthew Bailey at Automic
    -- https://community.automic.com/discussion/comment/2673/#Comment_2673
    with object_paths as (
    select (select OH_Client from oh where oh_idnr = child)  Client
         , (select OH_OType  from oh where oh_idnr = child)  Type
         , (select OH_Name   from oh where oh_idnr = child)  Object_Name
         , Path
         , ofs_link                                          Link
         ,case when instr(Path, 'SYSTEM|USERS',1) = 0
            then 0
            else 1
          end as Favorite
      from (select level
                 , ofs_oh_idnr_f                             as parent
                 , ofs_oh_idnr_o                             as child
                 , oh_name
                 , SYS_CONNECT_BY_PATH(
                   case when oh_otype='FOLD'
                     then substr(oh_name,instr(oh_name,'k\',1)+2)
                     else oh_name
                   end,'|') as Path
                 , ofs_link
              from ofs
                 , oh
             where oh_idnr            = ofs_oh_idnr_f
             start with ofs_oh_idnr_f = (select oh_idnr
                                           from oh
                                          where oh_otype  = 'CLNT'
                                            and oh_client = &CLIENT)
             connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f)
    )
    select * from object_paths
    where Object_Name                = &OBJECT
    --and Link                         = &Link
    --and Favorite                     = &Favorite
    order by Path, Type, Object_Name, Link, Favorite

    Note that this query contains the assumption that all USER objects are stored under the folder path/SYSTEM/USERS. Links appearing under this path are assumed to befavorites. I opted to use the vertical bar character ‘|’ as a path delimiter because it is a character that can appear in neither object names nor folder names. (The forward slash character ‘/’ can appear in the names of USER objects.)



  • 13.  SQL query to list all objects (and folders) under a given folder

    Posted Oct 26, 2017 12:04 PM
    Please help me with the query to find out details of the objects executed between the specific time frame.


  • 14.  SQL query to list all objects (and folders) under a given folder

    Posted Oct 26, 2017 12:17 PM
    Amarnath Reddy said:
    Please help me with the query to find out details of the objects executed between the specific time frame.
    I suggest you start a new topic for this question.


  • 15.  RE: SQL query to list all objects (and folders) under a given folder

    Posted Jul 23, 2019 10:51 AM
    HI Michael,

    Can you please help here to get the objects.

    I am not able to execute the query given above, its throwing syntax error.

    The point here i mention is that how we can get object without a table  "AllFolderPaths" where you have used below 

    select * from AllFolderPaths
    where Idnr in
    ( select OFS_OH_IDNR_F
      from OFS
      where OFS_OH_Idnr_O =
      ( select OH_IDNR
        from oh
        where oh_name = 'UC0.MAL.TEST1#1.CALL'
      )
      and OFS_Link = 1
    )



  • 16.  RE: SQL query to list all objects (and folders) under a given folder

    Posted Jul 24, 2019 04:39 AM
    Edited by Michael A. Lowry Jul 24, 2019 04:40 AM
    AllFolderPaths is a common table expression earlier in the same SQL statement. See this comment.