Report Modules - Frequently asked questions

Reports Module

   
  1. How to convert the long value to date format in ServiceDesk Plus?
  2. Where can I find the Frequently Asked Query Reports in the application?
  3. Is the database schema of ServiceDesk Plus available?
  4. 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.
  1. How to convert the long value to date format in ServiceDesk Plus?

    For MYSQL:
    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')

    For MSSQL:
    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)

     
  2. 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 ]

     
  3. 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 ]

     
  4. 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 servicedeskplus-support@manageengine.com

The World's Largest Organizations Rely On ServiceDesk Plus