ouhscbbmc/dbmonitor

Monitor characteristics of databases involving the BBMC

dbmonitor's Issues

odbcDriverConnect

@bard1536, If you're not using RODBC::odbcDriverConnect, consider doing it in the future. I've been using RODBC::odbcConnect, which requires that you manually set up a DSN on each machine that calls it. Like the DSNs we set up, it uses the Window's credentials (that's what the 'trusted_connection' part means).

This will save a little work the way we typically use it. It could save a lot of work if there are a lot of machines using it.

There are some benefits of the manual DSN approach. I like that a GUI is used to point to the database, and then test the connection --independent of any R code.

Close the issue when you've read it.

http://stackoverflow.com/questions/5720508/sql-server-rodbc-connection
Here's the core line:

dbhandle <- odbcDriverConnect('driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true')

Correct counts in old/initial versions

In the beginning of the project, the SQL code was double/triple counting some rows. Export from REDCap into R, correct the counts, and import them back into R.

Create Longitudinal project in REDCap

@thomasnwilson, thanks for agreeing to do this. As of right now, I think this project needs just four columns.

1. record_id: autonumber field (recommended by @thomasnwilson )
2. database: a text field
3. table: a text field
4. probe_date: a date and time field
5. row_count: an integer field

Please call my house if you have questions.

Create Roster project in REDCap

Spun-off from #1
The second/parent project is more like demographic information, where there's just one record per database. Currently, I'm thinking:

1. roster_id a primary key
2. database a text field for the name
3. server a text field for the name of the server
4. dsn a text field for the name of the ODBC DSN on the tool's local machine
5. name_pi the principle investigator's
6. email_pi their email
7. name_manager the person who actually knows what's going on
8. email_manager their name
9. server_type right now, it we'll support only SQL Server. Maybe Access or MySQL later. The backup tool (which is probably written in R) will need to know, because they'll be handled differently
10. monitor_row_count a boolean field for whether the database's tables' rows should be counted.
11. should_backup a boolean field for whether the tool should still be backing up the database

Create basic monitoring script

The heart of the automation will

• query SQL Server retrieve the tokens and REDCap URL
• query REDCap to get a roster of all the databases to monitor and/or backup
• for each database, execute a standard SQL script that returns a data.frame where each record represents a table, and has the row count.
• find the largest record_id and add that number to the records about to be added
• augment it with date/time and server name
• write to REDCap

Enable plugins on rcapdev?

@thomasnwilson, I'm trying to get plugins working for rcapdev. Did you do anything to enable plugins on miechvprojects?

I don't see anything in the settings, or in this link: https://iwg.devguard.com/trac/redcap/wiki/Plugins

I'm wondering if it has something to do with the URL configuration warnings we're getting from that server (https://github.com/OuhscCcanMiechvEvaluation/MReporting/issues/252). I'm emailing Cliff now to see if there's any progress.

Verify SSL certificate of REDCap server

Implement this: OuhscBbmc/RedcapGovernanceDocs#12 (comment)

(also see https://github.com/OuhscCcanMiechvEvaluation/MReporting/issues/277)

Email from REDCap or PHP?

@thomasnwilson, have you done anything in PHP or REDCap to send an email with code? Did you ever get is working R? If so, I'd like to know the settings that made it work.

I'm thinking that's a critical feature we'll need for this. I'm not seeing anything about email from a REDCap plugin.

A paragraph near the front of ch 14 in our PHP makes it sound easy. But doesn't enough details for me (who was just skimming).

Explanation of the DBMonitor project

I just wrote this email to the first users. I'm posting it here because I think it's a good start to describe the purpose of this tool to people who's databases we might use it for. @bard1536 & @thomasnwilson, feel free to make comments/questions.

I’m adding the four of you to a REDCap project, so you’ll probably get a funny email in the next few minutes.

I’m not moving any of your database from SQL Server to REDCap. The four things REDCap will be doing are:

1. Storing row counts (not the actual data) of each table in your database. The database will be automatically & periodically polled, and the row counts will be deleted. This longitudinal record will help detect if (a) a large batch of records was deleted, or (b) not as many records were added as you expected.

2. Storing the location of where to save the backups. As I’ve discussed with some of you, I want a backup system that’s independent of Peds IS. These backups will be stored as (compressed) CSV files. Anytime you like, you can open these CSVs (with something like Excel) and verify the backups exist. This strategy won’t work for huge databases, but I don’t expect that your specific one will cause any strains.

3. Providing a GUI where you can initiate either step above. The new software will still monitor the record counts and backup to CSV daily. But if there are times when you want run an explicit backup, you should be able to log into REDCap and press a button to initiate it. This feature won’t be complete for another few weeks.

4. Providing a place to see the longitudinal reports of the row counts and the success/failure of the backups.

Create basic backup script

Create a basic script that backs up the tables to csv, in a location designated in the roster.