Custom Query Report

RMM Central provides the following types of reports:

  • Canned reports on various modules like Patch Management, Asset Management, Active Directory, and so on.
  • Wizard-based Custom Reports to retrieve any specific information

In addition to the above report types, it also provides an ability to retrieve the required information from the database using the Query Report. This might be useful in cases where you are not able to get the required information from the Canned or the Custom Reports.

The Query Report can be created using the Query Report button available under Reports tab.. You may have to provide the SQL Query and create the report. The report can be saved for future reference and / or exported to CSV format for further processing.

From where can I get the Query?

Contact rmmcentral-support@manageengine.com with the details of your requirement. 

Our support team will process your requirement and send you the query.

Built-in Date Functions

Date is stored in the Long format in the database. You will not be able to interpret the date on seeing this long format. In order to convert this to readable date format, two built-n functions are included:

LONG_TO_DATE()

This function can be used to convert the date from the long value to the date format. Consider the following example:

You wish to retrieve software details along with the date and time at which the software was detected. The query you would normally use is:

 

Select SOFTWARE_NAME, DETECTED_TIME from invsoftware

 

SOFTWARE_NAME          DETECTED_TIME

Adobe Reader              1234558984892

Skype                        8945934747893

In the above result, you will see the Detected Time in long format, which is not readable. Now, modifying the query as below will give you the desired output

Select SOFTWARE_NAME, LONG_TO_DATE(DETECTED_TIME) from invsoftware

 

SOFTWARE_NAME          DETECTED_TIME_DATE_FORMAT

Adobe Reader                09/12/2009 15:35

Skype                          07/13/2009  13.25

 

DATE_TO_LONG()

This function can be used to convert the Date format to Long value. Consider the example where you wish to retrieve the details of the software detected between two specific dates. You should use the query as below:

 

select * from invsoftware where DETECTED_TIME between DATE_TO_LONG(08/01/2009 00:00:00) and

DATE_TO_LONG(08/31/2009 00:00:00)

The date should be specified in the following format: mm/dd/yyyy hh:mm:ss

Date Templates

For retrieving the data between some predefined  dates, you can make use of the date templates. The following date templates are supported:

  • Today - <from_today> - <to_today>
  • Yesterday - <from_yesterday> - <to_yesterday>
  • This Week - <from_thisweek> - <to_thisweek>
  • Last Week - <from_lastweek> - <to_lastweek>
  • This Month - <from_thismonth> - <to_thismonth>
  • Last Month - <from_lastmonth> - <to_lastmonth>
  • This Quarter - <from_thisquarter> - <to_thisquarter>
  • Last Quarter - <from_lastquarter> - <to_lastquarter>