You are viewing limited content. For full access, please sign in.

Question

Question

SQL Query to get the number of workflows by repository name or ID

Workflow
Updated December 19, 2018
asked on December 17, 2018

Using workflow 10.2. I have created a SQL stored procedure to pull daily metrics on our Laserfiche implementation from the Directory server and workflow databases into a reporting table which drives a Power BI
dashboard.  I am trying to query to find the number of workflows per day by status per repository.  I am getting some counts from joining up the search table but this does not seem to be returning all workflows. I know this is possible  but don't see a way to join the workflow ID to the repository either through the connection or ??.   Can someone familiar with the schema provide an example join to get this?

0 0

Answer

SELECTED ANSWER
replied on December 17, 2018

For performance reasons, Workflow keeps 2 sets of tables, one for running instances and the other for completed ones. It sounds like you're querying the search_ tables (running instances) but not the search_*_log ones (completed instances).

1 0
View 1 previous reply
replied on December 17, 2018

Thanks, this is what I am using for completed workflows:

select count(SI.instance_id)
 from LFWF.dbo.search_instance_log as SI
           left join LFWF.dbo.search_entry_log as SE on SI.search_id = SE.search_id AND SE.is_starting_entry = 1
           left join LFWF.dbo.search_status_log as SS on SI.search_id = SS.search_id    
           left join LFWF.dbo.search_rep SR on SR.repository_id = SE.repository_id 

WHERE    SR.repository_name = 'XXXX'
               AND SS.start_time >= dateadd(Day,-1,@Today)
               AND SS.status = 1 

0 0
replied on December 17, 2018

I'm not sure if you're trying to ask a question or just posting the query for other people's benefits. But...your day (@Today)) is in local timezone while the tables are in UTC.

0 0
replied on December 18, 2018

Good Point, thank you.  I was just looking for confirmation that the query is the best approach for producing the count.

0 0
replied on December 19, 2018

Is there another way to get to the repository_Name for the workflow without going through the entry?  Is there a way to get it from the connection used by the workflow? The problem is that I am missing the count of workflows which do not have a defined starting entry.

0 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...