Chat now with support
Chat with Support

KACE Systems Management Appliance 9.1 Common Documents - Administrator Guide

About the KACE Systems Management Appliance (SMA) Getting started
Configuring the appliance
Requirements and specifications Power-on the appliance and log in to the Administrator Console Access the Command Line Console Tracking configuration changes Configuring System-level and Admin-level General Settings Configure appliance date and time settings Enable Two-Factor Authentication for all users Verifying port settings, NTP service, and website access Configuring network and security settings Configuring Agent settings Configuring session timeout and auto-refresh settings Configuring locale settings Configuring the default theme Configure data sharing preferences About DIACAP compliance requirements Configuring Mobile Device Access Enable fast switching for organizations and linked appliances Linking Quest KACE appliances Configuring history settings
Setting up and using labels to manage groups of items Configuring user accounts, LDAP authentication, and SSO Using Replication Shares Managing credentials Configuring assets
About the Asset Management component Using the Asset Management Dashboard About managing assets Adding and customizing Asset Types and maintaining asset information Managing Software assets Managing physical and logical assets Maintaining and using manual asset information Managing locations Managing contracts Managing licenses Managing purchase records
Setting up License Compliance Managing License Compliance Setting up Service Desk Configure the Cache Lifetime for Service Desk widgets Creating and managing organizations Importing and exporting appliance resources
Managing inventory
Using the Inventory Dashboard Using Device Discovery Managing device inventory
About managing devices Features available for each device management method About inventory information Tracking changes to inventory settings Managing inventory information Finding and managing devices Provisioning the KACE SMA Agent Manually deploying the KACE SMA Agent Using Agentless management Adding devices manually in the Administrator Console or by using the API Forcing inventory updates Managing MIA devices Obtaining Dell warranty information
Managing applications on the Software page Managing Software Catalog inventory
About the Software Catalog Viewing Software Catalog information Adding applications to the Software Catalog Managing License assets for Software Catalog applications Associate Managed Installations with Cataloged Software Using software metering Using Application Control Update or reinstall the Software Catalog
Managing process, startup program, and service inventory Writing custom inventory rules
Deploying packages to managed devices
Distributing software and using Wake-on-LAN Broadcasting alerts to managed devices Running scripts on managed devices Managing Mac profiles Using Task Chains
Patching devices and maintaining security
About patch management Subscribing to and downloading patches Creating and managing patch schedules Managing patch inventory Managing Dell devices and updates Maintaining device and appliance security
Using reports and scheduling notifications Monitoring servers
Getting started with server monitoring Working with monitoring profiles Managing monitoring for devices Working with alerts
Using the Service Desk
Configuring Service Desk Using the Service Desk Dashboard Managing Service Desk tickets, processes, and reports
Overview of Service Desk ticket lifecycle Creating tickets from the Administrator Console and User Console Creating and managing tickets by email Viewing tickets and managing comments, work, and attachments Merging tickets Using the ticket escalation process Using Service Desk processes Using Ticket Rules Run Service Desk reports Archiving, restoring, and deleting tickets Managing ticket deletion
Managing Service Desk ticket queues About User Downloads and Knowledge Base articles Customizing Service Desk ticket settings Configuring SMTP email servers
Maintenance and troubleshooting
Maintaining the appliance Troubleshooting the KACE SMA
Appendixes Glossary About us Legal notices

Create reports using SQL queries

Create reports using SQL queries

You can create reports by entering SQL queries on the report form.

If you do not know the SQL queries to use, consider using the report wizard. See Create reports using the report wizard.

1.
Go to the Reports list by doing one of the following:

Log in to the KACE SMA System Administration Console, http://KACE_SMA_hostname/system, or select System from the drop-down list in the top-right corner of the page. Then click Reporting.

System-level reports include consolidated reports that aggregate information from all organizations, as well as standard reports for various KACE SMA components.

If your KACE SMA does not have the Organization component enabled, or if you want to access an organization-level report, log in to the KACE SMA Administrator Console, http://KACE_SMA_hostname/admin. Or, if the Show organization menu in admin header option is enabled in the appliance General Settings, select an organization in the drop-down list in the top-right corner of the page next to the login information. Then click Reporting.

Organization-level reports include standard reports for various KACE SMA components. If the Organization component is enabled on your appliance, these reports provide information specific to the selected organization.

The Reports list appears.
2.
Select Choose Action > New (SQL) to display the Report Detail page.

Option

Description

Title

The display name of the report, which appears on the report list. Make the title as descriptive as possible, so you can distinguish the report from others in the list.

Description

A description of the report.

Category

The category of the report. If the category does not already exist, it is added to the drop-down list on the Reports page.

Break on Columns

A comma-separated list of SQL column names. The report generates break headers and subtotals for these columns.

Show Line Numbers

Display a column with line numbers on the report.

SQL

The query statement that generates the report data. For more information, go to the MySQL documentation at http://dev.mysql.com/doc/refman/5.0/en/.

When writing a report or query against the Service Desk HD_Ticket table, be aware that the User custom field stores the user ID from the USER table in the HD_TICKET table, which is the table that holds the ticket record. If you want to display the username instead of the user ID in the report, you need to JOIN on the USER table.

See Database table names.

Organization settings

These settings are available only at the System level on appliances with the Organization component enabled. Options include:

All Organizations: The SQL Select statement is modified to iterate across all organizations, and the report contains information for all organizations.
Aggregate results: The SQL Select statement is modified to combine the records of all organizations, and the report contains summary information for all organizations. Standard reports of this type are categorized as Consolidated Reports.
4.
Click Save.
TIP: Charts and graphs cannot be created from within the KACE SMA reporting tool. To create charts or graphs, generate a report in XLS (Microsoft Excel) or CSV (comma-separated value) format, then import the data into a tool that has chart or graph capabilities, such as Microsoft Excel.

Create reports from list pages

Create reports from list pages

You can create reports while viewing list pages, such as the Devices page.

1.
Go to a list page. For example, to go to the Devices page, do the following:
2.
Select Choose Action > Create Report to display the Report Detail page.

Option

Description

Title

The display name of the report, which appears on the report list. Make the title as descriptive as possible, so you can distinguish the report from others in the list.

Description

A description of the report.

Category

The category of the report. If the category does not already exist, it is added to the drop-down list on the Reports page.

Break on Columns

A comma-separated list of SQL column names. The report generates break headers and subtotals for these columns.

Show Line Numbers

Display a column with line numbers on the report.

SQL

The query statement that generates the report data. For more information, go to the MySQL documentation at http://dev.mysql.com/doc/refman/5.0/en/.

When writing a report or query against the Service Desk HD_Ticket table, be aware that the User custom field stores the user ID from the USER table in the HD_TICKET table, which is the table that holds the ticket record. If you want to display the username instead of the user ID in the report, you need to JOIN on the USER table.

4.
Click Save.

The report appears on the Reports page.

Duplicate reports

Duplicate reports

You can duplicate any report, including standard reports that are shipped with the appliance. If you are creating a report that is similar to an existing report, duplicating the existing report can be faster than creating a report from scratch.

1.
Go to the Reports list by doing one of the following:

Log in to the KACE SMA System Administration Console, http://KACE_SMA_hostname/system, or select System from the drop-down list in the top-right corner of the page. Then click Reporting.

System-level reports include consolidated reports that aggregate information from all organizations, as well as standard reports for various KACE SMA components.

If your KACE SMA does not have the Organization component enabled, or if you want to access an organization-level report, log in to the KACE SMA Administrator Console, http://KACE_SMA_hostname/admin. Or, if the Show organization menu in admin header option is enabled in the appliance General Settings, select an organization in the drop-down list in the top-right corner of the page next to the login information. Then click Reporting.

Organization-level reports include standard reports for various KACE SMA components. If the Organization component is enabled on your appliance, these reports provide information specific to the selected organization.

The Reports list appears.
Depending on the type of report, either the Report Detail page or the first page in the report wizard appears.
Depending on the type of report, either the Report Detail page or the first page in the report wizard appears.

Edit SQL statements on reports created with the report wizard

Edit SQL statements on reports created with the report wizard

You can edit the SQL statements on single-topic reports created with the report wizard.

This editing is useful when you want to change the SQL statement, or when you want to copy the SQL statement to a new report. The edit option is not available on multi-topic reports.

1.
Go to the Reports list by doing one of the following:

Log in to the KACE SMA System Administration Console, http://KACE_SMA_hostname/system, or select System from the drop-down list in the top-right corner of the page. Then click Reporting.

System-level reports include consolidated reports that aggregate information from all organizations, as well as standard reports for various KACE SMA components.

If your KACE SMA does not have the Organization component enabled, or if you want to access an organization-level report, log in to the KACE SMA Administrator Console, http://KACE_SMA_hostname/admin. Or, if the Show organization menu in admin header option is enabled in the appliance General Settings, select an organization in the drop-down list in the top-right corner of the page next to the login information. Then click Reporting.

Organization-level reports include standard reports for various KACE SMA components. If the Organization component is enabled on your appliance, these reports provide information specific to the selected organization.

The Reports list appears.
3.
At the bottom of the form, click Edit SQL to display the Report Detail page.
4.
Edit or copy text in the SQL field as needed, then click Save.
Related Documents