Monitoring Postgres Table

[autovhcdev]

We have a "stats" table on a postgres server, does anyone know how to get splunk to monitor this? I suspect it involves a script... someone must have already done something like this?

[southeringtonp]

If you just want to dump the contents of that table every XXX minutes, it should be very easy to do.

Just write a shell script or batch file that runs the command-line postgres client and dumps the table(s) you want, and have Splunk index the output. Basically, any query you can run at the command line would do.

Take a look at the documentation on scripted inputs - that should help get you started.

http://www.splunk.com/base/Documentation/4.1.5/Admin/Setupcustom(scripted)inputs

---

If the table you want to monitor is continually growing (i.e., you're continually logging stats over time), then your problem is the same as for any other application that logs to a database.

You may wish to consider having whatever populates the stats table log directly to Splunk, if that's feasible. Assuming it isn't, then you need to do a little more work scripting, and you should consider using Python instead of dealing with shell-scripts and psql:

It depends how your table is structured, but here's a common approach if your table has an an increasing primary key value or timestamp:

• Keep track of the last-seen record ID in a file
• Build your SQL query to retrieve all records with ID values higher than the last one you saw
• Dump the results of the table to stdout
• Update the file with the highest ID value retrieved by your query

[southeringtonp]

To be clear, the original suggestion was not advocating dumping the entire database, just the results of a single query. It depends on how your data is structured though - if you're continually adding new records, then it's more like a traditional log table than just a list of stats. See edits above for more information.

[Lowell]

What do you mean by "directly"? Splunk only indexed textual data, so at some point the records have to be converted into a text-based format that splunk can index. There is really no concept of "adapters" or other such product-specific things, if that's what your thinking.

[autovhcdev]

the database is way too large to dump out, is there a way to index directly?