9.0

Edit

Get data from WorkSpace using SQL queries

The WorkFusion platform stores information about Manual Tasks on the Control Tower side (MySQL) and WorkSpace (WS) side (PostgreSQL) applications.

Below, you can find sample query examples to get information about Manual Tasks on the WS side.

WS databases are virtualizer (WS prod) and virtulizer_sanbox (WS sandbox).

Amount of Manual Tasks available for processing, grouped by the global category, category, and title

select cc.title as first_level_category, c.title as category,ht.title,count(h.id) 
from hit h
    join hit_type ht on ht.id = h.hit_type_id
    join category c on c.id = ht.category_id
    join category cc on cc.id = c.parent_category_id
where h.hit_status ='ASSIGNABLE' 
group by cc.title, c.title,ht.title
order by cc.title, c.title,ht.title;

Output example:

Amount of Manual Tasks available for processing, grouped by the global category, category, title, and age (in hours)

select 
    task.global_category,
    task.category,
    task.title,
    sum(CASE WHEN task.age_in_hours>8 THEN 1 ELSE 0 END) as "> 8 Hours",
    sum(CASE WHEN (task.age_in_hours>=4 and task.age_in_hours<=8)  THEN 1 ELSE 0 END) as "4-8 Hours",
    sum(CASE WHEN (task.age_in_hours>=3 and task.age_in_hours<4)  THEN 1 ELSE 0 END) as "3-4 Hours",
    sum(CASE WHEN (task.age_in_hours>=2 and task.age_in_hours<3)  THEN 1 ELSE 0 END) as "2-3 Hours",
    sum(CASE WHEN (task.age_in_hours>=1 and task.age_in_hours<2)  THEN 1 ELSE 0 END) as "1-2 Hours",
    sum(CASE WHEN (task.age_in_hours<1)  THEN 1 ELSE 0 END) as "< 1 Hour",
    count(task.age_in_hours) as Total
from(
    select cc.title as global_category, c.title as category,ht.title,EXTRACT(epoch FROM (age(current_timestamp, h.creation_time - interval '4 hours')))/3600 as age_in_hours  
    from hit h
        join hit_type ht on ht.id = h.hit_type_id
        join category c on c.id = ht.category_id
        join category cc on cc.id = c.parent_category_id
    where h.hit_status ='ASSIGNABLE') task
group by task.global_category,task.category,task.title
order by task.global_category,task.category,task.title

Output example:

← PreviousNext →