Generating an excel report
The Excel directory, in the root location of the server application, contains startpoint templates for the Microsoft Excel report interface, including the file Data Source Name used by the server reporting facility.
Excel template folder
This directory contains startpoint templates for the Excel report interface. You can copy XLS files into this folder and the file names appear on the Report > Add and Report > Edit screens. Additionally, this directory can contain File DSN type references to support any ODBC-compliant database. For more information, see the Support for other databasessection later in this topic.
Each XLS file must contain the following named areas. (Use the Excel Insert > Name menu item to re-create or modify the named areas.) The named areas can appear once anywhere in the Excel file. Each named label must conform to the correct number of fields, as shown in the Default.xls file, and defined as follows:
Named area | Description |
|---|---|
ReportTitle | Name of the report as displayed on the web interface screen This area must contain a single column. The named area typically resides on the Home tab of the worksheet. |
ReportTime | Date and time when the report is generated and the system contact (as configured on the System Parms tab of the web interface) The area must contain a single column. The named area typically resides on the Home tab of the worksheet. |
DeviceSummary | List of all devices in the filtered data source and a count of the messages that are received from the device The named area must contain three columns. This typically resides on the Dev Summary tab of the worksheet. |
UserSummary | List of all user names in the filtered data source and a count of the messages that are received and reference each user The named area must contain two columns. This typically resides on the User Summary tab of the worksheet. |
FacilitySummary | List of all facilities in the filtered data source and a count of the messages that are received from the facility The named area must contain two columns. This typically resides on the Facility Summary tab of the worksheet. |
SeveritySummary | List of all severities in the filtered data source and a count of the messages that are received with the severity The named area must contain two columns. This typically resides on the Severity Summary tab of the worksheet. |
FacilityData | List of all facilities in the filtered data source and a count of the messages that are received from the facility Counters are broken down by day. The named area must contain twenty-seven columns. This typically resides on the Facility Hist tab of the worksheet. |
SeverityData | List of all severities in the filtered data source and a count of the messages that are received with the severity Counters are broken down by day. The named area must contain ten columns. This typically resides on the Severity Hist tab of the worksheet. |
KeywordData | List of keyword counters configured for the report and a count of the messages that are received and match the severity Counters are broken down by day. This area typically resides on the Keyword Hist tab of the worksheet. |
MessageData | Selected Message Data, consisting of the Message Time, Message Address, Message Facility, Message Severity, and Message Data fields The named area must contain proper columns. This typically appears on the Messages tab of the worksheet. The data is populated in the worksheet only if the report has the Include Messages In Report setting to Yes. The default value is No, which conserves processing time and file sizes. |
In addition to the named fields, the template files can contain graphs, functions, formulas, macros, and other Excel elements that can be used to format and reduce the report data. For detailed information on Excel, refer to Microsoft documentation.
Support for other databases
In addition to XLS files, this directory can contain DSN files that are File DSN configuration files created by the standard administrative ODBC dialog box, found in the Windows Control Panel.
If the file ends with a .dsn suffix, then data is written to the specified database rather than the Excel spreadsheet. In this case, the file is not published and the hyperlink for the file is not operational.
Any tables with the named areas (listed previously) are dropped and re-created in the named data source each time the report is generated.
This provides a mechanism for supporting other reporting databases, possibly in support of third-party tools.
Converting an XLS file to an XLSX file
To comply with your organization’s security policies and to be compatible with latest spreadsheet application software, you can convert the XLS files that you download from BMC Defender Server to XLSX files.
To convert an XLS file to an XLSX file
- On the System > Schedule tab, click Edit Advanced Schedules.
To schedule a periodic file conversion, create a custom schedule by performing the following steps:
- Select a custom schedule (sched-1, sched-2, sched-3, or sched-4).
- Select the required frequency for conversion by selecting the day (Daily or any day of the week) and time.
Open the installationDirectory\config\sched.cnf file and enter the following value:
sched-n convert.bat "installationDirectory\s-html\reports" "officeDir"
Replace installationDirectory with the directory in which you installed the product. The default directory is C:\Program Files\BMC Software\BMC Defender.
Replace sched-n with the custom schedule you created (sched-1, sched-2, sched-3, or sched-4) in step 2, and officeDir with the directory that contains the excelcnv.exe file. The default directory is C:\Program Files\Microsoft Office\root\Office16.
After the custom schedule runs the periodic file conversion, the converted XLSX files are saved in installationDirectory\s-html\reports\xlsx.