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?
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 ...Query Report to find the Contract details in SCP 11.0
SELECT ad.ORG_NAME AS "Account", ac.CONTRACTNAME AS "Contract Name", ac.CONTRACTNO AS "Contract Number", ( case when sp.PLANTYPE like 'Charge Per Hour' then concat(cast(((bc.consumedunits/(1000))/(60*60)) as varchar), ':' ...query to find the unapproved contacts -> PGSQL
Below is the query to find the unapproved contacts -> PGSQL-> can be used from version 11 SELECT aaauser.User_id AS "User ID", aaauser.first_name "First name", aaauser.last_name "Last name", hd.id "Portal ID", hd.displayname "Portal Name", ...