daily statistics ( KPI ) - cron job?
Hello
For performance statistic purposes (KPI), I would like to run some SQL statements in one (or more) queries in order to build me a table with history data like
What about my backlog statistic? How could I generate a history statistic on the backlog per day?
Should I do a daily SQL (see backlog sql below), run it via SQL cron job and gather the data in a dedicated table?
Any (better) idea?
Thanks a lot for any help
best regards, Marc
backlog:
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (((std.STATUSNAME != 'Closed') OR (std.STATUSNAME IS NULL)) AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1
created incident:
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE ((rtdef.NAME = 'Incident') AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1
Service Request
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE ((rtdef.NAME = 'Service Request') AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1
Request For Information
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE ((rtdef.NAME = 'Request For Information') AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1
Order Request
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE ((rtdef.NAME = 'Order Request') AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1
For performance statistic purposes (KPI), I would like to run some SQL statements in one (or more) queries in order to build me a table with history data like
- backlog per end of the day
- number of incidents created on one day
- number of service requests on one day
- ...
What about my backlog statistic? How could I generate a history statistic on the backlog per day?
Should I do a daily SQL (see backlog sql below), run it via SQL cron job and gather the data in a dedicated table?
Any (better) idea?
Thanks a lot for any help
best regards, Marc
backlog:
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (((std.STATUSNAME != 'Closed') OR (std.STATUSNAME IS NULL)) AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1
created incident:
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE ((rtdef.NAME = 'Incident') AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1
Service Request
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE ((rtdef.NAME = 'Service Request') AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1
Request For Information
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE ((rtdef.NAME = 'Request For Information') AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1
Order Request
SELECT count(*) FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE ((rtdef.NAME = 'Order Request') AND (((wo.CREATEDTIME >= <from_today>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= <to_today>) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND wo.ISPARENT=1