Looking for help with a query report
Hello, I need a custom report that lists all my workstations with the status "in store", along with the specific date it was last changed to "in store" and the person who made the change. I wonder if I can get help here? Thanks.
SQL query report
hey guys can some one please change this query to something that service desk report can read and execute ??? SELECT "wo"."WORKORDERID" AS "id", "wof"."UDF_CHAR5" AS "APP_key" FROM "WorkOrder" "wo" LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
SQL Query to select Server and Workstation assets Operating System, OS Service packs installed (i.e. KBxxxxx) and the associated KBxx Installed On date.
Within SDP, I am able to view server and workstation OS, KBs installed and the date the KBs were installed on. See attached picture for reference. Could you please provide me a SQL query that will pull back this information for each asset?
Configuration Management - view/export the list of preventive maintenance tasks with their next scheduled time
This report helps to view/export the list of preventive maintenance tasks with their next scheduled time configured in the application. SELECT swo.WORKORDERID "Scheduled WOID", ti.SCHEDULE_TIME "Next Schedule Time", aau.FIRST_NAME "Requester", sdo.NAME "Site", dpt.DEPTNAME "Department", cd.CATEGORYNAME "Category", scd.NAME "Subcategory", icd.NAME "Item", qd.QUEUENAME "Group", ati.FIRST_NAME "Technician", swo.TITLE "Subject",
Request Management - number of requests submitted per hour
Count of request inflow per hour - provides an insight on monthly opened incidents per hour. It provides you a quick determination on timeframe that received a large number of incidents. This information can then be used by an incident management administrator to determine if a particular department requires additional resources to efficiently manage the number of incidents. PGSQL: SELECT EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) "created time", CASE WHEN EXTRACT(MONTH FROM
Export values from ManageEngine SDP SQL database custom request fields
Guys, anyone know how to export values from "Additional Field - Incident" from SQL DB? Thanks in advance.
Linking Support groups from dbo.ci to dbo.WorkOrder
I can get the created support groups by select * from [dbo].[CI] where CITYPEID = 8 which gives me the columns CIID CITYPEID CINAME DESCRIPTION LABEL CREATEDDATE LASTMODIFIED VERSION LIFECYCLEGROUPIDENTIFIER SITEID How can I link this to the primary workOrder
Custom query report approvers
Hi, Please provide me a query for all users with approver status User id user name(Display Name) User type(User or Technician) department Email ID Service Request Approver (yes/no) Thank you
How to get all approved status of a work order from database via SQL script
How to get all approved status of a work order from database via SQL script
Query to list the pending approvals of a Service Approver
I'v found query to list pending approvals, but it is related to Servicedesk MSP: https://pitstop.manageengine.com/portal/en/kb/articles/query-that-lists-pending-approvals-of-a-particular-service-approver Can anyone share a version for on-premises Servicedesk?
Custom query for audit team with worklogs, notes and attachments details
Hello, I'm need for a query for the audit team that contains the following requirements. I've been able to find much that allows me to export work logs, notes and attachments. All the export features say this is available currently. Any help is appreciated.
Change Management - report to generate details of change request
SELECT chdt.changeid "Change ID", chdt.title "Title", Longtodate(chdt.createdtime) "Created Time", Longtodate(chdt.scheduledstarttime) "Scheduled Start Time", Longtodate(chdt.scheduledendtime) "Scheduled End", Longtodate(chdt.completedtime) "Completed Time", orgaaa.first_name "Requested by", ownaaa.first_name "Technician", priodef1.priorityname "Priority", urgdef.NAME "Urgency", ctdef.NAME
Custom Audit report
Can you give me a query to deliver a report for audit changes specific for hardware removal and change of componant with date/time/user/location
Request Management - service template resource questions
Service templates resource questions. This report helps to find the resource questions and values chosen in the request. SELECT WO_Resources.WOID "RequestID",
CatalogResource.TITLE "Resource Question",
Questions.QUESTION "Question",
ResourcesQAMapping.ANSWER "Answer" FROM WO_Resources
LEFT JOIN ResourcesQAMapping ON WO_Resources.UID=ResourcesQAMapping.MAPPINGID
LEFT JOIN CatalogResource ON wo_resources.RESOURCEID=CatalogResource.UID
LEFT JOIN Questions ON Questions.QUESTIONID=ResourcesQAMapping.QUESTIONID
LEFT
Change Management - report on change approval details
SELECT chdt.changeid AS "Change ID", chdt.title AS "Title", orgaaa.first_name AS "Change Requester", qd.queuename AS "Group", ownaaa.first_name AS "Change Owner", catadef.categoryname AS "Category", priodef1.priorityname AS "Priority", Longtodate(chdt.createdtime) AS "Created
Service Desk/postgres to PBI
I have one question, has anyone solved the connection of onpremise postgres service desk to Microsoft Power BI? I got into the database, but I don't see any useful data in it that I could display, I would like to display dashboards of individual users
Insert rows into COMMENTS table-SQL Server on-premise
Build # 13.0 Build 13002 Does the COMMENTID column in COMMENTS have the identity specification property set to YES? We are looking to move our existing implementation of ServiceDesk to SQL Server on-premise. The ID column properties for identity specifications
Change Management - report on CAB recommendations for change requests
SELECT changetocab.changeid "ChangeID", chdt.title AS "Title", ctdef.NAME AS "Change Type", ownaaa.first_name AS "Change Owner", stageDef.displayname AS "change Stage", statusDef.statusdisplayname AS "Change Status", orgaaa.first_name AS "Change Requester", approvaldef.statusname AS "Approval Status", Longtodate(apprd.action_date) "Approval date",
Custom report for Change Auditing
Here are the fields I need please, if possible I need to be able to adjust the dates against the created time field. Change ID Title Created Time Scheduled Start Time Scheduled End Completed Time Change Owner Category Subcategory Group Change Manager
SQL Cookbook for ServiceDesk Plus - Chapter 1 - SQL basics
SQL Basics: SELECT wo.WORKORDERID AS "Request ID",
wo.TITLE AS "Subject",
aau.FIRST_NAME AS "Requester",
ti.FIRST_NAME AS "Technician",
cd.CATEGORYNAME AS "Category",
std.STATUSNAME AS "Request Status" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SDUser td
Custom Query report -show Job Title
Hi i'm using this custom report and i would like to show the JOB TITLE of the user when i run the report, because i cant see the JOB TITLE in Available Columns so that i can move it to Display Columns.... any one can help please. SELECT resUser.FIRST_NAME
Change Management - history of status changes made on a change request
SELECT chdt.changeid "Change ID", chdt.title "Title", orgaaa.first_name "Requested by", ownaaa.first_name "Technician", Longtodate(chdt.createdtime)
Change script to only include tasks from this month
The below script shows time spent by technicians on projects. I want to modify it to only show data from this (Current) month. What do I add in the script to do this? SELECT pd.projectid "Project ID", pd.title "Title", pd.projectcode "Project Code", aa.first_name
Query Report Error - LONGTODATE
Hello, I am running this query on Power BI and I am receiving the following error message: ERROR: function longtodate(bigint) does not exist After looking in the forum, it seems to be that longtodate is a native function for the query editor in the
Request Management - overview of all conversations of technician and requester in a request
This report gives an overview of all conversations of technician and requester in a request. SELECT wo.WORKORDERID "Request ID", (aau.FIRST_NAME) "Requester", (wo.TITLE) "Subject", (ti.FIRST_NAME) "Technician", longtodate(wo.CREATEDTIME) "Created Time", longtodate(c.createdtime)"Conversation CreatedOn", c.title "Conversation title", longtodate(n.notificationdate)"Technician reply CreatedOn", c.first_name "conversation by", c.description "conversation Description", n.notificationtitle "NOtification
Looking for help with a query report
I would like to do a custom query report that lists all assets that have at least one of 5 different pieces of software installed, but I don't know how to write a query that does that. Would be looking to get the asset names and the names of which software is installed on each returned in a table. Are there any examples anyone can point to that would cover this exactly or close enough that I could modify to suit my needs? Thanks
Service Catalogy
Hello, Can i delete Service Catalogy ? Or deactivate it (not to be active when i'm creating new ticket). Thank you
User Management - Technician change from history
This report is to find who changed the technician on a request and how many technicians handled the request. To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275 SELECT wo.WORKORDERID "Request ID",
wo.TITLE "Subject",
aau.FIRST_NAME "Requester",
ti.FIRST_NAME "Technician",
std.STATUSNAME "Request Status",
pd.PRIORITYNAME "Priority",
longtodate(wo.CREATEDTIME)
Configuration Management - to get the list of users enabled with service request approval permission / list of sites present in the application
This report returns all the users enabled with service request approval permission. SELECT aaauser.first_name "Approver Name", dpt.deptname "Department", sdo.name "Site", sd.jobtitle "Job Title" FROM srapproverdetails LEFT JOIN aaauser ON aaauser.user_id = srapproverdetails.approver LEFT JOIN aaacontactinfo ON aaauser.user_id = aaacontactinfo.contactinfo_id LEFT JOIN sduser sd ON aaauser.user_id=sd.userid LEFT JOIN userdepartment ud ON aaauser.user_id=ud.userid LEFT JOIN DepartmentDefinition
Asset Management - report to get the list of workstations that do not have agent installed on them
SELECT MAX(workstation.WORKSTATIONNAME) "Workstation Name",
MAX(net.IPADDRESS) "IP Address",
MAX(aaov.NAME) "Site",
MAX(aaaUser.FIRST_NAME) "User",
MAX(workstation.LOGGEDUSER) "Last Logged In User",
LONGTODATE(MAX(LASTSUCCESSAUDIT.AUDITTIME)) "Last Success scan" FROM SystemInfo workstation
LEFT JOIN NetworkInfo net ON workstation.WORKSTATIONID=net.WORKSTATIONID
LEFT JOIN Resources RESOURCE ON workstation.WORKSTATIONID=resource.RESOURCEID
LEFT JOIN ResourceOwner
Asset Management - report to get the list of inventoried workstations/servers along with their details
SELECT Max(workstation.workstationname) "Workstation", Max(workstation.model) "Model", Max(workstation.servicetag) "Service Tag", Max(state.displaystate) "Asset State", Max(osinfo.osname) "OS", Max(aaauser.first_name) "User", Max(deptDef.deptname) "Department", Max(aaov.NAME) "Site" FROM systeminfo workstation LEFT JOIN resources resource ON
Asset Management - report to fetch the list of detected workstations/servers along with their details
SELECT Max(workstation.workstationname) "Workstation", Max(workstation.model) "Model", Max(workstation.servicetag) "Service Tag", Max(state.displaystate) "Asset State", Max(osinfo.osname) "OS", Max(aaauser.first_name) "User", Max(deptDef.deptname) "Department", Max(aaov.NAME) "Site" FROM systeminfo workstation LEFT JOIN resources resource ON workstation.workstationid = resource.resourceid
Asset Management - report to find the last scanned time of assets and their scan status
SELECT resource.resourcename "Asset Name", Max(net.ipaddress) "IP Address", Longtodate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date", Longtodate(Max(audithistory.audittime)) "Last Scan Date", Max(audithistory.auditstatus) "Audit Status", Max(aaauser.first_name) "User" FROM resources resource LEFT JOIN resourceowner rOwner ON resource.resourceid
Asset Management - report to find the user accounts of workstations
SELECT workstation.workstationname "Workstation", ua.username "User Name", ua.domainname "Domain Name", ua.description "Description", ua.status "Status", state.displaystate "Asset State" FROM usersaccountinfo ua LEFT JOIN systeminfo workstation ON ua.workstationid = workstation.workstationid LEFT JOIN resources resource ON workstation.workstationid = resource.resourceid
Change Management - count of change requests based on template
SELECT ct.NAME "Template", Count(chdt.changeid) "count" FROM changedetails chdt LEFT JOIN changetemplate ct ON chdt.templateid=ct.templateid WHERE chdt.createdtime >= <from_lastmonth> AND chdt.createdtime <= <to_lastmonth> GROUP BY ct.NAME ORDER BY 1 Click this link to navigate to the next report.
Change Management - report on change roles associated to users on a given change
PGSQL: SELECT chdt.changeid "Change ID", chdt.title "Title", orgaaa.first_name "Change Requester", ownaaa.first_name "Change Owner", cmDef.first_name "Change Manager", stageDef.displayname
Change Management - report on change stages history
SELECT chdt.changeid "Change ID", chdt.title "Title", orgaaa.first_name "Requested by", ownaaa.first_name "Technician", Longtodate(chdt.createdtime) "Created Time", Longtodate(chdt.scheduledstarttime) "Scheduled Start Time", Longtodate(chdt.scheduledendtime) "Scheduled End", Longtodate(chdt.completedtime) "Completed Time", stagedef.NAME
Change Management - changes that have assets involved
SELECT chdt.changeid AS "Change ID", chdt.title AS "Title", orgaaa.first_name AS "Change Requester", ownaaa.first_name AS "Change Owner", catadef.categoryname AS "Category", subcatadef.NAME AS "Subcategory", itemdef1.NAME AS "Item", res.ciname "Asset" FROM changedetails chdt LEFT JOIN sduser orgsd ON chdt.initiatorid = orgsd.userid LEFT JOIN aaauser orgaaa ON orgsd.userid = orgaaa.user_id
Change Management - changes that were caused by incidents
SELECT chdt.changeid "Change ID", chdt.title "Change Title", Longtodate(chdt.createdtime) "Change Created Time", Longtodate(chdt.completedtime) "Change Completed Time", orgaaa.first_name "Change Requested by", ownaaa.first_name "Change Technician", stagedef.NAME "Change Status", wo.workorderid "Request ID", aau.first_name
Change Management - incidents caused by change
SELECT chdt.changeid "Change ID", chdt.title "Change Title", Longtodate(chdt.createdtime) "Change Created Time", Longtodate(chdt.completedtime) "Change Completed Time", orgaaa.first_name "Change Requested by", ownaaa.first_name "Change Technician", stagedef.NAME "Change Status", wo.workorderid "Request ID", aau.first_name
Next Page