Metadata Report for SmatDB

[Tom Righele]

Hello all-

I’ve been asked to generate a report based off of our SMAT to identify when there are long periods of inactivity. Is there anyway to write a TCL or sh script to spit out metadata of specific smat files?

Thanks,

Tom

8)

Topic

[Mark Thompson]

If you are using smatdb, an sqlite query can gather information for you.

This script (written for AIX) uses Tcl sqlite library routines to display MessageContent.  The SELECT statement could be altered to whatever metadata fields you want to view.  See https://www.sqlite.org/tclsqlite.html

Code:

#!/usr/bin/env hcitcl
#
######################################################################
# Name:      smatdb2nl
# Purpose:   Dump smatdb files to nl-delimited messages
# UPoC type: script
# Args:      smatdb1 … smatdbN
#
# Returns:   Newline delimited messages
#
# Notes:
# It will be difficult to find message boundaries in the output for messages containing NL characters.
# Script does not work with encrypted smatdb files – see hcismatdb for help.
#
# History:
# Internal documentation notes here …

proc smatdb2nl {argv} {
foreach smatfile $argv {
sqlite smatdb $smatfile
smatdb eval {SELECT MessageContent FROM smat_msgs} {
puts [string map [list n r] $MessageContent]
}
}
}
smatdb2nl $argv

- Mark Thompson
HealthPartners

[Tom Righele]

Thank you Mark! I’m unfortunately working with encrypted smatdb files. Anyway to get around this easily?

-Tom

[Mark Thompson]

Adding this line before the SELECT in the code above will apply your encryption key.

Warning:  Using this on an unencrypted database immediately encrypts the database.  See Charlie Bursell’s hcismatdb for a great example of how to cautiously open encrypted smatdb’s.

Code:

smatdb eval “PRAGMA KEY=’yourEncryptionKeyHere'”

- Mark Thompson
HealthPartners

[Keith McLeod]

Have not explore the meta data aspect.

[Tom Righele]

Thank you all for your responses!

I’ve been playing around with these and I’m still having some trouble. I apologize, I’m not very familiar with SQLite.  😳

I’ve copied a smatdb to a test folder and ran the smatdb2nl against the file, but I am not seeing anything. Should I be seeing some type of output after I run the file?

Thanks again and apologize for the questions!

[Mark Thompson]

The smatdb2nl script does not work on encrypted files unless you add that functionality.

You can test sqlite from an command line like this.  Count the number of records in the smatdb, where smatdbname is the full file name, encryptionKey is your encryption key.

Code:

>sqlite smatdbname
sqlite> PRAGMA KEY=”encryptionKey”;
sqlite> select count(*) from smat_msgs;

If you send me a PM, we can arrange a time to look at this offline.

- Mark Thompson
HealthPartners

[Author]

Replies