Skip to main content
Version: 10.2
warning

This page is intentionally hidden from navigation but remains accessible via direct link. It is retained for internal reference purposes only and may contain outdated or transitional content.

Database Extraction

The database_extraction is a Perl tool that extracts the Core Server database data. It is a helper tool for customers who want to extract data from the database without using web GUI to put data into another database for processing. The process_powerbi.conf also runs this script when activated.

Requirements​

  • Open iT Core Server
  • Administrative rights
  • List of data types and data type's ages to extract

Restriction on Usage​

The Distinct measurement is not supported in the database_extraction.pl script. The script accesses raw data, which doesn't include distinct measurement because it is computed after the reporting stage.

The Core Server will initially extract the data, then internally manage the data, and process the data using the Distinct measurement. This explains why the database_extraction.pl script is unable to get the Distinct measurement.

Distinct is also a virtual measurement that Open iT adds while creating the report.

Manually Running Database Extraction​

This section provides instructions for manually running a database extraction, allowing you to generate customized reports based on your preferred data level, data type, period, and format.

  1. Open a command prompt with Administrator level privileges.

  2. Go to the bin directory, which is by default in C:\Program Files\OpeniT\Core\bin, run the command:

    Command Syntax
    cd $BIN_DIR
    Example
    cd "C:\Program Files\OpeniT\Core\bin"

  3. Run the following command:

    Command Syntax
    database_extraction --level <data_level> --datatypes <datatypes> --period <period> --format csv

    Required Parameters:

    ParameterDescription
    --level <data_level>Use this parameter to specify the level of data to extract. You can extract data at the following levels:
    • year (Y)
    • month (M)
    • week (W)
    • day (D)
    • hour (h)
    • 15 minutes (15m)
    • 5 minutes (5m)
    --datatypes <datatypes>Use this parameter to specify the data types to extract. The data type IDs must be separated by a comma (,). To generate for all available data types, specify all.

    If more than one data type (or all) is specified, then you cannot use some of the other optional parameters (see parameters marked with **). These options only work when one or both of --classifications and --measurements are in use.
    --period <period>Use this parameter to specify the period you want to extract data, either as a single year, month, or day; or as a range of years, months, or days, depending on level.

    Written as: YYYY[-YYYY] or YYYY/MM[-YYYY/MM] or YYYY/MM/DD[-YYYY/MM/DD].

    If weekly resolution is used on the --level parameter, the exact start and end date must be specified.
    --format csvUse this parameter to specify the format of the output. Currently, the only format supported is CSV (specify it in lowercase).
    Database Extraction Required Parameters

    Optional Parameters:

    ParameterDescription
    --classifications **Use this parameter to specify one or more classification names, each separated by a comma (,). You can use this parameter if only one data type is selected. If one or more classifications contain a space, the whole string must be quoted. By default, all non-virtual classifications are included. You can include a set of classification values by putting them in parentheses after the classification name. The values are delimited by commas (,).
    --measurements **Use this parameter to specify one or more measurement names, each separated by a comma (,). If one or more measurements contains a space, the whole string must be quoted. By default, all measurements are included. Virtual measurements are not accessible.
    --useepoch **Use this parameter to output the epoch time as a number and not format the time stamp.
    --userresolution **Use this parameter to add data type numbers in the records.
    --metaUse this parameter to specify the action to take on meta values. The possible values are:
    • keep – will leave the value unchanged
    • trim – will remove enclosing angle brackets
    • clear – will give an empty string
    --dirUse this parameter to specify the output directory. The output is written in the temp directory, which is by default in C:\ProgramData\OpeniT\Data\temp.
    --filenameUse this parameter to specify the output filename.
    --digitsUse this parameter to specify the number of digits after the decimal point. The default is given by the REPORTS_CSV_DECIMAL_DIGITS directive in the openit.cfg file, which is by default in C:\Program Files\OpeniT\Core\Configuration, or, if this is not set, the default is 2. Add a + or - to always round up or down (e.g., 3+).
    --vlevel **Use this parameter to specify the database level of the data to extract. The vlevel is used for virtual mappings. You can extract data at the following levels:
    • year (Y)
    • month (M)
    • week (W)
    • day (D)
    • hour (h)
    • 15 minutes (15m)
    • 5 minutes (5m)
    The --level parameter is used to specify the level of the output file, while the --vlevel parameter indicates the level of the input from the database. The reason for using --vlevel is that the virtual mapping configuration (C:\Program Files\OpeniT\Core\mappings) might exhibit daily or weekly granularity. As a result, this granularity must be reflected in the resolution used internally before the data is aggregated to the --level parameter with month or year resolution.
    --sortUse this parameter to sort output on time, if time is selected.
    --noinfoUse this parameter to suppress info messages.
    --noaverageUse this parameter to not calculate the average values but keep and add the accumulated values as they are in the database (i.e., do not divide by total weight).
    --stdoutUse this parameter to write output to STDOUT.
    --helpUse this parameter to display the help message.
    Database Extraction Optional Parameters

    Example
    database_extraction --level D --datatypes all --period 2023 --format csv

    This example generates a CSV output for day (D) level, for all data types, and for the period 2023 (that is, for the whole year).

    NOTE

    You can generate a selection-type report by utilizing the --classifications or the --measurement option, or a combination of both. The resulting file will automatically include a header and a timestamp in addition to the chosen classifications and measurements.

  4. Verify that the output directory is in the temp directory, which is by default in C:\ProgramData\OpeniT\Data\temp.