Custom Query Report

The Custom Query Report feature lets you create personalized reports by inputting specific queries, offering real-time results tailored to your needs. It allows users to design, configure, and save queries, providing flexibility in analyzing selected data. This feature enhances control over the information presented, enabling users to generate reports that are closely aligned with their specific analysis requirements.

Creating a new Custom Query report

1. Click on the Settings tab.
2. Under Reporting select Custom Query Report.
3. Click on Create New Report.
4. Enter a unique Report Name for your report.
5. Provide a brief description of the report’s purpose in the Description field.
6. In the Query field, specify the SQL query you want to use to generate the report. You can either:
   • Enter a static SQL query directly (or)
   • Use placeholders for dynamic execution to create flexible queries by setting parameters
   How Placeholders work:
   Placeholders are replaced while the query is being executed while generating the report. Applications Manager currently supports 2 types of placeholders:

   • Timestamp Placeholders:
     Use these placeholders to represent time as a Unix timestamp in milliseconds (ms), which is the number of milliseconds that have passed since January 1, 1970 (UTC).
     Format:
     Today - <from_today> - <to_today>
     Yesterday - <from_yesterday> - <to_yesterday>
     This week - <from_thisweek> - <to_thisweek>
     Last 7 days - <start_of_last_7_days> - <end_of_last_7_days>
     Last week - <from_lastweek> - <to_lastweek>
     This month - <from_thismonth> - <to_thismonth>
     Last 30 days - <start_of_last_30_days> - <end_of_last_30_days>
     Last month - <from_lastmonth> - <to_lastmonth>
     This quarter - <from_thisquarter> - <to_thisquarter>
     Last quarter - <from_lastquarter> - <to_lastquarter>
     This year - <start_of_this_year> - <current_time>
     Last 1 year - <from_last1year> - <to_last1year>
   • Sub Query Placeholders:
     Format: {{$placeholdername}}
     These placeholders can be used to dynamically pass values to the main query. Insert the placeholders at the required positions and configure the sub-queries that will return the expected dynamic values. During report generation, the sub-queries associated with the placeholders will be executed first to construct the main query. The main query will then be executed to generate the final report.
7. Click on the Process button to execute the query.

On completing the steps, Applications Manager will process the query and generate the report based on the specified query and placeholders.

Sample Custom Query Report

The following example demonstrates how to create a custom query report to calculate the average CPU utilization for all Windows monitors for today:

Report Name:
Windows Monitor - CPU Utilization - Today Average

Description:
This report calculates the average CPU utilization for all Windows monitors throughout the day, providing insights into system performance.

Query:

SELECT AM_ManagedObject.DISPLAYNAME AS "MONITOR NAME",
    AM_ManagedObject.TYPE AS "MONITOR TYPE",
    AM_ATTRIBUTES.DISPLAYNAME AS "ATTRIBUTE",
    ROUND(CAST(SUM(TOTAL) * 1.0 / SUM(TOTALCOUNT) AS DECIMAL), 3) AS TODAY_AVERAGE
FROM AM_ManagedObject
INNER JOIN AM_ATTRIBUTES
    ON AM_ManagedObject.TYPE = AM_ATTRIBUTES.RESOURCETYPE
    AND AM_ATTRIBUTES.ATTRIBUTEID IN (40705)
LEFT JOIN {{$placeHolder}}
    ON {{$placeHolder}}.RESID = AM_ManagedObject.RESOURCEID
    AND {{$placeHolder}}.ATTRIBUTEID = AM_ATTRIBUTES.ATTRIBUTEID
    AND {{$placeHolder}}.ARCHIVEDTIME > <from_today>
    AND {{$placeHolder}}.ARCHIVEDTIME < <to_today>
GROUP BY AM_ManagedObject.DISPLAYNAME, AM_ManagedObject.TYPE, AM_ATTRIBUTES.DISPLAYNAME;

{{$placeHolder}}:

SELECT ARCHIVEDDATA_TABLENAME
FROM AM_ATTRIBUTES_EXT
WHERE ATTRIBUTEID = 40705

Output:

Scheduling a Custom Query Report

To configure and schedule a Custom Query Report in Applications Manager, follow these steps:

1. Click on the Settings tab.
2. Under Reporting select Schedule Report.
3. Enter a Schedule Name and a Description for the report.
4. Set the Status of the report to Enable/Disable as required.
5. In the Schedule Reports Details section, select Custom Query Report from the Report Type dropdown menu.
6. Select the desired Recurrence Details to specify the timeframe of the report.
7. Choose the configured custom query report you want to schedule from the Available Custom Query Report list.
8. Use the > button to move the selected report to the Selected Custom Query Report list on the right.
9. Configure the Time Settings to choose the frequency at which the report should be generated; i.e: Hourly/Daily/Weekly/Monthly.
10. Choose to Send Report as an attachment or a link the mail (Default: Attachment).
11. Select the format of the report in the Report Type field (PDF/Excel).
12. In the Select Email Actions dropdown, choose any predefined email actions that should be associated with the report when it is sent.
13. Click Save to finalize the scheduled report.

Once all configurations are complete, the report will be automatically generated and delivered based on your configured settings.