Add GROUP BY to query report
I have made this report to show us which users are eligible for mobile phone upgrades: SELECT aau.FIRST_NAME "Requester", DATE_FORMAT(from_unixtime(reqf.UDF_DATE1/1000), '%m/%d/%Y') "Mobile Phone Upgrade Date" FROM AaaUser aau LEFT JOIN Requester_Fields reqf ON aau.USER_ID=reqf.USERID WHERE (reqf.UDF_DATE1/1000 < unix_timestamp(now()) ) I'd like to create two separate reports, one grouped by Site, and the other grouped by Region--how would I do this? We're using 8016 with MySQL. Thanks!
Reports Time Spent
I have a report that will show me how much time a tech enters against a request ( see query below) How can I enhance this query to also show how much time is spent on a change request. SELECT rc.DATETIME "Cost entry date",wo.WORKORDERID "Request ID",wo.TITLE "Subject",aau.FIRST_NAME "Requester",std.STATUSNAME "Request Status",rc.MM2COMPLETEREQUEST "Time Elapsed" FROM WorkOrder wo LEFT JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN RCTechnicianIDs rct ON rc.REQUESTCHARGEID=rct.REQUESTCHARGEID
Need help with a Query.
Would someone be kind enough to help me with a query to give a report of percentages of violated SLA's vs met SLA's. I need it grouped by technician and by group please. We are running MySQL still.
Run a report and allow a user to see it.
Users have the tabs Home, Service Catalog, Results, Solutions, My Details. I as a technician I have many more tabs, one of which is Reports. When a (any) customer needs a report run he can not execute it. I would like to write a custom report that anyone can run when they need it. A scheduled report sent to a specific person or group on a schedule will not fit the bill. One day Joe may run it, the next Susan, etc. Can I create a service in Service Catalog or Results that would allow then to run it
Component inventory
If in december the Mouse has been assigned to User1 & in feb it assigned to User2 Can i get the report where this mouse is attached at current time & No. of users used it???
Survey Reports - Multiple "Ratings" for single Incident
Hello, We are currently experiencing multiple "Ratings" for single request incidents (when producing Survey Reports) and therefore we produce incorrect reporting results. Is this a known issue or am I just creating a custom report incorrectly? Here is the QUERY. Thanks for your assistance. ============================================================== SELECT sadt.ANSVALUES "Rating",spre.CREATEDTIME "Survey Created Time",sadt.ANSVALUES "Rating",wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",aau.FIRST_NAME
Help for query report in Servicedesk Plus 8.0
Hi, I need to be able to draw a report showing all installed software titles in PC's sorted by WorkstationID - as well as info on "last logged user". Format like this: Workstation ID - Software titles - Last logged user. How would this query be, when using mysql? /Erling
SSO slow in Version 8016
Upgraded from version 7513 to version 8016. Followed the upgrade path. Went through the steps to create the SSO connection, had to run the vbscript on the DC to get a computer account created. When users logon with SSO it take around 20 seconds for the page to load. Anyone else experiencing this issue?
Report combining Service Requests and Changes
I am looking for a report that will combine the Service Request with a Change Request that was spawned from it. I need to be able to show the following fields: Service Request Number Category Subcategory Requestor Subject Descritpion Techinican Change Request Number Change Type I will need to be able to filter on Category and Change Type. Would you be able to help?
Report - Request with Resolved Status but no resolved timestamp
Hi We have an issue where when a request is raised and resolved at the same time the resolved timestamp does not get set. This means that the request will never close as we set to autoclose after 7 days of being resolved. Could you provide me with a report in MSSQL that will show requests that have the status of resolved but no resolved timestamp. Thanks Mike
Custom Report - Approved Incidents
Hello, I am currently running a query to generate a report for the approved incidents by the approver. I need some help changing it so it does not show the incidents where the approver is "Not Assigned". I also need it to only pull the approved incidents from the previous month so the report can run on the first of every month to provide proper status. Here is the query i'm using now: SELECT wo.WORKORDERID 'Request ID',aau.FIRST_NAME 'Requester',rtdef.NAME 'Request Type',wo.TITLE 'Subject',ti.FIRST_NAME
Printer Report
Hi all, Is there a way I can have a report of all printers and maybe include name and possible for details? Thanks Martin
SLA Report with Restablishment time
I have a new challenge for the Report Gurus: I need a SLA report that calculates something I called "Restablishment Time" ('Executed Time'+'Time spent at first WorkLog entry') and to sum at the end a technician-entry called "Unavailability Time" that will be used for availability calculation (% in month). The report should be something like this: Request ID | Priority | Created Time | Executed Time | Restablishment Time | Resolved Time | Unavailability Time (in min) | Time taken to respond (hh:mm)
Custom scheduled report needed
Hi, We recently upgraded from ServiceDesk+ version 6 to version 8.0.0 Build 8009, running on MSSQL. A scheduled report we used to run now no longer works properly as formatting has changed. Would you be able to supply the SQL for the following, please? Report The purpose of this report is to show if we have met one of our KPIs - the time taken between a new request being created, and the request being assigned to a technician. For all requests created in the previous quarter, we need: RequestID;
Business Rules
I have encountered an issue today where business rules have disappeared for me in the admin section. The tab is still there but it shows no business rules. But if I select organize business rules they do show up there. I noticed after the fact that this was not just limited to the business rules tab. But also effected Requesters,Technicians and any other tab that had similar created items within it. Was able to see them but only after deleting my account and re-adding.
Report Help - Custom Report - Request ID field
Hello, I'm creating a custom report and need the option of "Request ID" in the Group ID field: Here’s the settings I have: Advanced Matrix Report - Column Grouping o Columns: Executed Time o Date Formate: Day - Group By o Technician o Group o Request ID - Summarize Column By o Sum o Time Elapsed - Date Filter o Executed Time During Last week - Advanced Filtering o Technician is <List of selected Tech Names> What I need is the next Group By option of Request ID that’s highlighted above. I I’d like
Assets
We have been using ServiceDesk Plus for about a month now and this problem has happened twice. While working on the assets and getting them all assigned to the correct departments all of a sudden they all get changed to the same department. The only thing I can think of that I did just before this happened was that I removed an unused site.
Servlet API not pulling out category..
Hi, I have my own form I am making in SDP with the Servlet API SDP provides. All of my form fields work properly and get created into the ticket EXCEPT the Service Category whose parameter in Servlet API is "category" I cannot figure out why the automated process is not working for category perhaps this is a bug on your end as my code is the same for all fields and seems to work just fine with other fields except the "category" parameter. I have provided an example from my code. When ticket is created
Deleting default reports
Hi, is there a way to delete default reports (and not only the ones I created). ServiceDesk Plus v8. Best reagrds.
Please supply report queries which work in MSSQL!
https://forums.manageengine.com/topic/report-on-requests-which-were-changed-to-resolved-state-last-week http://forums.manageengine.com/topic/query-to-get-the-timespent-on-the-requests-which-are-open#49000005169121 http://forums.manageengine.com/topic/report-on-surveys-for-overall-rating-in-percentage#49000005169141 Lists the query for MySQL and MSSQL - it does not work in MSSQL There are more and more of these appearing on your forums - why are the topics being posted as locked? - I am unable to
Query to view the templates in ServiceDesk based on Service Category
Query: select rtl.templatename'Template based on Service Category', sdef.name from requesttemplate_list rtl left join servicedefinition sdef on rtl.parent_service=sdef.serviceid where rtl.is_catalog_template=1 order by 2 Result: Prem Maheswaran SDP Support - FAQ ServiceDesk Plus - Help Desk Software of your Choice
Asset Report with Cost
Using SQL and ManageEngine version 8.0 build 8015, I was hoping to generate an asset report that would include the following: Department User Asset Description Asset Cost Serial Number Purchase Date Resource Tag Please let me know if you can help. Thank you.
Printers marker life count report
Dear Support Team, We have SDP 8.0.0 Build 8013 with MSSQL. To the IT internal control, we need create a report with Marker Life Count of all printers in the network (total pages count). Add Resource Name, Product Name, Printer Serial Number and State. Scans the network works perfectly, the page counter of the printers was found successfully. Thanking you in anticipation. Daniel Fabro.
Column Feature in Reporting
I am trying to run a monthly report from service desk. I want the top colume to be weeks (say,week 1,week 2...) for a particular month and the left to be request category.However, from the drop down list of options,it only contains created time,responded time, etc..... and does not contain weeks as I want in my reporting format. What could I do to include weeks on the top column. Thanks LSmith Papua New Guinea
Service Requests Matrix Report
I tried to create a matrix report specific to Service Requests, with the aim of grouping the requests by service category and request template. This due to the fact that the service request template is not one of the options you can select while creating the custom report. I can create a report showing how many Application Log-in service requests were received but can't have any further breakdown. I waited for a few patches and this issue is till there. Very annoying.
Query Error (KPI report :based on group and technician)
This query doesn't work in build 8013 for MSSQL (https://forums.manageengine.com/#Topic/49000005072090) SELECT au.FIRST_NAME 'Technician', qd2.QUEUENAME "Group", (SELECT COUNT(wo4.WORKORDERID) FROM Workorder_Threaded wot4 INNER JOIN workorder wo4 ON wo4.WORKORDERID=wot4.WORKORDERID LEFT JOIN WorkOrderStates wos4 ON wos4.WORKORDERID=wo4.WORKORDERID LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE (wot4.THD_WOID=wot4.WORKORDERID)
Query Error (Re: Resolved time Report)
Hi, https://forums.manageengine.com/topic/resolved-time-report The query above for MSSQL produces the following error in build 8013 - MSSQL: java.sql.SQLException: Operand type clash: ntext is incompatible with tinyint at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
Software list - SQL - help needed
Hello Is there a way to create queries graphically, using a third party app eventually? Would somebody help me please on an SQL (mysql) for a List with installed Software I am stuck at this point (see below) but I also need workstation state (in use, disposed, ...) , software type (managed, freeware, ...) as well as site, department and user and ordered by software SELECT SystemInfo.WORKSTATIONNAME "Workstation",WmiSWUsageInfo.SOFTWAREUSAGE "Software_Usage",SWInstallationKeyInfo.SOFTWAREINFOID "Product_ID",SWInstallationKeyInfo.PRODUCTID
as I can get a report with the time and date update report for the first time
I need to get a report each month of the time and date the updated technical report for the first time when I change the status to resolved
reporting date and time resolved
need to get a monthly report where it comes from the time and date when the report is resolved
Can we use a different delimiter field for parsing incoming email
We have the system setup with Admin > Mail Server Settings > Incoming set to monitor a particular mailbox. It is reading that mailbox successfully, and creating a new Request for each new email. A notification email is sent back to the submitter and in the Subject of that email we include the following (everything in between the quotes): "Request ID :##$RequestId##] ". If a reply is sent to that email notification (e.g. for Request ID 353), and the Subject contains "Request ID: ##353##", it adds
Requester notification on Request Status Change
Is there a way to notify the requester only when its request status is changed from "open" to any other? I know that there is the "Acknowledge requester by e-mail when the request is updated", but I want to limit this notifications. Is this possible? -Regards
Automatically define Critical Level of Request depending upon the Requester Job Title
We have pre-defined criteria regarding the critical level of any request that would be logged for assets. The definition of the Critical level depends upon the Job Title of the Requester with whom the asset is attached and is used by him. Requests logged for assets attached to users with Job Title above a particular level in the Organisation would always have Critical Level I, whereas Requests logged for assets attached to Requesters below that level would always have Critical Level II. If an asset
SDP Query
SELECT wo.WORKORDERID "Request ID",icd.NAME "Item", dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') "Created Time" ,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.DUEBYTIME/1000),'1970-01-01 00:00:00') "DueBy Time", datediff(d,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.DUEBYTIME/1000),'1970-01-01 00:00:00'),GETDATE())"Aging(Days)", ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status" FROM WorkOrder wo LEFT JOIN SiteDefinition siteDef
Tickets created and closed timeline report
Hello, I have a problem with a report that management has asked for. They want to see week by week, the number of tickets opened, and the number of tickets closed, in a line chart format. I created the line chart report, but the numbers are inaccurate. Here is the issue that I need help with... For quite a few of our tickets, they are opened and closed on the same day. When I group by request status, ServiceDesk just goes through the current status of the records and reports those counts. This
Date format in query reports
Hello, When i try to get some data by a new query report (in SDP), i have some problem with the dates format: If I use the sdp built-in function "longtodate(wo.createdtime)", the date is returned in a useless format for me (i am spanish). If I use the function "from_unixtime(wo.createdtime)" the thing gets better (YYYY-MM-DD HH:MM:SS) but i am interested in a specific date format: "DD/MM/YYYY HH:MM:SS". I don't know the reason when i use the function "convert()" or "date_format()", i get some senseless
Scheduled exported report
Hi, Is there a way to have a report run on a regular schedule but have it saved to a directory as opposed to haivng it emailed?
Query Error (Tasks details with closed time)
This query doesn't work in build 8013 for MSSQL https://forums.manageengine.com/#Topic/49000004962255 Error: java.sql.SQLException: Operand data type ntext is invalid for max operator. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628) at net.sourceforge.jtds.jdbc.MSCursorResultSet.processOutput(MSCursorResultSet.java:933)
Need a Query Report to List down the service request approvers
Hi Bharat, The Link does not show me any query
Request Trend Report
Hi: In need of a report that shows Request trends by month (Jan,Feb, Mar,April,.... That is, for each month we would like to see: Number of Tickets Opened Number of Tickets Close Number of Tickets OhholdIf possible can a Line Chart be included (see attached)? Thanks
Next Page