danrche Posted August 12, 2011 Posted August 12, 2011 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.... expandcollapse popup#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
PsaltyDS Posted August 12, 2011 Posted August 12, 2011 (edited) 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. 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 August 12, 2011 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
danrche Posted August 12, 2011 Author Posted August 12, 2011 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. 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.
danrche Posted August 12, 2011 Author Posted August 12, 2011 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. 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
jchd Posted August 12, 2011 Posted August 12, 2011 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 hereRegExp tutorial: enough to get startedPCRE 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)
danrche Posted August 15, 2011 Author Posted August 15, 2011 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!
jchd Posted August 15, 2011 Posted August 15, 2011 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 hereRegExp tutorial: enough to get startedPCRE 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)
danrche Posted August 16, 2011 Author Posted August 16, 2011 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.
jchd Posted August 16, 2011 Posted August 16, 2011 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 hereRegExp tutorial: enough to get startedPCRE 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)
danrche Posted August 16, 2011 Author Posted August 16, 2011 #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.
jchd Posted August 16, 2011 Posted August 16, 2011 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 hereRegExp tutorial: enough to get startedPCRE 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)
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now