Query Reports
query to find the unapproved contacts -> PGSQL
Below is the query to find the unapproved contacts -> PGSQL-> can be used from version 11 SELECT aaauser.User_id AS "User ID", aaauser.first_name "First name", aaauser.last_name "Last name", hd.id "Portal ID", hd.displayname "Portal Name", ...
Query to get the Request Approval details
Compatible builds : 14000 to 14200 DB : MSSQL /PGSQL OUTPUT: SELECT wo.WORKORDERID AS "Request ID", wo.title AS "Subject", wo.REQUESTERID AS "Requester ID", LONGTODATE(wo.CREATEDTIME) AS "Requested Date", LONGTODATE(apdet.sent_time) AS "Action ...
Query to display Accounts and Sub Accounts (8.1)
Execute the below query from the Reports Tab -> New Query (Applicable for 8.1 version) SELECT acc.NAME AS "Account",sorg.NAME AS "Sub Account" FROM Customer cust LEFT JOIN AaaOrganization acc ON cust.CUSTOMER_ID=acc.ORG_ID LEFT JOIN SubAccount ...
Query to list out the notes added to the tickets
QUERY: To list of the notes added to the tickets while generating report report SELECT ti.FIRST_NAME AS "Support Rep", wo.WORKORDERID AS "Request ID", longtodate(wo.CREATEDTIME) AS "Created Time", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Contact", ...
Query report for Time Spent for First response in Version 11.0 (Postgres)
SELECT slad.SLANAME AS "Request SLA", wo.WORKORDERID AS "Request ID", longtodate(wo.CREATEDTIME) AS "Created Time", slad.SLANAME AS "Request SLA", longtodate(wo.FR_DUETIME) AS "Request first response due time",round(wo.RESPONSE_TIMESPENT / 60000) AS ...
Query to find the list of Notifications high in number (outofoffice and microsoft emails) with a limit of 10 requests
MSSQL: Select top 10 WorkorderId, (Select Count(*) from Notify_WorkOrder where Notify_WorkOrder.WorkorderId = WorkOrder.WorkorderId) AS NotificationCount from Workorder Order by (Select Count(*) from Notify_WorkOrder where ...
Query to show list of contacts that are assigned to an account but not have a login
The below query shows the list of contacts that are assigned to an account, but does not have a login. SELECT org.NAME AS "Account Name" ,aaauser.FIRST_NAME AS " Contact with no login" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ...
Issue running reports
Cause : Report API running the background causes the issue. If the user mentions issue with running reports with the below error and trace as mentioned below. Error trace: ...
Query to display task details and associated account (start date, due date, createdby, assigned to, etc.,)
Use the below query to show all task information (columns listed : taskid, title, Linked account, start date, due date, status, Description, Assigned to, Created by and Created time) select td.taskid, ad.title, org.name as "Linked Account", ...
Query to export conversations in SCP 8.1 (incoming and outgoing emails)
select notification.notificationid, notify_workorder.workorderid, notification.notificationtitle, notificationtodescription.notificationdescription from notification left join notificationtodescription on notification.notificationid = ...
Support rep last login time, start time, end time along with personal information
select aas.session_id "SessionID", aal.name "User Name",longtodate(aaauser.createdtime) as "User Created Time", aaacontactinfo.emailid "Email", aaacontactinfo.landline "Phone",aaacontactinfo.mobile "Mobile", aas.user_host "User Host", ...
Email id already exists issue
It wont allow you to create a contact, says 'email already exists'.But wont be listed in contact list view,support list view,Acc manager list view select * from aaacontactinfo where emailid='email@email.com'; This will give you the contactinfo_id , ...
Query to find Contact roles
All requests from their account and sub account SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser ...
To find the details about number of users logged in to the application and other related informations
In order to find the details about the list of users logging time to host, their logout time and current status of their connections. Please use the below query, select aas.session_id "SessionID", aal.name "User Name", aas.user_host "User Host", ...
Steps for deleting/modifying the reports created by other techs:
Steps for deleting/modifying the reports created by other techs: Connect to your database To connect to your database please refer http://www.manageengine.com/products/support-center/faq-general.html Execute the below query which will give you the ...
Query to find contact associated in Sub-accounts
SELECT org.NAME "Account Name",aaauser.FIRST_NAME "Contact Name",ucinfo.EMAILID "Contact Email", au1.first_name "Subaccount Contact's name", aci.emailid "Subaccount's contact's email address" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo ...
Duplicate users while importing from AD
Use the below queries; To find the number of duplicate users: SELECT objectguid, COUNT(*) TotalCount FROM aduser GROUP BY objectguid HAVING COUNT(*) > 1; SELECT userid, samaccname, objectguid, COUNT(*) TotalCount FROM aduser GROUP BY userid, ...
Query to find the last logged time of a contact in the portal
select aas.session_id "SessionID", aal.name "User Name",aau.FIRST_NAME "Contact Name",org.NAME "Account Name", parentorg.NAME "Parent Account Name",suborg.name "Sub Account", aas.user_host "User Host", aas.application_host "Application Host", ...
Last logged in time of SupportReps
select max(au.first_name)'Technician',al.name"Login Name", longtodate(max(acs.opentime))"Last Logged in Time" from aaaaccsession acs left join aaaaccount act on act.account_id=acs.account_id left join aaalogin al on al.login_id=act.login_id left join ...
List of contacts logged into the system
Mysql select aas.session_id "SessionID", aal.name "User Name",aau.FIRST_NAME "Contact Name",org.NAME "Account Name", aas.user_host "User Host", aas.application_host "Application Host", longtodate(aas.opentime) "Start Time", longtodate(aas.closetime) ...
Followers report
You can use the below query to get the Followers and the request details. Go to Reports --> New Query Report select wo.workorderid "Request Id",wo.title "Subject",aaau.first_name "Followers",aaauc.emailid "Followers email" from workorder_cclist woc ...
Delete requests
If you would like to delete the older requests, you could use the below query. Connect to the Database For MSSQL: delete from workorder where dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00') <= ...
Changing the ownership of Report
In order to change the ownership of the existing Reports, please follow the below steps, 1. connect to your database. Incase, if your connecting database is postgresql, please refer the below link to connect to it. viz. ...
Query for Support Rep list in DB
Please run this query to find the list of Support Reps available with login in the system select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on ...