Query report to get requestid,username ,email with created date ( MSSQL )
Tested in build MSSQL (14306)
SELECT wo.WORKORDERID AS "Request ID", aaau.FIRST_NAME AS "Requester", aaaci.emailid,wo."TITLE" AS "Subject", "cd"."CATEGORYNAME" AS "Category",
longtodate(wo.CREATEDTIME) AS "created time" FROM "WorkOrder" "wo" Left join AaaUser aaau ON wo.REQUESTERID=aaau.USER_ID LEFT JOIN aaausercontactinfo aaauci ON aaau.user_id=aaauci.user_id LEFT JOIN aaacontactinfo aaaci ON aaaci.contactinfo_id=aaauci.contactinfo_id LEFT JOIN sduser sdu ON sdu.userid=aaau.user_id LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID and wo.CREATEDTIME >= <from_lastmonth> AND wo.CREATEDTIME <= <to_lastmonth> ORDER BY 1
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
Other format - (Date selection criteria is added)
SELECT wo.WORKORDERID AS "Request ID", aaau.FIRST_NAME AS "Requester", aaaci.emailid,wo."TITLE" AS "Subject", "cd"."CATEGORYNAME" AS "Category",
longtodate(wo.CREATEDTIME) AS "created time" FROM "WorkOrder" "wo" Left join AaaUser aaau ON wo.REQUESTERID=aaau.USER_ID LEFT JOIN aaausercontactinfo aaauci ON aaau.user_id=aaauci.user_id LEFT JOIN aaacontactinfo aaaci ON aaaci.contactinfo_id=aaauci.contactinfo_id LEFT JOIN sduser sdu ON sdu.userid=aaau.user_id LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID where wo.CREATEDTIME >= datetolong('2021-04-20') AND wo.CREATEDTIME <= datetolong('2021-04-23')
PS : provide the needed dates in the bold area
New to ADSelfService Plus?
Related Articles
Report to get list of Requesters with Requester created time and created by (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Kindly execute the report under Report -> New Query Report. SELECT AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", longtodate(aaauser.createdtime) "Created time", AaaContactInfo.EMAILID ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Query to show technician created time (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT AaaUser.FIRST_NAME "Technician Name", aci.emailid "Email ID", LONGTODATE(AaaUser.createdtime) "Technician Created Time", SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser left ...Query to retrieve both the live and archived requests details
Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...