I went through some tables in the database but can't find where it is stored at.
I made a database view that roughly mimics the Monitor page in the database. Some of the differences are that it shows a line for each user when a form is assigned to multiple users, when a form is assigned to a team, it lists the manager(s) for the team, and when a form is not assigned to any users (like if it's waiting on a timer event) it lists assigned to an administrator account, but otherwise it looks the same as the Monitor page.
I'm using it for LFForms Version 11.0.2212.30987 and it matches pretty close to the Monitor page. I have noticed that occassionally reassigned tasks show for both the original owner and the reassigned owner, and I haven't been able to resolve that, but otherwise it's really close to what the application shows on the Monitor page.
I use this for various reports and BI processes.
Be aware that Laserfiche does not usually announce or point out when they make changes to the database structure, so you do run some risk with something like this breaking after an upgrade.
SELECT [Process Name], [Instance name], [Started by], [Last updated], [Assigned to], [Assigned to (username)], [Assigned to (email)], [Current step], [Step start date], [Start date], [Instance ID], [Step due date]
FROM (SELECT instance.bp_name AS [Process Name], instance.title AS [Instance name], CASE WHEN start_user.displayname LIKE '%WORKFLOW%' THEN 'Workflow' ELSE start_user.displayname END AS [Started by],
instance.lastacted_date AS [Last updated], CASE WHEN bp_worker_resume.status = 5 AND assigned_user_snapshot.displayname IS NOT NULL
THEN assigned_user_snapshot.displayname WHEN bp_worker_resume.status = 5 AND assigned_user_snapshot.displayname IS NULL
THEN assigned_user_record.displayname WHEN bp_worker_resume.status = 2 THEN assigned_user_snapshot.displayname WHEN assigned_user_team_snapshot.displayname IS NULL AND
teams.name IS NOT NULL THEN CONCAT(teams.name, ' (Listing the Team Manager)') WHEN assigned_user_team_snapshot.displayname IS NULL
THEN teams.name WHEN bp_worker_resume.status = 1 THEN assigned_user_team_snapshot.displayname END AS [Assigned to], CASE WHEN bp_worker_resume.status = 5 AND
assigned_user_snapshot.username IS NOT NULL THEN assigned_user_snapshot.username WHEN bp_worker_resume.status = 2 AND assigned_user_snapshot.username IS NOT NULL
THEN assigned_user_snapshot.username WHEN assigned_user_team_snapshot.displayname IS NULL AND team_users.username IS NOT NULL
THEN team_users.username WHEN bp_worker_resume.status = 1 AND assigned_user_team_snapshot.username IS NOT NULL
THEN assigned_user_team_snapshot.username ELSE 'Laserfiche Administrator' END AS [Assigned to (username)], CASE WHEN bp_worker_resume.status = 5 AND
assigned_user_snapshot.displayname IS NOT NULL AND assigned_user_snapshot.email IS NOT NULL THEN assigned_user_snapshot.email WHEN bp_worker_resume.status = 5 AND
assigned_user_snapshot.displayname IS NULL AND assigned_user_record.email IS NOT NULL THEN assigned_user_record.email WHEN bp_worker_resume.status = 2 AND
assigned_user_snapshot.email IS NOT NULL THEN assigned_user_snapshot.email WHEN assigned_user_team_snapshot.displayname IS NULL AND team_users.email IS NOT NULL
THEN team_users.email WHEN bp_worker_resume.status = 1 AND assigned_user_team_snapshot.email IS NOT NULL
THEN assigned_user_team_snapshot.email ELSE 'PUT_YOUR_OWN_ADMINISTRATOR_EMAIL_ADDRESS_HERE' END AS [Assigned to (email)], CASE WHEN bp_worker_resume.step_name IS NOT NULL
THEN bp_worker_resume.step_name ELSE bp_steps.name END AS [Current step], CASE WHEN bp_worker_resume.assign_date IS NOT NULL
THEN bp_worker_resume.assign_date ELSE bp_worker.update_date END AS [Step start date], instance.start_date AS [Start date], instance.bp_instance_id AS [Instance ID],
bp_worker_resume.due_date AS [Step due date]
FROM dbo.cf_bp_main_instances AS instance LEFT OUTER JOIN
dbo.cf_user_snapshot AS start_user_snapshot ON start_user_snapshot.id = instance.user_snapshot_id LEFT OUTER JOIN
dbo.cf_users AS start_user ON start_user.user_id = start_user_snapshot.user_id LEFT OUTER JOIN
dbo.cf_bp_worker_instances AS bp_worker ON bp_worker.bp_instance_id = instance.bp_instance_id LEFT OUTER JOIN
dbo.cf_bp_steps AS bp_steps ON bp_steps.step_id = bp_worker.current_step_id AND bp_steps.process_id = bp_worker.current_process_id AND (bp_steps.step_type = 'catchEvent' OR
bp_steps.step_type = 'serviceTask') AND bp_steps.is_deleted = 0 LEFT OUTER JOIN
dbo.cf_bp_worker_instnc_to_resume AS bp_worker_resume ON bp_worker_resume.worker_instance_id = bp_worker.instance_id LEFT OUTER JOIN
dbo.cf_bp_worker_instance_history AS bp_worker_history ON bp_worker_history.instance_id = bp_worker.instance_id AND (bp_worker_history.status = 'assigned' OR
bp_worker_history.status = 'reassigned') AND bp_worker_resume.resume_id = bp_worker_history.assigned_resume_id AND bp_worker_resume.owner_snapshot_id IS NULL LEFT OUTER JOIN
dbo.cf_user_snapshot AS assigned_user_snapshot ON assigned_user_snapshot.id = bp_worker_resume.owner_snapshot_id LEFT OUTER JOIN
dbo.cf_users AS assigned_user_record ON assigned_user_record.username = assigned_user_snapshot.username LEFT OUTER JOIN
dbo.cf_user_snapshot AS assigned_user_team_snapshot ON assigned_user_team_snapshot.id = bp_worker_history.target_snapshot_id LEFT OUTER JOIN
dbo.teams AS teams ON (teams.id = bp_worker_resume.team_id OR
bp_worker_resume.team_id IS NULL AND teams.id = bp_worker_history.team_id) AND bp_worker_resume.owner_snapshot_id IS NULL LEFT OUTER JOIN
dbo.team_members AS team_members ON team_members.team_id = teams.id AND team_members.member_rights = 3 AND team_members.leave_date IS NULL LEFT OUTER JOIN
dbo.cf_users AS team_users ON team_users.user_id = team_members.user_group_id
WHERE (instance.status = 1) AND (bp_worker.status <> 6) AND (bp_worker_resume.status = 5 OR
bp_worker_resume.status = 1 OR
bp_worker_resume.status = 2) AND (bp_worker_resume.assign_date IS NOT NULL) AND (bp_steps.step_type IS NULL OR
bp_steps.step_type = 'catchEvent') OR
(instance.status = 1) AND (bp_worker.status <> 6) AND (bp_worker_resume.assign_date IS NOT NULL) AND (bp_steps.step_type IS NULL OR
bp_steps.step_type = 'catchEvent') AND (bp_steps.step_id IS NOT NULL) OR
(instance.status = 1) AND (bp_worker.status <> 6) AND (bp_worker_resume.status = 5 OR
bp_worker_resume.status = 1 OR
bp_worker_resume.status = 2) AND (bp_steps.step_type IS NULL OR
bp_steps.step_type = 'catchEvent') AND (bp_steps.step_id IS NOT NULL) OR
(instance.status = 1) AND (bp_worker.status <> 6) AND (bp_steps.step_type IS NULL OR
bp_steps.step_type = 'catchEvent') AND (bp_steps.step_id IS NOT NULL) AND (bp_steps.step_id IS NOT NULL) OR
(instance.status = 1) AND (bp_worker.status = 3) AND (bp_steps.step_type = 'serviceTask')) AS subquery
GROUP BY [Process Name], [Instance name], [Started by], [Last updated], [Assigned to], [Assigned to (username)], [Assigned to (email)], [Current step], [Step start date], [Start date], [Instance ID], [Step due date]
I'm new to Laserfiche and not familiar with the schema. I have been trying to replicate the monitor status window. Your query works great, and I really appreciate it. You probably already know, but it added "current status" by including the following statement:
case instance.status
when 1 then 'In progress'
when 2 then 'Completed'
when 3 then 'Canceled'
when 4 then 'Terminated by error'
when 5 then 'Terminated by End Event'
else 'Unknown Status'
end as [current status],
I hope that this status is accurate.