Community and Support
            Knowledge Base Analytics Plus Integrations SDP

            Problem Module Data import

            Importing Problem data into Analytics Plus: 
            You could log into the Analytics Plus 'ME ServiceDesk Plus Analytics' database and start creating these two tables using the below queries. Check this link for steps to import data from Local DB.
            Problem data: ( for PGSQL)

            SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title", to_timestamp(prob.REPORTEDTIME/1000) AS "Reported Date", to_timestamp(prob.UPDATEDTIME/1000) AS "Updated Date", to_timestamp(prob.DUEBYTIME/1000) AS "DueBy Date", to_timestamp(prob.CLOSEDTIME/1000) AS "Closed Date", orgaaa.FIRST_NAME AS "Reported by", ownaaa.FIRST_NAME AS "Technician", priodef.PRIORITYNAME AS "Priority", urgdef.NAME AS "Urgency", statdef.STATUSNAME AS "Status", catadef.CATEGORYNAME AS "Category", subcatadef.NAME AS "Subcategory", itemdef.NAME AS "Item", impactdef.NAME AS "Impact", probdesc.FULL_DESCRIPTION AS "Description", solwork.DESCRIPTION AS "Workaround", solres.DESCRIPTION AS "Solution", sdo.NAME AS "Site", orgsd.ISVIPUSER AS "VIP User" FROM Problem prob LEFT JOIN SDUser orgsd ON prob.ORIGINATORID=orgsd.USERID LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID LEFT JOIN SDUser ownsd ON prob.OWNERID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN StatusDefinition statdef ON prob.STATUSID=statdef.STATUSID LEFT JOIN PriorityDefinition priodef ON prob.PRIORITYID=priodef.PRIORITYID LEFT JOIN UrgencyDefinition urgdef ON prob.URGENCYID=urgdef.URGENCYID LEFT JOIN CategoryDefinition catadef ON prob.CATEGORYID=catadef.CATEGORYID LEFT JOIN SubCategoryDefinition subcatadef ON prob.SUBCATEGORYID=subcatadef.SUBCATEGORYID LEFT JOIN ItemDefinition itemdef ON prob.ITEMID=itemdef.ITEMID LEFT JOIN ProblemResolution probResol ON prob.PROBLEMID=probResol.PROBLEMID LEFT JOIN ImpactDefinition impactdef ON probResol.IMPACTID=impactdef.IMPACTID LEFT JOIN ProblemToDescription probdesc ON prob.PROBLEMID=probdesc.PROBLEMID LEFT JOIN SolutionToWorkAround probwork ON prob.PROBLEMID=probwork.PROBLEMID LEFT JOIN Solution solwork ON probwork.SOLUTIONID=solwork.SOLUTIONID LEFT JOIN SolutionToResolution probres ON prob.PROBLEMID=probres.PROBLEMID LEFT JOIN Solution solres ON probres.SOLUTIONID=solres.SOLUTIONID LEFT JOIN SiteDefinition siteDef ON prob.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID 

            Problem data: (for MSSQL)

            SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title", DATEADD(MILLISECOND, prob.REPORTEDTIME % 1000, DATEADD(SECOND, prob.REPORTEDTIME / 1000, '19700101')) AS "Reported Date",  DATEADD(MILLISECOND, prob.UPDATEDTIME % 1000, DATEADD(SECOND, prob.UPDATEDTIME / 1000, '19700101')) AS "Updated Date", DATEADD(MILLISECOND, prob.DUEBYTIME % 1000, DATEADD(SECOND, prob.DUEBYTIME / 1000, '19700101')) AS "DueBy Date", DATEADD(MILLISECOND, prob.CLOSEDTIME % 1000, DATEADD(SECOND, prob.CLOSEDTIME / 1000, '19700101')) AS "Closed Date", orgaaa.FIRST_NAME AS "Reported by", ownaaa.FIRST_NAME AS "Technician", priodef.PRIORITYNAME AS "Priority", urgdef.NAME AS "Urgency", statdef.STATUSNAME AS "Status", catadef.CATEGORYNAME AS "Category", subcatadef.NAME AS "Subcategory", itemdef.NAME AS "Item", impactdef.NAME AS "Impact", probdesc.FULL_DESCRIPTION AS "Description", solwork.DESCRIPTION AS "Workaround", solres.DESCRIPTION AS "Solution", sdo.NAME AS "Site", orgsd.ISVIPUSER AS "VIP User" FROM Problem prob LEFT JOIN SDUser orgsd ON prob.ORIGINATORID=orgsd.USERID LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID LEFT JOIN SDUser ownsd ON prob.OWNERID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN StatusDefinition statdef ON prob.STATUSID=statdef.STATUSID LEFT JOIN PriorityDefinition priodef ON prob.PRIORITYID=priodef.PRIORITYID LEFT JOIN UrgencyDefinition urgdef ON prob.URGENCYID=urgdef.URGENCYID LEFT JOIN CategoryDefinition catadef ON prob.CATEGORYID=catadef.CATEGORYID LEFT JOIN SubCategoryDefinition subcatadef ON prob.SUBCATEGORYID=subcatadef.SUBCATEGORYID LEFT JOIN ItemDefinition itemdef ON prob.ITEMID=itemdef.ITEMID LEFT JOIN ProblemResolution probResol ON prob.PROBLEMID=probResol.PROBLEMID LEFT JOIN ImpactDefinition impactdef ON probResol.IMPACTID=impactdef.IMPACTID LEFT JOIN ProblemToDescription probdesc ON prob.PROBLEMID=probdesc.PROBLEMID LEFT JOIN SolutionToWorkAround probwork ON prob.PROBLEMID=probwork.PROBLEMID LEFT JOIN Solution solwork ON probwork.SOLUTIONID=solwork.SOLUTIONID LEFT JOIN SolutionToResolution probres ON prob.PROBLEMID=probres.PROBLEMID LEFT JOIN Solution solres ON probres.SOLUTIONID=solres.SOLUTIONID LEFT JOIN SiteDefinition siteDef ON prob.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID

            Problem Incident mapping (for PGSQL)

            SELECT ProblemToIncidentMapping.PROBLEMID AS "Problem ID", ProblemToIncidentMapping.WORKORDERID AS "RequestID", to_timestamp(WorkOrder.CREATEDTIME/1000) AS "Request Created Time" FROM ProblemToIncidentMapping LEFT JOIN WorkOrder ON ProblemToIncidentMapping.WORKORDERID=WorkOrder.WORKORDERID where WorkOrder.WORKORDERID is not null

            Problem Incident mapping (for MSSQL)
            SELECT ProblemToIncidentMapping.PROBLEMID AS "Problem ID", ProblemToIncidentMapping.WORKORDERID AS "RequestID", DATEADD(MILLISECOND, WorkOrder.CREATEDTIME % 1000, DATEADD(SECOND, WorkOrder.CREATEDTIME / 1000, '19700101')) AS "Request Created Time" FROM ProblemToIncidentMapping LEFT JOIN WorkOrder ON ProblemToIncidentMapping.WORKORDERID=WorkOrder.WORKORDERID where WorkOrder.WORKORDERID is not null
            Once these two tables are created, You could Edit the 
            Problem Incident mapping table, Join them using the Lookup column under 'Problem Incident mapping' table.
             
            Once Joined, you should be able to create reports using all the tables like Problems, Requests, Problem Incident mapping etc.

            I Have created few of your listed requirements in my local environment and have attached the PDF of the dashboard.

            Other Queries to Map Assets affected and Services affected:
            Problem to Service Mapping (for PGSQL and MSSQL)
            SELECT ProblemToService.PROBLEMID AS "Problem ID", ProblemToService.SERVICEID AS "Service ID", Problem.REPORTEDTIME AS "Problem Created Time" FROM ProblemToService LEFT JOIN Problem ON ProblemToService.PROBLEMID=Problem.PROBLEMID where Problem.PROBLEMID is not null

            Problem To Asset Mapping(for PGSQL and MSSQL)
            SELECT ProblemToAsset.PROBLEMID AS "Problem ID", ProblemToAsset.ASSETID AS "ASSET ID", Problem.REPORTEDTIME AS "Problem Created Time" FROM ProblemToAsset LEFT JOIN Problem ON ProblemToAsset.PROBLEMID=Problem.PROBLEMID where Problem.PROBLEMID is not null

            Problem Worklog:
            SELECT Problem.PROBLEMID AS "Problem ID",
            ChargesTable.CHARGEID AS "ChargeID",
            ChargesTable.DESCRIPTION AS "Description",
            ChargesTable.TIMESPENT AS "Time Spent",
            ChargesTable.TECH_CHARGE AS "Tech Charge",
            ChargesTable.OTHER_CHARGE AS "Other Charge",
            ChargesTable.TOTAL_CHARGE AS "Total Charge",
            DATEADD(MILLISECOND, ChargesTable.CREATEDTIME % 1000, DATEADD(SECOND, ChargesTable.CREATEDTIME / 1000, '19700101')) AS "Created Time",
            DATEADD(MILLISECOND, ChargesTable.TS_STARTTIME % 1000, DATEADD(SECOND, ChargesTable.TS_STARTTIME / 1000, '19700101')) AS "Starttime",
            DATEADD(MILLISECOND, ChargesTable.TS_ENDTIME % 1000, DATEADD(SECOND, ChargesTable.TS_ENDTIME / 1000, '19700101')) AS "Endtime",
            CreatedBy.FIRST_NAME AS "Created By",
            Technician.FIRST_NAME AS "Technician",
            TaskToCharge.TASKID AS "TaskID"
            FROM Problem INNER JOIN ProblemToCharge on Problem.PROBLEMID = ProblemToCharge.PROBLEMID
            LEFT JOIN ChargesTable on ProblemToCharge.CHARGEID = ChargesTable.CHARGEID
            LEFT JOIN AaaUser CreatedBy ON ChargesTable.CREATEDBY=CreatedBy.USER_ID
            LEFT JOIN AaaUser Technician ON ChargesTable.TECHNICIANID=Technician.USER_ID
            LEFT JOIN TaskToCharge ON ChargesTable.CHARGEID=TaskToCharge.CHARGEID
            LEFT JOIN TaskDetails ON TaskToCharge.TASKID = TaskDetails.TASKID
            Problem Tasks:
            SELECT Problem.PROBLEMID AS "Problem ID",
            TaskDetails.TASKID AS "TaskID",
            DATEADD(MILLISECOND, TaskDetails.CREATEDDATE % 1000, DATEADD(SECOND, TaskDetails.CREATEDDATE / 1000, '19700101')) AS "Created Date",
            DATEADD(MILLISECOND, TaskDetails.SCHEDULEDSTARTTIME % 1000, DATEADD(SECOND, TaskDetails.SCHEDULEDSTARTTIME / 1000, '19700101')) AS "Scheduled Start Time",
            DATEADD(MILLISECOND, TaskDetails.SCHEDULEDENDTIME % 1000, DATEADD(SECOND, TaskDetails.SCHEDULEDENDTIME / 1000, '19700101')) AS "Scheduled End Time",
            DATEADD(MILLISECOND, TaskDetails.ACTUALSTARTTIME % 1000, DATEADD(SECOND, TaskDetails.ACTUALSTARTTIME / 1000, '19700101')) AS "Actual Start Time",
            DATEADD(MILLISECOND, TaskDetails.ACTUALENDTIME % 1000, DATEADD(SECOND, TaskDetails.ACTUALENDTIME / 1000, '19700101')) AS "Actual End Time",
            TaskDetails.TITLE AS "Title",
            TaskDetails.MODULE AS "Module",
            TaskDetails.PER_OF_COMPLETION AS "Percentage Of Completion",
            TaskDetails.ADDTIONAL_COST AS "Additional Cost",
            TaskDetails.ISSITEVISIT AS "Site Visit",
            TaskDetails.ISESCALATED AS "Task IsEscalated",
            TaskDetails.ISOVERDUE AS "Overdue Task",
            TaskDetails.ESTIMATEDEFFORTDAYS AS "Estimated Before Days",
            TaskDetails.ESTIMATEDEFFORTHOURS AS "Estimated Before Hours",
            TaskDetails.ESTIMATEDEFFORTMINUTES AS "Estimated Before Minutes",
            TaskDetails.ESTIMATEDEFFORT AS "Estimated Effort",
            SDOrganization.NAME AS "Site",
            CreatedBy.FIRST_NAME AS "Created By",
            AaaTechnician.FIRST_NAME AS "Owner",
            PriorityDefinition.PRIORITYNAME AS "Priority",
            StatusDefinition.STATUSNAME AS "Task Status",
            TaskTypeDefinition.TASKTYPENAME AS "Task Type",
            QueueDefinition.QUEUENAME AS "Group"
            FROM TaskDetails INNER JOIN ProblemToTaskDetails on TaskDetails.TASKID = ProblemToTaskDetails.TASKID
            LEFT JOIN Problem on ProblemToTaskDetails.PROBLEMID = Problem.PROBLEMID
            LEFT JOIN SiteDefinition ON TaskDetails.SITEID=SiteDefinition.SITEID
            LEFT JOIN SDOrganization ON SiteDefinition.SITEID=SDOrganization.ORG_ID
            LEFT JOIN AaaUser CreatedBy ON TaskDetails.CREATEDBY=CreatedBy.USER_ID
            LEFT JOIN SDUser Technician ON TaskDetails.OWNERID=Technician.USERID
            LEFT JOIN AaaUser AaaTechnician ON Technician.USERID=AaaTechnician.USER_ID
            LEFT JOIN PriorityDefinition ON TaskDetails.PRIORITYID=PriorityDefinition.PRIORITYID
            LEFT JOIN StatusDefinition ON TaskDetails.STATUSID=StatusDefinition.STATUSID
            LEFT JOIN TaskTypeDefinition ON TaskDetails.TASKTYPEID=TaskTypeDefinition.TASKTYPEID
            LEFT JOIN QueueDefinition ON TaskDetails.GROUPID=QueueDefinition.QUEUEID

            Attachments (1)
            Updated: 08 Feb 2018 10:38 PM
            Helpful?  
            Help us to make this article better
            0 0