Tom,
We have written a command line program to gain access to the SMATDB files and have had luck running the code below. The one thing that I noticed about yours is that you have sqlcipher that I am not familiar with. I am not sure if you can use the PIPE to sqlcipher that way in the DBCMD. Of course I could be wrong but I have no idea.
This is not the full program below and some of the logic was taken from Charlie’s hcismatdb program that he posted a while back. ( Thanks Charlie for the great work over the years ). Some of the logic is old and just copied from other programs.
There are a lot of command line options that my program takes to make certain decisions about which files to choose, which sqlcmd to build, etc.
cd $HciRootDir/$site/exec/processes
set SmatFiles [recursive_glob ./ “*${thread}*smatdb”]
if { $optd } { puts “SmatFiles == $SmatFiles” }
if { [llength $SmatFiles] $TypeOfSql”}
set whatToFind [string toupper $whatToFind]
if { $optH == 0 } {
switch -exact — $TypeOfSql {
“hour” {
set sqlcmd “select MessageContent,DestConn,DriverCtl,UserData,SourceConn,Time,TimeIn,TimeOut
from smat_msgs where UPPER(MessageContent) like ‘%$whatToFind%’ and
TimeIn >= $FirstTime;”
}
“SingleDay” {
set sqlcmd “select MessageContent,DestConn,DriverCtl,UserData,SourceConn,Time,TimeIn,TimeOut
from smat_msgs where UPPER(MessageContent) like ‘%$whatToFind%’ and
TimeIn >= $MidnightOf and TimeOut = $MidnightOfBegin and TimeOut = $FirstTime;”
}
“SingleDay” {
set sqlcmd “select MessageContent,DestConn,DriverCtl,UserData,SourceConn,Time,TimeIn,TimeOut
from smat_msgs where UPPER(DriverCtl) like ‘%$whatToFind%’ and
TimeIn >= $MidnightOf and TimeOut = $MidnightOfBegin and TimeOut <= $MidnightToEnd;"
}
default {
set sqlcmd "select MessageContent,DestConn,DriverCtl,UserData,SourceConn,Time,TimeIn,TimeOut
from smat_msgs where UPPER(DriverCtl) like '%$whatToFind%';"
}
}
}
if { $optd } { puts "sqlcmd == $sqlcmd" }
foreach File $SmatFiles {
if { $optd } { puts "File == $File" }
# checking for regular file.
if {[catch {open $File rb} fd]} {
puts stderr "n Cannot open $Filen"
continue
}
if {[string toupper [read $fd 6]] eq "SQLITE"} {
set encrypt 0
} else {
# Assume encrypted
set encrypt 1
}
# Close the file
close $fd
lassign "" msg DestConn DriverCtl UserData SourceConn TimeSaved
set dbName "$File"
sqlite DBCMD $dbName -readonly 1
DBCMD timeout 4000
# If suspect encrypted issue PRAGMA
if {$encrypt} {DBCMD eval "PRAGMA KEY='$site'"}
# Query for tables get names to see if SMAT DB
if {[catch { DBCMD eval "SELECT name FROM sqlite_master WHERE ENGINE='table'"} rslt]} {
DBCMD close
puts stderr "n Cannot query $File as SMAT DB – $rsltn"
if {$encrypt} {
puts stderr "Make sure site name is correct for this SMAT DB!n"
}
continue
}
if {![regexp -nocase — {smat_msgs} $rslt]} {
DBCMD close
puts stderr "n $File is sqlite but does not seem to be a SMAT DB file!n"
continue
}
catch {unset x}
DBCMD eval $sqlcmd x {
set msg $x(MessageContent)
set DestConn $x(DestConn)
set DriverCtl $x(DriverCtl)
set UserData $x(UserData)
set SourceConn $x(SourceConn)
set TimeSaved $x(Time)
set TimeIn $x(TimeIn)
set TimeOut $x(TimeOut)
if { [string equal $msg ""] } {
continue
}
set milli [string range $TimeSaved 10 end]
set TimeSaved [string range $TimeSaved 0 9]
if { $optw } { puts $ofileid "$msg" }
incr OutCnt
regsub -all "n" $msg "r" msg
regsub -all "rr" $msg "r" msg
if { ! $optx && ! $optt } {
puts "Found in file: $File"
set FirstThree [string range $msg 0 2]
if { [string equal $FirstThree "MSH"] } {
set segments [split $msg r]
foreach dispSeg $segments {
puts $dispSeg
}
} else {
puts "[pretty-print-xml $msg]"
}
if { $opth } {
puts "tDriver Control Info: $DriverCtl"
if { ! [string equal $SourceConn ""] } { puts "tSource Interface: $SourceConn" }
if { ! [string equal $UserData ""] } { puts "tMsg User Data: $UserData" }
}
puts "ntDate/Time saved: [clock format $TimeSaved -format "%D %T.$milli"]"
puts ""
flush stdout
}
}
DBCMD close
}
.
