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 Notify_WorkOrder.WorkorderId = WorkOrder.WorkorderId) DESC
PGSQL:
Select WorkorderId, (Select Count(*) from Notify_WorkOrder where Notify_WorkOrder.WorkorderId = WorkOrder.WorkorderId) AS NotificationCount from Workorder Order by (Select Count(*) from Notify_WorkOrder where Notify_WorkOrder.WorkorderId = WorkOrder.WorkorderId) DESC limit 10
New to ADSelfService Plus?
Resources
Related Articles
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 ...
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') <= ...
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 ...
Script to Notify Support Reps when a new reply is received for Unassigned Requests
Please do follow the below steps to notify support reps when an unassigned request is updated or when a reply is received. 1. Download the configuration.json file, sendMail.py file, requestobj.json file from the below link: ...
Phone number need to be mandatory in 8.1 for contacts
Kindly follow the steps below to create the FAFR, 1. Access admin Module 2. Select the request template option and click the edit option the required request template the script needs to be applied on 3. Select the Fields and Forms Rule tab and paste ...