Query to find out who created Accounts
Execute the below queries under Reports->New Query Report.
select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan')
Note down the Org_IDs of the above query from the result and replace the ID in the below query.
SELECT err.message "System log message", err.errormodule "Module", err.suberrormodule "Sub Module", err.action "Action", err.type "Type", case when au.first_name is null then 'System' Else au.first_name end "Performed by", longtodate(err.occurredtime) "Time of occurrence" FROM errorlog err LEFT JOIN aaauser au ON err.ownerid=au.user_id WHERE
err.action like '%Add%' and err.errormodule like '%Account%' AND err.message like '%3301%'
NOTE: Replace the IDs one by one and execute the above query separately to know who created these accounts from the Performed By field.
Query to find status change, its time and comments.
Execute this query under Reports->New Query Report. Date filter is highlighted, you can modify it as per the need. PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request Status", wo.TITLE "Subject", ...
Query to show Created, Pending and Completed Requests
PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created Last Week", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Pending from Last Week", count(case when wo.completedtime >= <from_lastweek> AND wo.completedtime <= <to_lastweek> THEN ...
Query to show technicians associated accounts, sites and Support groups - MSSQL
1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job Title", sdo.name "Associated ...
Query to show tickets created based on shift time_PGSQL
Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created Time", extract(hour from ...
How to find an account ID
In order to find the account id, under Reports Tab, click New Query Report run the below query: select adef.org_id "Account ID",adef.org_name "account",sdef.siteid "Siteid",sdo.name "Site" from sitedefinition sdef left join accountsitemapping asm on ...