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
How to email pending requests list to technician periodically
This post describes the use of a python script to email pending requests to each technician in a periodic interval using Custom Schedules.This showcases the use of a feature called 'Custom Schedules' that was released in the build 9300 of ServiceDesk ...Query to show number of requests raised per Asset (MSSQL)
Tested in Build MSSQL (14306) SELECT ci.ciname 'Asset Name', cd.COMPONENTNAME 'Product', cty.COMPONENTTYPENAME 'Product Type',COUNT(wo.ciid) 'Total Requests raised for this asset' FROM workorder wo left join CI ON ci.ciid=wo.ciid LEFT JOIN RESOURCES ...Query to retrieve the requests details
Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...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') <= ...