SQL Scripts

Caution: You must run source /opt/lce/tools/source-for-pgsql-shortcuts once in a console, before you can run psqlf <someScript.sql> commands in that console.

File Usage/Description
Administration: Disk Usage  
activeDb-size.sql Breaks down datastore's disk usage by major categories.
disk-usage-one-silo.sql

States how much disk is being used by this table and associated database objects.

<silo#>

disk-usage-summary.sql Gives a concise summary of disk usage by table category (tables which store events, tables which maintain filter pointers, rollup counts tables, etc.) Output of this script has been added to diag report to facilitate troubleshooting.
drop-indexes-on-older-silos.sql

Allows operator to easily free up disk space by dropping indexes on silos which have not yet been archived or trimmed out of activeDb, but are no longer queried.

[how old, in whole days, must a silo be to have its indexes dropped]

Administration: PostgreSQL Processes  
breve-processes.sql

Shows the SQL command (up to available screen width) being executed by each process. Automatically refreshes display every N (defaults to 10) seconds.

[<refreshInterval_seconds, defaults to 10; 0 to only show once>]

expand-processes.sql Shows complete SQL command (up to 2048 characters) being executed by each process.
progress-analyze.sql

Lists processes running ANALYZE commands, names the target tables and/or indexes, and estimates progress.

[<refreshInterval_seconds, defaults to 15; 0 to only show once>]

progress--bulk-load.sql

Lists processes inserting rows into the siloN tables, and estimates progress; can also track the progress of creating archiveDb snapshots.

[<refreshInterval_seconds, defaults to 5; 0 to only show once>]

progress--index-or-reindex.sql

Lists processes running CREATE INDEX or REINDEX commands, names the target objects, and estimates progress.

progress--rebuild-table.sql

Lists processes running CLUSTER commands, names the target objects, and estimates progress.
progress--stream-backup.sql

Lists processes taking a backup (see Perform an Online PostgreSQL Backup) and estimates progress.

[<refreshInterval_seconds, defaults to 60; 0 to only show once>]

progress–vacuum.sql

Lists processes running VACUUM commands, names the target objects, and estimates progress.
Administration: Tenable Log Correlation Engine Configuration and Alerts  
alerts-by-day.sql For more information, see Alerts.
alerts-by-month.sql For more information, see Alerts.
recent-alerts-24hours.sql For more information, see Alerts.
show-config--changed-since-rpm-install.sql

Shows the following for each configuration attribute changed since the Tenable Log Correlation Engine RPM was installed:

  • Name
  • Current value
  • Date of last modification
show-config--mv--event_rules.sql For clearly displaying the configured event rules. You may find this presentation preferable to the one in Web UI. For more information, see Show All Event Rules.
Administration: License and Utilization Counting Toward License Limits  
show-status.sql Displays information which had formerly been available in command-line environment by invoking /opt/lce/tools/lce_cfg_utils --display-status: daily syslog counts, syslog sources, Tenable Log Correlation Engine daemons' latest start time, etc. Normally an operator will not need this utility, because all that information is also available in Tenable Log Correlation Engine Web UI.
silos.sql

Lists silos ordered by timestamp of oldest event. For each silo, shows:

  • how many events are inside

  • how much rawlog those events collectively contain

  • that silo's provenance (whether it had been recorded live, migrated in from an earlier Tenable Log Correlation Engine version, or imported from a plain-text file)

  • presence in activeDb: N if not, Y if yes, P if present but only temporarily to satisfy “archive-peek” queries.

  • presence in archiveDb: N if not, Y if yes.

  • TASL %: what percentage of this silo has been scanned for processing by TASL scripts.

[ -<N newest silos to show> | <N oldest silos to show> ]

Statistics About Stored Events and Event Normalization  
dimension-occurrence-stats.sql

Permits insight into distribution of the normalized dimensions (event1, event2, sensor, type, user) among stored events.

For additional information, see dimension-occurrence-stats.sql.

event1|event2|sensor|type|user [--long]

ip-occurrence-stats.sql Reports how many stored events are covered by each configured include_networks range. Also, shows top 100 srcIP by event volume (only including IPs recorded in least 5 events/minute on average): this can help you make a better-informed decisions about what range(s) to configured in include_networks.
normalization-percentages.sql

For each dimension (event1, event2, sensor, type, user) reports for what percentage of stored events the respective dimension is known. The numbers are broken down by silo: this lets you quickly gauge extent of event normalization achieved, as well as track progress over time.

throughput--kilo-eps.sql

Shows volume of event influx, by the hour, in units of 1000 events per second.

For additional information, see throughput--kilo-eps.sql.

<daysAgo_max> [<daysAgo_min,default>=0]

useful-and-idle--plugins.sql

Helps you make an informed decision about which PRM and TASL plugins can be disabled without decreasing your event normalization levels; lets you immediately identify any custom plugins which are not working.

<silo#>

Output subsections:

  • Event Counts, by Normalizing .prm File
  • Non-contributing Plugins, by Containing .prm File
  • Entirely Non-contributing .prm Files
  • Entirely Non-contributing .prm Files, Not Already Disabled in Configuration
  • Event Counts, by Engendering .tasl File
  • Non-contributing .tasl Files
  • Non-contributing .tasl Files, Not Already Disabled in Configuration

Tip: To disable any of the plugins, run the command provided in the output.

rawlog-storage.sql

Shows percentile statistics for rawlog length in a given silo's events.

<silo#> <percent of rows to scan; a whole divisor of 100>

Performance Tuning  
buffer-cache--categories.sql Summarizes the current allocation of the PostgreSQL buffer cache.
index-usage-–silo.sql For respective tables, shows which indexes have been used in queries and how often.

indexes-–nonsilo.sql

indexes--silo-fp.sql

indexes--silo-proper.sql

Summarizes some useful information about indexes defined on respective tables.
locks.sql

Lists database object locks currently held by user-focused PostgreSQL tasks.

planner-estimates.sql

Shows estimates that the PostgreSQL query optimizer is now relying on when it generates an access plan for querying the given table. For each column, shows M (defaults to 10) most common values.

<table name> [<mCommonestValues>,=10]

planner-estimates–silo.sql

For each column, shows M (defaults to 10) most common values.

<silo#> [<mCommonestValues>,=10]

routines.sql Lists stored procedures and gives some rudimentary invocation statistics.
rebuild-rawlog-index.sql

Rebuilds index on the rawlog column.  Required to apply modified text search configuration retroactively to events already stored.

<silo#>

table-access-stats-–nonsilo.sql

table-access-stats--silo-fp.sql

table-access-stats--silo-proper.sql

Shows how many times respective tables had been ANALYZEd and/or VACUUMed, and whether a given table has changed since.

table-sizes–nonsilo.sql

Shows how much disk is being used by each table along with its indexes.

[<table name fragment>]

top-statements--by--all-exe-time.sql

Tells which SQL commands and stored-procedure calls take the longest to run.

top-statements--by--n-calls.sql 

Tells which SQL commands and stored-procedure calls are called most frequently.
Troubleshooting  
identify-currsilo.sql Prints numeric ID of the silo that incoming new events are now being written to.
ipfilters-overview.sql Lists IP filters used in recent queries. Shows the first few IP addresses or IP address ranges of each filter. To see all the IP addresses or IP address ranges belonging to a particular filter, use the reconstitute-ipfilter.sql script.
lock-blockers.sql

Shows which processes are waiting their turn to access a particular database object.

migr-full-status.sql Details migration plan and how far has each of its items been executed.
n-events--by-hhour--bar-chart.sql

Displays a rudimentary bar chart to give a rough idea of variation in log volume through the day or through the week. Another view of this information is provided by the throughput--kilo-eps.sql script.

<silo#>

presence-dim-by-hhour.sql

Displays a concise info-graphic of the presence of a particular type (or user, or sensor, or ...) among the events in a given silo, with half-hourly granularity. This can provide a bare-bones reporting capacity even when a Tenable Security Center connection is interrupted.

<silo#> event1|event2|sensor|type|user <literal>

recent-kinds-5minutes.sql

recent-kinds-60minutes.sql

Summarizes events most recently added to activeDb by kind.
reconstitute-ipfilter.sql

Lists all the IP addresses, or IP address ranges, belonging to a particular filter.

<ipfilterId>