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>]

command-progress--index--create-or-rebuild.sql

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

command-progress--cluster.sql

Lists processes running CLUSTER commands, names the target objects, and estimates progress.

command-progress--vacuum.sql 

Lists processes running VACUUM commands, names the target objects, and estimates progress.
expand-processes.sql Shows complete SQL command (up to 2048 characters) being executed by each process.
Administration: LCE 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 LCE 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, LCE daemons' latest start time, etc. Normally an operator will not need this utility, because all that information is also available in LCE 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 LCE version, or imported from a plain-text file)

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

Statistics About Stored Events and Event Normalization  
cardinalities.sql

Shows how many distinct values are there for each of a siloN table's columns.

<silo#>

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.

varlen-columns-stats.sql

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

<silo#>

Performance Tuning  
all-columns.sql Lists all the distinct columns in schema, along with respective datatypes' alignment and size.
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.

nondefault-settings.sql Shows the current PostgreSQL server settings.
planner-estimate-basis.sql

Shows estimates that the PostgreSQL query optimizer is now relying on when it generates an access plan for querying the given silo.

<silo#>

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.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.sc 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>

silo-partitions.sql

Lists silos in activeDb, focusing on the respective partitions of the events table.

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