I trust everybody has enjoyed the summer :0)
This issue with scheduled reports came up with a client the other day so I thought I’d share. A customer was trying to find a scheduled report that had been previously set up in ManageEngine ServiceDesk Plus. They knew it existed as it was still sending reports but they were unable to locate the actual scheduled report.
The reason for this was due to the fact that scheduled reports are currently only visible to the technician that creates them in ManageEngine ServiceDesk Plus. In this case the scheduled reports the client was looking for had been created under a different technician account.
To resolve the issue we needed to find all the scheduled reports that had been created in ManageEngine ServiceDesk Plus and the technician accounts they had been created under.
The following custom query (paste into Reports > New Query Report), which should work with PostgreSQL or MS SQL databases, should help us to do this:
select rst.reportid”Report ID”,crd.report_name”Report Name”,au.first_name”Technician Name”,rst.ownerid”Technician ID”,sd.status”Active/Resigned” from reportscheduletask rst left join customreport_details crd on crd.reportid=rst.reportid left join aaauser au on au.user_id=rst.ownerid left join sduser sd on sd.userid=au.user_id;
In my test system this gave the following output and, as we can see, scheduled reports have been created by two different technicians:
Note that the ‘Active/Resigned’ status shows if the Technician account has been deleted or not. If they have the status would read as ‘Resigned’ *1.
If I now login with the account ‘nigel.arnold’ and view the scheduled reports I only see two reports, not the entire list:
If you need to change the scheduled report so that it is visible to another technician account you will have to change the technician id to assign it to another technician. In this case I could change the scheduled reports that are currently assigned to ‘nigel.arnold’ with technician id ‘303’ to the ‘administrator’ with technician id ‘4’.
To do this we would need to run the following query via a direct connection to the relevant back-end database *2:
update reportscheduletask set ownerid=required_technician’s_userid where ownerid=current_technician’s_userid;
Or in this example on my test system:
update reportscheduletask set ownerid=4 where ownerid=303;
*1. If you have a deleted Technician it is usually a good idea to find if there are any other reports assigned to them. To do this run the following query:
select * from customreport_details where ownerid=User-Id of deleted Tech;
Then to assign those reports to another technician run the following query *2:
update customreport_details set ownerid=NewTechnician’sid where ownerid=User-Id of deleted Tech;
You can run the following query to find your current technicians and relevant id’s if needed:
select au.user_id,au.first_name,s.status from helpdeskcrew hc left join aaauser au on au.User_id=hc.technicianid left join sduser s on s.userid=au.user_id;
*2. Make sure you have an up to date backup before you run any update queries on a production system !
To connect to PostgreSQL database from an administrative command prompt
Go to [ServiceDesk Plus – Home] pgsqlbin in the command prompt.
C:> cd ManageEngineServiceDeskPluspgsqlbin
Enter the command: psql.exe -U postgres -h 127.0.0.1 -p 65432 servicedesk
C:> cd ManageEngineServiceDeskPluspgsqlbin> psql.exe -U postgres -h 127.0.0.1 -p 65432 servicedesk
For MS SQL use your query browser in MS SQL Server Management Studio