TOC & Recently Viewed

Recently Viewed Topics

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.
clear-one-silo.sql

Removes the requested silo from activeDb. It is strongly suggested that you do not invoke this script directly, but use archive-manager --remove-active <siloName> instead.

<silo#>

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  
cancel.sql

Signals a user-focused PostgreSQL task to stop work and return to caller.

<backendPID> // E.g., 314159

full-processes.sql Shows complete SQL command (up to 2048 characters) being executed by each process.
watch-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>]

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

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]

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

procedures.sql Lists stored procedures and gives some rudimentary invocation statistics.

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.
Troubleshooting  
events-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> ]

hhourly-counts.sql Takes no argument
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.sqlrecent-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>

 

Copyright © 2019 Tenable, Inc. All rights reserved. Tenable, Tenable.io, Tenable Network Security, Nessus, SecurityCenter, SecurityCenter Continuous View and Log Correlation Engine are registered trademarks of Tenable, Inc.. Tenable.sc, Lumin, Assure, and the Cyber Exposure Company are trademarks of Tenable, Inc. All other products or services are trademarks of their respective owners.