5 Replies Latest reply on Jan 15, 2014 7:50 AM by olsen86

    Query the metadata for last saved stats

    New Member

      I need to query the metadata to see when a workflow was last saved and the user who saved it? Can someone help me out with this?

        • 1. Re: Query the metadata for last saved stats
          Guru

          The date when a workflow was saved for the last time is available in the Metadata Exchange view (MX view) REP_WORKFLOWS (please see the Repository Guide for more details about the MX views).

           

          The user ID can be obtained from the MX view REP_VERSION_PROPS; look for OBJECT_ID = (workflow ID from REP_WORKFLOWS) and OBJECT_TYPE = 71 (workflow), then you will find the USER_ID which you can join with the MX view REP_USERS.

           

          Regards,

          Nico

          • 2. Re: Query the metadata for last saved stats
            Guru

            The below query should give you the expected result:

             

             

            SELECT *

              FROM (SELECT s.subj_name subject_area, 'Mapping' parent_type,

                           m.mapping_name, t.object_type_name object_type,

                           wi.instance_name object_name, u.user_name, p.saved_from,

                           p.last_saved last_saved

                      FROM opb_widget w,

                           opb_object_type t,

                           opb_widget_inst wi,

                           opb_mapping m,

                           opb_subject s,

                           opb_version_props p,

                           rep_users u

                     WHERE w.widget_type = t.object_type_id

                       AND w.widget_id = wi.widget_id

                       AND w.widget_type = wi.widget_type

                       AND wi.mapping_id = m.mapping_id

                       AND m.subject_id = s.subj_id

                       AND w.widget_id = p.object_id

                       AND w.widget_type = p.object_type

                       AND p.user_id = u.user_id

                    UNION

                    SELECT DISTINCT ISNULL (w.subject_area, 'UNKNOWN') subject_area,

                                    w.task_type_name parent_type,

                                    ISNULL (w.task_name, 'UNKNOWN') parent_name,

                                    ti.task_type_name object_type,

                                    ti.instance_name object_name, u.user_name,

                                    p.saved_from,

                                    p.last_saved

                               FROM rep_task_inst ti INNER JOIN

                                    rep_version_props p ON (  ti.task_type = p.object_type

                                AND ti.task_id = p.object_id) LEFT OUTER JOIN

                                    (SELECT subject_area, task_name, task_id,

                                            task_type_name

                                       FROM rep_all_tasks

                                      WHERE task_type_name IN ('Workflow', 'Worklet')) w ON ti.workflow_id = w.task_id INNER JOIN

                                    rep_users u ON  p.user_id = u.user_id

                                                ) tab

            • 3. Re: Query the metadata for last saved stats
              New Member

              Thank you for your help Nico!! This was exactly what I needed to know!

              • 4. Re: Query the metadata for last saved stats
                Guru

                Did you try executing the query that I posted?

                 

                We use this query to get the information of all the objects, their last saved time, user that saved it and the machine from which it was saved..

                • 5. Re: Query the metadata for last saved stats
                  New Member


                  I did, thank you Yogesh!