Import Problem Module data into Analytics Plus

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


      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Change module data : Import into Analytics Plus

                      Change module data : Import into Analytics Plus Note: When Analytics Plus officially supports Change module integration Out of the box, then these imported tables and reports might get overwritten. Please ensure to name the table as 'Change_localDB'. ...

                    • Importing Projects Module data into Analytics Plus

                      We are yet to sync 'Projects' module's data into Analytics Plus. Meanwhile, you can use custom queries to import those data from ServcieDesk Plus. Step 1: Login into Analytics Plus and open ServiceDesk Plus database. Step 2: Click 'Import Data' and ...

                    • Steps to import Archived Request into Analytics Plus

                      By default, we don't import archived request details into Analytics Plus but we are working on a model to import these details out of the box and will be available in one of our future builds. Meanwhile, please follow the below text instructions Step ...

                    • How to import Survey data from ServiceDesk Plus to Analytics Plus?

                      Helpdesk surveys help you identify how happy your customers really are with your services. A recent research revealed that 96% of unhappy customers don't complain but, 91% of those unhappy customers leave and never come back. That is sad, isn't it?   ...

                    • Data import via localdb fails with timeout error

                      Issue: SQL query timeouts after upgrade to version 4000. Analysis:  The timeout error pops up before generating the data preview. Log traces: [14:34:01:353]|[12-04-2018]|[SYSERR]|[INFO]|[128]:  ...

                      Related Products