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>