Vertica integration

Our Vertica integration monitors the performance of your data storage.

After setting up our Vertica integration, we give you a dashboard for your Vertica metrics.

Install the infrastructure agent

You can install the infrastructure agent two different ways:

Our guided install is a CLI tool that inspects your system and installs the infrastructure agent alongside the application monitoring agent that best works for your system. To learn more about how our guided install works, check out our Guided install overview.
If you'd rather install our infrastructure agent manually, you can follow a tutorial for manual installation for Linux, Windows, or macOS.

Use `nri-flex` to capture metrics

Flex comes bundled with the New Relic infrastructure agent. You need to configure nri-flex for Vertica and create a flex configuration file.

Follow these steps:

Create a file named vertica-flex-config.yml in this path:
bash
```
$/etc/newrelic-infra/integrations.d
```
Update the file vertica-flex-config.yml with these configurations:
- EVENT_TYPE: You can consider EVENT_TYPE as a New Relic database table that you can query using NRQL.
- COMMAND: This contains the command which is used to print metrics on the terminal.
Once your configuration file is updated, it will look like below:

--- 
  integrations:
    - name: nri-flex
      config:
        name: VerticaFlex
        apis:
          - database: vertica
            db_conn: 'vertica://dbadmin:vertica@13.235.79.230:5433/dbadmin'
            logging:
              open: true
            db_async: true # process queries async
            db_queries:
              - name: VerticaFindCitiesDetails
                run: SELECT * FROM cities;
              - name: VerticaDiskSpaceUsedByTables
                run: SELECT projection_schema, anchor_table_name, to_char(sum(used_bytes)/1024/1024/1024,'999,999.99') as disk_space_used_gb FROM projection_storage
GROUP by projection_schema, anchor_table_name ORDER by disk_space_used_gb desc limit 50;
              - name: VerticaFreeDiskSpace
                run: SELECT to_char(sum(disk_space_free_mb)/1024,'999,999,999') AS disk_space_free_gb, to_char(sum(disk_space_used_mb)/1024,'999,999,999') AS disk_space_used_gb FROM disk_storage;
              - name: VerticaUserInformation
                run: SELECT user_name, is_super_user, resource_pool, memory_cap_kb, temp_space_cap_kb, run_time_cap FROM users;
              - name: VerticaUserDetails
                run: SELECT * FROM user_sessions;
              - name: VerticaQueriesByUser
                run: SELECT * FROM query_profiles WHERE user_name ILIKE '%dbadmin%';
              - name: VerticaUserRolesInformation
                run: SELECT * FROM roles;
              - name: VerticaResourcePoolAssignments
                run: SELECT user_name, resource_pool FROM users;
              - name: VerticaTableInfo
                run: SELECT table_name, is_flextable, is_temp_table, is_system_table, count(*) FROM tables GROUP by 1,2,3,4;
              - name: VerticaActiveEvents
                run: SELECT * FROM active_events WHERE event_problem_description NOT ILIKE '%state to UP';
              - name: VerticaDiskStorage
                run:  SELECT node_name, storage_path, storage_usage, storage_status, disk_space_free_percent FROM disk_storage;
              - name: VerticaLongRunningQueries
                run: SELECT query_duration_us/1000000/60 AS query_duration_mins, table_name, user_name, processed_row_count AS rows_processed, substr(query,0,70) FROM query_profiles ORDER BY query_duration_us DESC LIMIT 250;
              - name: VerticaLicenseConsumption
                run: SELECT GET_COMPLIANCE_STATUS();
              - name: VerticaAudit
                run: SELECT AUDIT('');

Forward your Vertica logs to New Relic

You can use our log forwarding to forward Vertica logs to New Relic.

On Linux machines, your can find your log file named logging.yml in this path:

bash

$/etc/newrelic-infra/logging.d/

After creating the log file, add the following script to the logging.yml file:

logs:
  - name: vertica.log
    file: /home/dbadmin/vdb/v_vdb_node0001_catalog/vertica.log
    attributes:
      logtype: vertica_log

Restart the New Relic infrastructure agent

Before you can start reading your data, use the instructions in our infrastructure agent docs to restart your infrastructure agent.

bash

$sudo systemctl restart newrelic-infra.service

In couple of minutes, your application will send metrics to one.newrelic.com.

Find your data

You can choose our pre-built dashboard template named Vertica to monitor your Vertica application metrics. Follow these steps to use our pre-built dashboard template:

From one.newrelic.com, go to the + Add data page.
Click on Dashboards.
In the search bar, type vertica.
The Vertica dashboard should appear. Click on it to install it.

Your Vertica dashboard is considered a custom dashboard and can be found in the Dashboards UI. For docs on using and editing dashboards, see our dashboard docs.

Here's a NRQL query to search database user information for data points like user name, resource pool, memory cap, temporary space cap, and run time cap.

SELECT user_name, is_super_user, resource_pool, memory_cap_kb, temp_space_cap_kb, run_time_cap 
FROM VerticaUserInformation;

What's next?

To learn more about building NRQL queries and generating dashboards, check out these docs:

Introduction to the query builder to create basic and advanced queries.
Introduction to dashboards to customize your dashboard and carry out different actions.
Manage your dashboard to adjust your display mode, or to add more content to your dashboard.