[[PageOutline]]

= Inflow metrics =

All time max count in one day:
{{{
#!SQLTable
SELECT COUNT(*) as count, FROM_UNIXTIME(time, '%Y-%m-%d') AS date
 FROM ticket GROUP BY date ORDER BY count DESC LIMIT 1;
}}}

Max in past 30 days:
{{{
#!SQLTable
SELECT COUNT(*) as count, FROM_UNIXTIME(time, '%Y-%m-%d') AS date
 FROM ticket WHERE time > UNIX_TIMESTAMP() - (86400 * 30)
 GROUP BY date ORDER BY count DESC LIMIT 1;
}}}

Min in past 30 days:
{{{
#!SQLTable
SELECT COUNT(*) as count, FROM_UNIXTIME(time, '%Y-%m-%d') AS date
 FROM ticket WHERE time > UNIX_TIMESTAMP() - (86400 * 30)
 GROUP BY date ORDER BY count ASC LIMIT 1;
}}}

Avg in past 30 days:
{{{
#!SQLTable
SELECT ROUND(COUNT(*)/30) as avg
 FROM ticket WHERE time > UNIX_TIMESTAMP() - (86400 * 30);
}}}

Past 24 hours:
{{{
#!SQLTable
SELECT COUNT(*) as count FROM ticket WHERE time > UNIX_TIMESTAMP() - 86400;
}}}

'''Consider above a warning sign if value > 30 days average or approaching alltime max.'''

= Standing tickets =

Alltime support team-owned tickets by status:
{{{
#!SQLTable
SELECT COUNT(*) as count, owner, status
 FROM ticket
 WHERE owner IN ('hinojosa', 'jwigglesworth', 'ctsai')
 GROUP BY owner, status
 ORDER BY status ASC, owner ASC;
}}}

Past 30 days support team-owned tickets by status:
{{{
#!SQLTable
SELECT COUNT(*) as count, owner, status
 FROM ticket
 WHERE owner IN ('hinojosa', 'jwigglesworth', 'ctsai')
 AND time > UNIX_TIMESTAMP() - (86400 * 30)
 GROUP BY owner, status
 ORDER BY status ASC, owner ASC;
}}}

'''Evaluate per-staff member performance for warning indicators (folks getting bogged down due to process or tool issues?).'''

Total standing (Support) ticket count:
{{{
#!SQLTable
SELECT COUNT(*) AS count, count(*)/(select count(*) AS count from ticket where time > unix_timestamp() -
 (86400*2) AND status != 'closed')*100 AS percentage
 FROM ticket WHERE keywords NOT LIKE '%PEND%' AND
 keywords NOT LIKE '%SOG%' AND keywords NOT LIKE '%ENGR%'
 AND keywords NOT LIKE '%CREATE%' AND keywords NOT LIKE '%CONSUME%'
 AND status != 'closed';
}}}

'''Consider above a warning sign (and trigger triage activity) if this count is a substantial percentage of the total unclosed tickets received in the past two days.'''

= General flow =

Alltime tickets by status:
{{{
#!SQLTable
SELECT COUNT(*) AS count, status
 FROM ticket GROUP BY status ORDER BY count DESC;
}}}

Past 30 days tickets by status:
{{{
#!SQLTable
SELECT COUNT(*) AS count, status
 FROM ticket WHERE time > UNIX_TIMESTAMP() - (86400 * 30)
 GROUP BY status ORDER BY count DESC;
}}}

Past 7 days tickets by status:
{{{
#!SQLTable
SELECT COUNT(*) AS count, status
 FROM ticket WHERE time > UNIX_TIMESTAMP() - (86400 * 7)
 GROUP BY status ORDER BY count DESC;
}}}

'''Consider above a warning sign if status=closed > status!=closed.'''

= Pending tickets =

Count of pending tickets:
{{{
#!SQLTable
SELECT COUNT(*) AS count
 FROM ticket WHERE keywords like '%PEND%' AND status != 'closed';
}}}

= SOG tickets =

Count of open SOG tickets:
{{{
#!SQLTable
SELECT COUNT(*) AS count
 FROM ticket WHERE keywords like '%SOG%' AND status != 'closed';
}}}

Count of SOG tickets in 7 day window ending 7 days ago:
{{{
#!SQLTable
SELECT COUNT(*) FROM ticket 
 WHERE ticket.time <= UNIX_TIMESTAMP() - (86400 * 7)
 AND ticket.time > UNIX_TIMESTAMP() - (86400 * 14)
 AND keywords LIKE '%SOG';
}}}

Count of SOG tickets in past 7 days:
{{{
#!SQLTable
SELECT COUNT(*) FROM ticket 
 WHERE ticket.time > UNIX_TIMESTAMP() - (86400 * 7)
 AND keywords LIKE '%SOG';
}}}

Past 7 days SOG ticket closure activity:
{{{
#!SQLTable
SELECT COUNT(DISTINCT(ticket)) as count, author
 FROM ticket_change, ticket
 WHERE ticket_change.time > UNIX_TIMESTAMP() - (86400 * 7)
 AND ticket.id=ticket_change.ticket
 AND field='status'
 AND oldvalue!='closed'
 AND newvalue='closed'
 AND keywords LIKE '%SOG%'
 GROUP BY author ORDER BY count DESC;
}}}

'''Consider this a warning sign if count is not on decline or well-controlled.'''


= ENGR tickets =

Count of open ENGR tickets:
{{{
#!SQLTable
SELECT COUNT(*) AS count
 FROM ticket WHERE (keywords like '%ENGR%' or keywords like '%CONSUME%' or keywords like '%CREATE%') AND status != 'closed';
}}}

Count of ENGR tickets in 7 day window ending 7 days ago:
{{{
#!SQLTable
SELECT COUNT(*) FROM ticket 
 WHERE ticket.time <= UNIX_TIMESTAMP() - (86400 * 7)
 AND ticket.time > UNIX_TIMESTAMP() - (86400 * 14)
 AND (keywords like '%ENGR%' or keywords like '%CONSUME%' or keywords like '%CREATE%');
}}}

Count of ENGR tickets in past 7 days:
{{{
#!SQLTable
SELECT COUNT(*) FROM ticket 
 WHERE ticket.time > UNIX_TIMESTAMP() - (86400 * 7)
 AND (keywords like '%ENGR%' or keywords like '%CONSUME%' or keywords like '%CREATE%');
}}}

Past 7 days ENGR ticket closure activity:
{{{
#!SQLTable
SELECT COUNT(DISTINCT(ticket)) as count, author
 FROM ticket_change, ticket
 WHERE ticket_change.time > UNIX_TIMESTAMP() - (86400 * 7)
 AND ticket.id=ticket_change.ticket
 AND field='status'
 AND oldvalue!='closed'
 AND newvalue='closed'
 AND (keywords like '%ENGR%' or keywords like '%CONSUME%' or keywords like '%CREATE%')
 GROUP BY author ORDER BY count DESC;
}}}

'''Consider this a warning sign if count is not on decline or well-controlled.'''

= Past 24 hours ticket work =

Total activity (status changes, comments, etc.) by Support on tickets in past 24 hours:
{{{
#!SQLTable
SELECT COUNT(*) AS count, author
 FROM ticket_change WHERE time > UNIX_TIMESTAMP() - 86400
 AND author in ('jwigglesworth', 'ctsai', 'hinojosa')
 GROUP BY author ORDER BY count DESC;
}}}

Comments posted by Support to tickets in past 24 hours:
{{{
#!SQLTable
SELECT COUNT(*) AS count, author
 FROM ticket_change WHERE time > UNIX_TIMESTAMP() - 86400
 AND author in ('jwigglesworth', 'ctsai', 'hinojosa')
 AND field='comment'
 GROUP BY author ORDER BY count DESC;
}}}

24-hour view of triage activity on tickets posted in past 24 hours:
{{{
#!SQLTable
SELECT COUNT(DISTINCT(ticket)) as count, author
 FROM ticket_change, ticket WHERE
 ticket_change.time > UNIX_TIMESTAMP() - 86400
 AND author in ('jwigglesworth', 'ctsai', 'hinojosa')
 AND ticket.id=ticket_change.ticket
 AND ticket.time > UNIX_TIMESTAMP() - 86400
 GROUP BY author ORDER BY count DESC;
}}}

24-hour view of activity on tickets posted more than 24 hours ago:
{{{
#!SQLTable
SELECT COUNT(DISTINCT(ticket)) as count, author
 FROM ticket_change, ticket WHERE
 ticket_change.time > UNIX_TIMESTAMP() - 86400
 AND author in ('jwigglesworth', 'ctsai', 'hinojosa')
 AND ticket.id=ticket_change.ticket
 AND ticket.time <= UNIX_TIMESTAMP() - 86400
 GROUP BY author ORDER BY count DESC;
}}}

'''Evaluate per-staff member performance for warning indicators (folks getting bogged down due to process or tool issues?).'''

= Ticket kills and undead =

Kill rate (closure) for support in past 7 days
{{{
#!SQLTable
SELECT COUNT(DISTINCT(ticket)) as count, author
 FROM ticket_change, ticket
 WHERE ticket_change.time > UNIX_TIMESTAMP() - (86400 * 7)
 AND author in ('jwigglesworth', 'ctsai', 'hinojosa')
 AND ticket.id=ticket_change.ticket
 AND field='status'
 AND oldvalue!='closed'
 AND newvalue='closed'
 GROUP BY author ORDER BY count DESC;
}}}

Revive rate (closed -> open status)
{{{
#!SQLTable
SELECT COUNT(DISTINCT(ticket)) as count, author
 FROM ticket_change, ticket
 WHERE ticket_change.time > UNIX_TIMESTAMP() - (86400 * 7)
 AND author in ('jwigglesworth', 'ctsai', 'hinojosa')
 AND ticket.id=ticket_change.ticket
 AND field='status'
 AND oldvalue='closed'
 AND newvalue!='closed'
 GROUP BY author ORDER BY count DESC;
}}}

Kill rate for everyone in past 7 days:
{{{
#!SQLTable
SELECT COUNT(DISTINCT(ticket)) as count, author
 FROM ticket_change, ticket
 WHERE ticket_change.time > UNIX_TIMESTAMP() - (86400 * 7)
 AND ticket.id=ticket_change.ticket
 AND field='status'
 AND oldvalue!='closed'
 AND newvalue='closed'
 GROUP BY author ORDER BY count DESC;
}}}