Dump all tables in Loginsight 8.18 to csv files

Products

• VMware vRealize Log Insight 8.x

Issue/Introduction

In some scenarios , we need  end user to dump all  tables in Loginsight 8.18 to csv files, then send the files to TSE.Unfortunatelly Apache Cassandra DB doesn't provide any tool to dump the full database.

 

Environment

Loginsight 8.18

Cause

If you have some experience with SQL databases you know tools like  pg_dump that allow to extract a database into a SQL script file. Unfortunatelly Apache Cassandra DB doesn't provide any similar tool. We can only COPY a table to a .csv files.   

Resolution

For loginsight 8.18, there are 78 tables, and they are in 6 keyspaces.

So we can make a list, then dump all the 78 tables.

COPY configuration.config TO '/tmp/myVRLI/csvfiles/configuration.config.csv' WITH HEADER = TRUE;
COPY election.leaders TO '/tmp/myVRLI/csvfiles/election.leaders.csv' WITH HEADER = TRUE;
COPY inventory.inv_clients_v2 TO '/tmp/myVRLI/csvfiles/inventory.inv_clients_v2.csv' WITH HEADER = TRUE;
COPY inventory.inv_events TO '/tmp/myVRLI/csvfiles/inventory.inv_events.csv' WITH HEADER = TRUE;
COPY inventory.inv_source_clients TO '/tmp/myVRLI/csvfiles/inventory.inv_source_clients.csv' WITH HEADER = TRUE;
COPY inventory.inv_source_items TO '/tmp/myVRLI/csvfiles/inventory.inv_source_items.csv' WITH HEADER = TRUE;
COPY inventory.inv_sources_v2 TO '/tmp/myVRLI/csvfiles/inventory.inv_sources_v2.csv' WITH HEADER = TRUE;
COPY loadbalancer.leaders TO '/tmp/myVRLI/csvfiles/loadbalancer.leaders.csv' WITH HEADER = TRUE;
COPY logdb.ad_group  TO '/tmp/myVRLI/csvfiles/logdb.ad_group.csv' WITH HEADER = TRUE;
COPY logdb.ad_group_index  TO '/tmp/myVRLI/csvfiles/logdb.ad_group_index.csv' WITH HEADER = TRUE;
COPY logdb.alert_fire_history  TO '/tmp/myVRLI/csvfiles/logdb.alert_fire_history.csv' WITH HEADER = TRUE;
COPY logdb.alert_fire_history_v2 TO '/tmp/myVRLI/csvfiles/logdb.alert_fire_history_v2.csv' WITH HEADER = TRUE;
COPY logdb.alert_histories TO '/tmp/myVRLI/csvfiles/logdb.alert_histories.csv' WITH HEADER = TRUE;
COPY logdb.alert_schedules_v2 TO '/tmp/myVRLI/csvfiles/logdb.alert_schedules_v2.csv' WITH HEADER = TRUE;
COPY logdb.alert_state TO '/tmp/myVRLI/csvfiles/logdb.alert_state.csv' WITH HEADER = TRUE;
COPY logdb.alert_vrops_prop_map TO '/tmp/myVRLI/csvfiles/logdb.alert_vrops_prop_map.csv' WITH HEADER = TRUE;
COPY logdb.alerts TO '/tmp/myVRLI/csvfiles/logdb.alerts.csv' WITH HEADER = TRUE;
COPY logdb.alerts_by_webhook TO '/tmp/myVRLI/csvfiles/logdb.alerts_by_webhook.csv' WITH HEADER = TRUE;
COPY logdb.api_user TO '/tmp/myVRLI/csvfiles/logdb.api_user.csv' WITH HEADER = TRUE;
COPY logdb.auto_fields TO '/tmp/myVRLI/csvfiles/logdb.auto_fields.csv' WITH HEADER = TRUE;
COPY logdb.bookmarked_queries TO '/tmp/myVRLI/csvfiles/logdb.bookmarked_queries.csv' WITH HEADER = TRUE;
COPY logdb.ca_certs_v3 TO '/tmp/myVRLI/csvfiles/logdb.ca_certs_v3.csv' WITH HEADER = TRUE;
COPY logdb.content_packs_v2 TO '/tmp/myVRLI/csvfiles/logdb.content_packs_v2.csv' WITH HEADER = TRUE;
COPY logdb.dashboard_groups TO '/tmp/myVRLI/csvfiles/logdb.dashboard_groups.csv' WITH HEADER = TRUE;
COPY logdb.data_set TO '/tmp/myVRLI/csvfiles/logdb.data_set.csv' WITH HEADER = TRUE;
COPY logdb.data_set_index TO '/tmp/myVRLI/csvfiles/logdb.data_set_index.csv' WITH HEADER = TRUE;
COPY logdb.directory_group TO '/tmp/myVRLI/csvfiles/logdb.directory_group.csv' WITH HEADER = TRUE;
COPY logdb.directory_group_index TO '/tmp/myVRLI/csvfiles/logdb.directory_group_index.csv' WITH HEADER = TRUE;
COPY logdb.disabled_checks TO '/tmp/myVRLI/csvfiles/logdb.disabled_checks.csv' WITH HEADER = TRUE;
COPY logdb.entitlement_config TO '/tmp/myVRLI/csvfiles/logdb.entitlement_config.csv' WITH HEADER = TRUE;
COPY logdb.export_task TO '/tmp/myVRLI/csvfiles/logdb.export_task.csv' WITH HEADER = TRUE;
COPY logdb.fields_by_id TO '/tmp/myVRLI/csvfiles/logdb.fields_by_id.csv' WITH HEADER = TRUE;
COPY logdb.fields_by_name TO '/tmp/myVRLI/csvfiles/logdb.fields_by_name.csv' WITH HEADER = TRUE;
COPY logdb.fields_by_user TO '/tmp/myVRLI/csvfiles/logdb.fields_by_user.csv' WITH HEADER = TRUE;
COPY logdb.group TO '/tmp/myVRLI/csvfiles/logdb.group.csv' WITH HEADER = TRUE;
COPY logdb.group_index TO '/tmp/myVRLI/csvfiles/logdb.group_index.csv' WITH HEADER = TRUE;
COPY logdb.in_production_checks TO '/tmp/myVRLI/csvfiles/logdb.in_production_checks.csv' WITH HEADER = TRUE;
COPY logdb.ingestion_hosts_v3 TO '/tmp/myVRLI/csvfiles/logdb.ingestion_hosts_v3.csv' WITH HEADER = TRUE;
COPY logdb.inv_clients TO '/tmp/myVRLI/csvfiles/logdb.inv_clients.csv' WITH HEADER = TRUE;
COPY logdb.inv_settings TO '/tmp/myVRLI/csvfiles/logdb.inv_settings.csv' WITH HEADER = TRUE;
COPY logdb.inv_sources TO '/tmp/myVRLI/csvfiles/logdb.inv_sources.csv' WITH HEADER = TRUE;
COPY logdb.last_updated TO '/tmp/myVRLI/csvfiles/logdb.last_updated.csv' WITH HEADER = TRUE;
COPY logdb.leaders TO '/tmp/myVRLI/csvfiles/logdb.leaders.csv' WITH HEADER = TRUE;
COPY logdb.license_v2 TO '/tmp/myVRLI/csvfiles/logdb.license_v2.csv' WITH HEADER = TRUE;
COPY logdb.migration_data TO '/tmp/myVRLI/csvfiles/logdb.migration_data.csv' WITH HEADER = TRUE;
COPY logdb.node_upgrade_status TO '/tmp/myVRLI/csvfiles/logdb.node_upgrade_status.csv' WITH HEADER = TRUE;
COPY logdb.osi_count TO '/tmp/myVRLI/csvfiles/logdb.osi_count.csv' WITH HEADER = TRUE;
COPY logdb.osi_histogram TO '/tmp/myVRLI/csvfiles/logdb.osi_histogram.csv' WITH HEADER = TRUE;
COPY logdb.osi_histogram_daily TO '/tmp/myVRLI/csvfiles/logdb.osi_histogram_daily.csv' WITH HEADER = TRUE;
COPY logdb.scheduled_reports TO '/tmp/myVRLI/csvfiles/logdb.scheduled_reports.csv' WITH HEADER = TRUE;
COPY logdb.service_registry TO '/tmp/myVRLI/csvfiles/logdb.service_registry.csv' WITH HEADER = TRUE;
COPY logdb.shared_dashboard_urls TO '/tmp/myVRLI/csvfiles/logdb.shared_dashboard_urls.csv' WITH HEADER = TRUE;
COPY logdb.short_urls TO '/tmp/myVRLI/csvfiles/logdb.short_urls.csv' WITH HEADER = TRUE;
COPY logdb.snapshot TO '/tmp/myVRLI/csvfiles/logdb.snapshot.csv' WITH HEADER = TRUE;
COPY logdb.snapshot_order TO '/tmp/myVRLI/csvfiles/logdb.snapshot_order.csv' WITH HEADER = TRUE;
COPY logdb.ssl_keystore TO '/tmp/myVRLI/csvfiles/logdb.ssl_keystore.csv' WITH HEADER = TRUE;
COPY logdb.table_modification_times TO '/tmp/myVRLI/csvfiles/logdb.table_modification_times.csv' WITH HEADER = TRUE;
COPY logdb.tomcat_sessions TO '/tmp/myVRLI/csvfiles/logdb.tomcat_sessions.csv' WITH HEADER = TRUE;
COPY logdb.upgrade_status TO '/tmp/myVRLI/csvfiles/logdb.upgrade_status.csv' WITH HEADER = TRUE;
COPY logdb.user TO '/tmp/myVRLI/csvfiles/logdb.user.csv' WITH HEADER = TRUE;
COPY logdb.user_ad_groups TO '/tmp/myVRLI/csvfiles/logdb.user_ad_groups.csv' WITH HEADER = TRUE;
COPY logdb.user_auth TO '/tmp/myVRLI/csvfiles/logdb.user_auth.csv' WITH HEADER = TRUE;
COPY logdb.user_auth_history TO '/tmp/myVRLI/csvfiles/logdb.user_auth_history.csv' WITH HEADER = TRUE;
COPY logdb.user_directory_groups TO '/tmp/myVRLI/csvfiles/logdb.user_directory_groups.csv' WITH HEADER = TRUE;
COPY logdb.user_index TO '/tmp/myVRLI/csvfiles/logdb.user_index.csv' WITH HEADER = TRUE;
COPY logdb.user_usage_reporting_config TO '/tmp/myVRLI/csvfiles/logdb.user_usage_reporting_config.csv' WITH HEADER = TRUE;
COPY logdb.vimevent_context TO '/tmp/myVRLI/csvfiles/logdb.vimevent_context.csv' WITH HEADER = TRUE;
COPY logdb.webhooks TO '/tmp/myVRLI/csvfiles/logdb.webhooks.csv' WITH HEADER = TRUE;
COPY logdb.webhooks_by_alert TO '/tmp/myVRLI/csvfiles/logdb.webhooks_by_alert.csv' WITH HEADER = TRUE;
COPY machine_learning.spock_cluster_counts TO '/tmp/myVRLI/csvfiles/machine_learning.spock_cluster_counts.csv' WITH HEADER = TRUE;
COPY machine_learning.spock_cluster_diffs TO '/tmp/myVRLI/csvfiles/machine_learning.spock_cluster_diffs.csv' WITH HEADER = TRUE;
COPY machine_learning.spock_cluster_leases TO '/tmp/myVRLI/csvfiles/machine_learning.spock_cluster_leases.csv' WITH HEADER = TRUE;
COPY machine_learning.spock_clusters TO '/tmp/myVRLI/csvfiles/machine_learning.spock_clusters.csv' WITH HEADER = TRUE;
COPY machine_learning.spock_exclusive_tasks TO '/tmp/myVRLI/csvfiles/machine_learning.spock_exclusive_tasks.csv' WITH HEADER = TRUE;
COPY machine_learning.spock_global_queries_v2 TO '/tmp/myVRLI/csvfiles/machine_learning.spock_global_queries_v2.csv' WITH HEADER = TRUE;
COPY machine_learning.spock_pattern_status TO '/tmp/myVRLI/csvfiles/machine_learning.spock_pattern_status.csv' WITH HEADER = TRUE;
COPY machine_learning.spock_patterns_v2 TO '/tmp/myVRLI/csvfiles/machine_learning.spock_patterns_v2.csv' WITH HEADER = TRUE;
COPY machine_learning.spock_pending_clusters TO '/tmp/myVRLI/csvfiles/machine_learning.spock_pending_clusters.csv' WITH HEADER = TRUE;

 

To make it simple:

a) Save these COPY command in a file: vrli-tablelist.sql

 

b) Then run a .sh script file to dump these tables;

 

dump-db.sh:

mkdir  -p  /tmp/myVRLI/csvfiles
rm -rf  /tmp/myVRLI/csvfiles/*
/opt/vmware/bin/cqlsh-no-pass -f vrli-tablelist.sql
tar czvf  /tmp/vrli-db-dump.tgz  /tmp/myVRLI/csvfiles/*

c) Run this dump-db.sh, it will dump 78 tables to /tmp/myVRLI/csvfiles/, then generate a .tgz file:  /tmp/vrli-db-dump.tgz

The end user need to send this /tmp/vrli-db-dump.tgz to us.