Report Modules - Frequently asked questions
How to convert the long value to date format in ServiceDesk Plus?
Use "from_unixtime([columnname]/1000)" in the mysql prompt to get the date in the correct format.
For getting the long value from date, see the following example:
mysql> select unix_timestamp('2004-1-31 20:10:10') * 1000;
unix_timestamp will give you the time in seconds since Jan 1 1970. Multiply by 1000 to get the time in milliseconds.
DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000) ,'%d-%m-%Y %k:%i')'Created Time',
Compare date in MYSQL:
where wo.createdtime >= (UNIX_TIMESTAMP(DATE('2009-01-01 00:00:00')) * 1000) and wo.createdtime <= (UNIX_TIMESTAMP(DATE('2009-12-31 23:59:59')) * 1000)
Also you can use,
where (from_unixtime(wo.createdtime/1000) between '2011-09-20 00:00:00' and '2011-09-20 23:59:59')
As an example, see the query below,
Display the date column in SQL Server
select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00') 'Created Date', WORKORDERID 'Request ID' from WorkOrder
select DATEADD(s,wo.CREATEDTIME/1000,'01-01-1970')'Created time' from workorder wo
Compare date column in SQL server:
select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00') 'Created Time' from workorder
where dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2011-01-01 00:00',21)
and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2011-12-31 23:59',21)
Where can I find the Frequently Asked Query Reports in the application?
Go to the Reports tab -> Frequently Asked Reports. [ Screenshot ] Or, go to Reports tab -> New Query Report -> Frequently Asked Reports.
We have posted all the query reports requested by our customers in our forums. You could find the links to these from within the product.
Click Reports -> Query Reports -> Under Search Query reports drop down you will find the frequently asked query reports categorized by modules.
[ Screenshot ]
Is the database schema of ServiceDesk Plus available?
The database schema is accessible from within the ServiceDesk Plus application.
Go to Reports -> New Query report -> Select the module you want to report on, from the Table Schema drop down list and press the Get button which will give you the Schema.
[ Screenshot ]
One of our technicians has left the company and the reports schedule by him is still being triggered. How do I go about deleting this report? Also, I would want to check the other reports scheduled by the technician.
When a technician is deleted or his login removed from ServiceDesk Plus, then the ownership of his ‘Scheduled Reports’ would be moved to the technician who performed the delete technician/remove login operation.
Was your question answered here?
Check the other FAQ modules or you can contact our support team at firstname.lastname@example.org