So this little piece came in handy to pull and archive away chat logs on the client’s local machine. We barely just moved to Version 11.9 and now it seems to have broken/encrypted the database (Finally) But for anyone else still running an older version…

Imports System.Text.RegularExpressions

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim strDB As String = "Data Source=" & "C:\Users\" & Environment.UserName & "\AppData\Local\Cisco\Unified Communications\Jabber\CSF\History\" & Environment.UserName & "@[DOMAIN].db" & ";pooling=false"
        Using Sqlight As SQLite.SQLiteConnection = New SQLite.SQLiteConnection(strDB)
            Using Sqlcmd As SQLite.SQLiteCommand = Sqlight.CreateCommand()
                Sqlcmd.CommandText = "Select * from history_message order by date desc"
                Sqlight.Open()
                Try
                    Using myReader As SQLite.SQLiteDataReader = Sqlcmd.ExecuteReader()
                        While myReader.Read
                            Dim pattern As String = "(?:<div>)(.+)<\/div>"
                            Dim R As Regex = New Regex(pattern, RegexOptions.IgnoreCase)
                            Dim m As Match = R.Match(myReader.GetString(2))
                            Dim Epoch As Integer = myReader.GetInt32(3)
                            'Debug.WriteLine(myReader.GetString(4) & " - " & FromUnix(Epoch, False) & ":" & m.Groups(1).ToString)
                            Debug.WriteLine(myReader.GetString(4) & " - " & m.Groups(1).ToString)
                        End While
                    End Using
                Catch ex As Exception
                    Debug.WriteLine(ex.Message)
                End Try
            End Using
        End Using
        End
    End Sub
    Public ReadOnly Property Epoch() As DateTime
        Get
            Return New DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
        End Get
    End Property

    Public Function FromUnix(ByVal seconds As Integer, local As Boolean) As DateTime
        Dim dt = Epoch.AddSeconds(seconds)
        If local Then dt = dt.ToLocalTime
        Return dt
    End Function

    Public Function ToUnix(ByVal dt As DateTime) As Integer
        If dt.Kind = DateTimeKind.Local Then dt = dt.ToUniversalTime
        Return CInt((dt - Epoch).TotalSeconds)
    End Function

End Class

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax