Jump to content
Sign in to follow this  
danrche

SQL Lite DB file locked

Recommended Posts

danrche

Hello all,

I've created a "Log parser" for Trend Micro 7 and have come up with an error where the database is locked. Any help or recommendations?

History:

We manage a large amount of customers with Trend Micro 7 installed for Antivirus. The're is a Management console available but there are too many customers to check each one. We'd rather have the agents create tickets directly to our ticketing system.

Solution in place:

1. created a db parser using the sqllite udf.

2. it writes the results to the event logs (which we then pull into our ticketing system)

3. set exclusions for all the Trend Micro stuff to allow the LogParser to run unaffected by Trend

Problem:

1. the parser works like a champ, I have it running and working on several sites and machines with no trouble, HOWEVER; several machines (including a whole site) continues to report back "Database Locked".

I'm posting my code below, please feel free to comment, criticize, etc....

#include <SQLite.au3>
#include <SQLite.dll.au3>
#comments-start
Action_Result_for_UI   = the action taken by SA 
1. Cleaned
2. Deleted
3. Quarantined
4. Access denied
5. Passed
6. Action required

Scan_Type
1. Real-time scan
2. manual scan
3. scheduled scan
4. scan now
#comments-end
Local $hQuery, $aRow, $aNames, $data, $tag
_SQLite_Startup ()
;ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open ("C:\Program Files\Trend Micro\UniClient\plugins\EventLog.db") ; open :memory: Database
_SQlite_Query (-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $hQuery)
If @error Then
    RunWait("eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D " & '"' & "Database file is locked" & '"' ,"", @SW_HIDE)
    Exit
EndIf
;_SQlite_Query (-1, "Select * From LOG_WOFIE_spyware_L2;", $hQuery)
;_SQLite_Query (-1, "Select * From Log_WOFIE_WTP_MAIN;", $hQuery)
_SQLite_FetchNames ($hQuery, $aNames) ; Read out Column Names
    ;ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s %-10s", $aNames[11], $aNames[13], $aNames[9], $aNames[8]) & @CRLF)
    $label = StringFormat(" %-10s  %-10s  %-10s %-10s", $aNames[11], $aNames[13], $aNames[9], $aNames[8])
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
    ;ConsoleWrite(StringFormat(" %-10s  %-10s  %-10s %-10s", $aRow[11], $aRow[13], $aRow[9], _GetDateFromUnix($aRow[8])) & @CRLF)
    $date = @MON & "/" & @MDAY & "/" & @YEAR
    If Int(_GetDateFromUnix($aRow[8])) = $date Then
        _eventlog_Create(StringFormat(" %-10s  %-10s  %-10s %-10s", $aRow[11], $aRow[13], $aRow[9], _GetDateFromUnix($aRow[8])))
    EndIf
WEnd
_SQLite_Close ()
_SQLite_Shutdown ()

Func _eventlog_Create($data)
    $tag = ""
    Select
        Case StringInStr($data, "1  ", 0, 1)
            $tag = "Virus Cleaned"
            $Eid = 1
        Case StringInStr($data, "2  ", 0, 1)
            $tag = "Virus Deleted"
            $Eid = 2
        Case StringInStr($data, "3  ", 0,1)
            $tag = "Virus Quarantined"
            $Eid = 3
        Case StringInStr($data, "4  ", 0,1)
            $tag = "Virus Access Denied, couldn't clean the virus"
            $Eid = 4
        Case StringInStr($data, "5  ", 0,1)
            $tag = "Virus Passed"
            $Eid = 5
        Case StringInStr($data, "6  ", 0,1)
            $tag = "Action Required, Virus left uncleaned"
            $Eid = 6
    EndSelect
        RunWait("eventcreate /T Warning /ID " & $Eid & " /L ManagedIRON /SO TrendMicro /D " & '"' & $tag & @CRLF & $label & @CRLF & $data & '"' ,"", @SW_HIDE)  
EndFunc

Func _GetDateFromUnix ($nPosix)
   Local $nYear = 1970, $nMon = 1, $nDay = 1, $nHour = 00, $nMin = 00, $nSec = 00, $aNumDays = StringSplit ("31,28,31,30,31,30,31,31,30,31,30,31", ",")
   While 1
      If (Mod ($nYear + 1, 400) = 0) Or (Mod ($nYear + 1, 4) = 0 And Mod ($nYear + 1, 100) <> 0) Then; is leap year
         If $nPosix < 31536000 + 86400 Then ExitLoop
         $nPosix -= 31536000 + 86400
         $nYear += 1
      Else
         If $nPosix < 31536000 Then ExitLoop
         $nPosix -= 31536000
         $nYear += 1
      EndIf
   WEnd
   While $nPosix > 86400
      $nPosix -= 86400
      $nDay += 1
   WEnd
   While $nPosix > 3600
      $nPosix -= 3600
      $nHour += 1
   WEnd
   While $nPosix > 60
      $nPosix -= 60
      $nMin += 1
   WEnd
   $nSec = $nPosix
   For $i = 1 to 12
      If $nDay < $aNumDays[$i] Then ExitLoop
      $nDay -= $aNumDays[$i]
      $nMon += 1
   Next
   Return $nMon & "/" & $nDay & "/" & $nYear & " " & $nHour & ":" & $nMin & ":" & $nSec
EndFunc; ==> _GetDateFromUnix

Share this post


Link to post
Share on other sites
PsaltyDS

You seem to be making a huge assumption that just because you got @error <> 0, the database was locked. Try a more informative error report:

$iRET = _SQLite_Query(-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $hQuery)
If $iRET <> $SQLITE_OK Then
    $iErrSav = @error
    $iExtSav = @extended
    RunWait('eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D "' & _
            'Database query failed: $iRET = ' & $iRET & '; $@error = ' & $iErrSav & '; @extended = ' & $iExtSav & '"', "", @SW_HIDE)
    Exit
EndIf

You may find it has nothing to do with record locking.

:mellow:

P.S. There is a locking method available that could be checked as part of your query process. Look for jchd's posts on the topic as an example.

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
danrche

You seem to be making a huge assumption that just because you got @error <> 0, the database was locked. Try a more informative error report:

$iRET = _SQLite_Query(-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $hQuery)
If $iRET <> $SQLITE_OK Then
    $iErrSav = @error
    $iExtSav = @extended
    RunWait('eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D "' & _
            'Database query failed: $iRET = ' & $iRET & '; $@error = ' & $iErrSav & '; @extended = ' & $iExtSav & '"', "", @SW_HIDE)
    Exit
EndIf

You may find it has nothing to do with record locking.

:mellow:

huh, I didn't think of that, thanks. I'm still learning about error handling and "throwing Exceptions" and such. I'll modify this and see what I get, thanks for the quick response.

Share this post


Link to post
Share on other sites
danrche

You seem to be making a huge assumption that just because you got @error <> 0, the database was locked. Try a more informative error report:

$iRET = _SQLite_Query(-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $hQuery)
If $iRET <> $SQLITE_OK Then
    $iErrSav = @error
    $iExtSav = @extended
    RunWait('eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D "' & _
            'Database query failed: $iRET = ' & $iRET & '; $@error = ' & $iErrSav & '; @extended = ' & $iExtSav & '"', "", @SW_HIDE)
    Exit
EndIf

You may find it has nothing to do with record locking.

:mellow:

P.S. There is a locking method available that could be checked as part of your query process. Look for jchd's posts on the topic as an example.

gave it a go, the result is $iRET = 5 (the database file is locked). now I have to figure out why the db is always locked on those computers. Thanks for the quick error reporting code. it's good to see methods on dealing with errors, I didn't even think to search in the sqlite udf for those options/variables

Share this post


Link to post
Share on other sites
jchd

Yes error checking is necessary.

What I don't get is what is your setup: which process is writing to the DB? Is the DB shared over a network? Can you use the (recent) WAL mode to avoid blocking readers while one writer works?

Why don't you use _SQLite_GetTable2d in place of your individual calls? You could see some performance improvement and code simplification.

As a sidenote, you probably should use an explicit list of columns instead of * in your select. Then you should also use SQLite strftime() and friends to provide you with ready to use date/time format instead of having to run your own (much slower) Autoit interpreted function.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
danrche

Yes error checking is necessary.

What I don't get is what is your setup: which process is writing to the DB? Is the DB shared over a network? Can you use the (recent) WAL mode to avoid blocking readers while one writer works?

Why don't you use _SQLite_GetTable2d in place of your individual calls? You could see some performance improvement and code simplification.

As a sidenote, you probably should use an explicit list of columns instead of * in your select. Then you should also use SQLite strftime() and friends to provide you with ready to use date/time format instead of having to run your own (much slower) Autoit interpreted function.

The database is local to each machine and is created and maintained by the Trend Micro Agent on each machine. I wrote the app posted to pull the info from it into the event logs (Kaseya is the RMM tool we use and monitors the event logs already) for ticket creation on Virus's not cleaned. I'm targeting the db file b/c the log files Trend supplies don't update until the Trend Agent is opened by the user and the logs are viewed by the user. I also found the error reporting provied (which rocks thank you) showed me on the one site that all of them had a 1 (db file is not there). I'm troubleshooting that with Trend Micro now. As for the code you suggested with the _SQLLite_GetTable2d and such, I do want it to be lite weight on the boxes and quick, but this is my 1st attempt at this and I needed something quick as we're pushing out Trend 7 to all our clients (with Trend) totaling 2600 nodes total across multiple companies. I like the suggestions you had posted and will work them into the next version of the parser for Trend. I'm not a developer by Trade (more of a sys admin) but enjoy coding and hope to become better with time.

Thank you for your help, please continue to post any suggestions as I learn a lot from going though these forums. I will work on implementing the code changes you suggested to see if I can make this process better and will post my coded results to here when done.

Again, thanks so much for your help guys, this is a LIFE SAVER!

Share this post


Link to post
Share on other sites
jchd

Give _SQLLite_GetTable2d a try to keep the code short, efficient and lightweight.

As for the timestamps, use something like:

select columnA, columnB, ..., strftime('%Y/%m/%d %H:%M:%S', columnNameOfTimestamp, 'unixepoch', 'localtime'), columnX, columnY, ...

Depending on your use case you may want to leave or drop the 'localtime' modifier. Read this page for details.

The reason behind is that invoking directly low-level highly-optimized C function(s) inside SQLite is much, much faster than coding the same in AutoIt.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
danrche

Give _SQLLite_GetTable2d a try to keep the code short, efficient and lightweight.

As for the timestamps, use something like:

select columnA, columnB, ..., strftime('%Y/%m/%d %H:%M:%S', columnNameOfTimestamp, 'unixepoch', 'localtime'), columnX, columnY, ...

Depending on your use case you may want to leave or drop the 'localtime' modifier. Read this page for details.

The reason behind is that invoking directly low-level highly-optimized C function(s) inside SQLite is much, much faster than coding the same in AutoIt.

Thanks, Will have a go at this and post my result. Thanks again for your guidance.

Share this post


Link to post
Share on other sites
jchd

I hate web when the page needs reloading and you don't have a copy of what you wrote just before...

I also was telling you that you should try using _SQLite-SetTimeout(-1, 60000) right after your _SQLite_Open to allow for a one minute time-out (for instance), expectedly ample time for the other app to release the write lock it has set. If Trend poses a write lock for an extensive duration (bad design) you can still extend the timeout value.

This function is per-connection and saves you complex looping/error checking repeatedly in your code.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
danrche

#include <SQLite.au3>
#include <SQLite.dll.au3>
#comments-start
Action_Result_for_UI   = the action taken by SA 
1. Cleaned
2. Deleted
3. Quarantined
4. Access denied
5. Passed
6. Action required

Scan_Type
1. Real-time scan
2. manual scan
3. scheduled scan
4. scan now
#comments-end
Local $hQuery, $aRow, $aNames, $data, $tag, $aResult, $iColumns, $iRow
_SQLite_Startup ()
;ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open ("C:\Program Files\Trend Micro\UniClient\plugins\EventLog.db") ; open :memory: Database


$iRET = _SQLite_GetTable2d(-1, "Select * From LOG_WOFIE_MALWARE_MAIN;", $aResult, $iRow, $iColumns)
If $iRET <> $SQLITE_OK Then
    $iErrSav = @error
    $iExtSav = @extended
    RunWait('eventcreate /T Error /ID 100 /L ManagedIRON /SO TrendMicro /D "' & _
            'Database query failed: $iRET = ' & $iRET & '; $@error = ' & $iErrSav & '; @extended = ' & $iExtSav & '"', "", @SW_HIDE)
    Exit
Else
    RunWait('eventcreate /T Warrning /ID 1 /L ManagedIRON /SO TrendMicro /D "' &  _SQLite_Display2DResult($aResult) & '"',"", @SW_HIDE)
EndIf

_SQLite_Close ()
_SQLite_Shutdown ()

Ok, so here's the code I modified to include your suggestions so far, it is much smaller and a bit quicker too.

Share this post


Link to post
Share on other sites
jchd

Fine. Just post again if you encounter issues.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×