General
Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...
Query to get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...
Query to show users of Organization Roles for each account (MSSQL & PGSQL)
T ested in Build PGSQL (14300) or MSSQL (14306) select ad.org_name "Account Name", org.orgrolename "ORG Role Name", aau.first_name "User" from orgroles org left join roletousermapping rtu ON org.orgroleid=rtu.roleid LEFT JOIN roletouseraccountmapping ...
Query to show Problems, its associated incidents and change_ (MSSQL)
Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...
Query to show Account Specific Category, Sub category and Item (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT accountdefinition.org_name "Account Name", CategoryDefinition.CATEGORYNAME "Category Name", SubCategoryDefinition.NAME "Sub Category Name" ,ItemDefinition.NAME"Item Name" FROM CategoryDefinition ...
Query to find users with multiple E-mail addresses of the same name (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) select aaau.user_id,aaau.first_name,aaaci.emailid,sdu.status,sdo.name "Site",adef.org_name "Account" from aaauser aaau LEFT JOIN aaausercontactinfo aaauci ON aaauci.user_id=aaau.user_id LEFT JOIN ...
Query to show permissions for roles created. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute the query. select AaaRole.NAME "Role name", ar.name "Permission" from AaaRole inner join AaaImpliedRole on AaaRole.ROLE_ID=AaaImpliedRole.ROLE_ID left join ...
Query to show hours and days since last update (MSSQL)
Tested in Build MSSQL (14306) Go to Reports-New Query Report and execute this query. select wos.WORKORDERID "Request ID", max(accountdefinition.org_name) "Account",MAX(aau.FIRST_NAME) "Requester",MAX(wo.TITLE) "Subject", MAX(sd.statusname) "Ticket ...
Query to find duplicates list of user with various criteria (PGSQL)
Tested in Build PGSQL (14300) Listed queries for the below cases Query to list requesters and technicians with email address filter. Query to list requesters and technicians with login name filter Query to list support groups with email address ...