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"
                    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
                End Try
            End Using
        End Using
    End Sub
    Public ReadOnly Property Epoch() As DateTime
            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 *