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", longtodate(td.Starttime) as "Start Date", longtodate(td.Duebytime) as "Due Date", astat.Statusname as "Status", ad.description as "Description", aa.First_name as "Assigned to", aal.Name as "Created by", longtodate(ad.Createdtime) from taskdefinition td left join activitydefinition ad on td.Taskid=ad.Activityid left join customer_activity cact on ad.Activityid=cact.Activityid left join aaaorganization org on cact.Customer_id=org.Org_id left join activitystatus astat on td.Statusid=astat.statusid left join aaauser aa on td.Ownerid=aa.User_id left join aaalogin aal on ad.Createdby=aal.User_id
Query to list users details with account and subaccount
How to run the report Copy the below query, go to Reports >> New Query Report >> Paste the query and click on run report. DB MSSQL and Postgres Query SELECT org.NAME AS "Account Name", adef.org_name "Sub Account", sduser.FIRSTNAME AS "First Name", ...
Apply Contract to All Requests raised by this account option is greyed out while creating a new contract
1. Whenever I put in Expiry date the checkbox for Apply Contract to All Requests raised by this account becomes greyed out. --> This will happen if there is another active contract for the same period. Two rules are applied while creating a contract ...
Login Frequency Query Report
The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP ...
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 ...
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 ...