slaughter Posted March 8, 2014 Share Posted March 8, 2014 (edited) BFGuard is Brute force Guard for windows server remote desktop connection developed in autoit language. BFGuard is simple app but yet very handy for people using publicly accessible windows services like Remote desktop, Microsoft SQL or SAMBA and trying to protect these services from brute force atacks. This app does not modifies any windows services and do minimal load on your system it was worked out in this way all along. It does minimal load on system even on thou sen attacks in minute or second. It acts as a part of windows, connecting windows event log and windows firewall so this app reads windows logs analyzes them and blocks attacker IP addresses over windows firewall. This app supports all software services witch adds info to windows event log so natively it supports Remote desktop connection known as RDP protocol, windows sharing known as SAMBA protocol, Microsoft SQL server and more… BFGuard is windows firewall add-on witch helps automate defense mechanism essential existing in windows. MANY THANKS FOR HELP AND SUPPORT llewxam jchd Screen shoots: Update's 2014-10-29 Full version uploaded. Enjoy. 2014-03-15 Fixed major error with auto-ban. 2014-03-12 Can type SQL query to combo box in statistic and click load Like select * from ip_list where date > date('now','-1 hour') order by date 2014-03-11 When date is read from event log it is in not SQL format. Now before importing data to SQL data is formated to correct SQL format. And now it is possible to do simple compare by date. New fast data import mode (jchd code. THANKS!) Now statistic is working and button load is active. (post yours SQL lines will add them) White list is working, but need testing Still not clear is auto-block working correct. Some bug fixes. 2014-03-10 v2 Added IP geolocation button. (llewxam code. Thanks!) 2014-03-10 Reworked GUI from scratch. Any new ideas would be good. Script exploded from single function script to more separate functions to make it easer to read and update for community Added new table where you can see blocked IP and date's some buttons is only for feature like white list, auto ban 2014-03-09 Added buttons block, unblock ( It does what is said, blocks selected IP from accessing your server RDP, FTP,SQL, SAMBA, ECT) Automatically reads log and adds to list every 60 sec. Initial run of programs reads 10k events from log. Second time when you run program reads 24hours log to make it more quicker. Some bugs.... and more ect... PLANS: Automatic firewall rule management ( adding, removing after time pass) BAD ASS IP sharing over web (making global black list) Know bugs: When inserting data to SQLite it hangs up a bit, so wait for it. If not happy you can make queries to go every time it pharases a event, but it takes longer. Now it inserts everything in one batch. To do list: AdlibUnregister and AblibRegister shuld be disabled on initial databse read. (thanks to llewxam) Remove old data import code from script if no bugs detected in some time Code update coming soon. (FREE FOR NON COMMERCIAL USE) (ANY OTHER USE IS PROHIBITED OR TRY CONTACT ME) expandcollapse popup#RequireAdmin #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=ico\Icojam-Blue-Bits-Shield.ico #AutoIt3Wrapper_Res_Fileversion=0.0.0.3 #AutoIt3Wrapper_Res_Fileversion_AutoIncrement=y #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <GUIConstantsEx.au3> #include <Constants.au3> #include <Array.au3> #include <File.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <GuiListView.au3> #include <GuiImageList.au3> #include <WindowsConstants.au3> #include <ButtonConstants.au3> #include <ComboConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <ListViewConstants.au3> #include <ProgressConstants.au3> #include <StaticConstants.au3> #include <TabConstants.au3> #include <WindowsConstants.au3> #include <Date.au3> #include <Crypt.au3> Global $iMemo, $mac_list, $license_key Global $Input1, $Checkbox1, $atempts, $minutes, $Combo1 _Main() Func _Main() Local $hEventLog, $aEvent Global $SQLite_db,$hListView,$hListView2,$GUI,$wevtutil_query, $initial_event_count, $Label1, $Progress1, $sql_qry, $minutes_in_ban_list, $display_results Global $Input1, $Checkbox1, $atempts, $minutes, $Combo1, $whiteipinput, $Checkbox2 $GUI = GUICreate("BFGurad - version 1.37", 621, 450, -1, -1, BitOR($GUI_SS_DEFAULT_GUI,$WS_MAXIMIZEBOX,$WS_SIZEBOX,$WS_THICKFRAME,$WS_TABSTOP)) $Tab1 = GUICtrlCreateTab(8, 8, 601, 417) GUICtrlSetResizing(-1, $GUI_DOCKLEFT+$GUI_DOCKRIGHT+$GUI_DOCKTOP+$GUI_DOCKBOTTOM+$GUI_DOCKWIDTH+$GUI_DOCKHEIGHT) $TabSheet1 = GUICtrlCreateTabItem("Log entrys") $iMemo = GUICtrlCreateEdit("", 16, 88, 585, 329) $Progress1 = GUICtrlCreateProgress(16, 36, 582, 17) $Label1 = GUICtrlCreateLabel("Reading event log", 16, 61, 584, 17) $TabSheet2 = GUICtrlCreateTabItem("Blocked IP") $hListView2 = GUICtrlCreateListView("", 12, 41, 586, 350) _GUICtrlListView_SetExtendedListViewStyle($hListView2, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES)) _GUICtrlListView_InsertColumn($hListView2, 0, "IP Address", 150) _GUICtrlListView_InsertColumn($hListView2, 1, "From", 130) _GUICtrlListView_InsertColumn($hListView2, 2, "To", 130) _GUICtrlListView_InsertColumn($hListView2, 3, "Status", 100) $Button1 = GUICtrlCreateButton("Unblock", 12, 393, 50, 25) $Button1_geolocate = GUICtrlCreateButton("Who?", 64, 393, 50, 25) $Checkbox1 = GUICtrlCreateCheckbox("Auto block", 200, 392, 73, 15) $Checkbox2 = GUICtrlCreateCheckbox("Auto unblock", 200, 406, 73, 15) $atempts = GUICtrlCreateInput("5", 284, 393, 25, 21) $minutes = GUICtrlCreateInput("1", 380, 393, 41, 21) GUICtrlCreateLabel("attempts in", 316, 393, 55, 17, $SS_CENTERIMAGE) GUICtrlCreateLabel("min. For", 428, 393, 41, 17, $SS_CENTERIMAGE) $Button2 = GUICtrlCreateButton("White list IP", 116, 393, 75, 25) $Input1 = GUICtrlCreateInput("60", 476, 393, 41, 21) $Label3 = GUICtrlCreateLabel("minutes.", 524, 393, 31, 17, $SS_CENTERIMAGE) $TabSheet3 = GUICtrlCreateTabItem("WhiteList") $hListView3 = GUICtrlCreateListView("", 12, 41, 586, 350) _GUICtrlListView_SetExtendedListViewStyle($hListView3, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES)) _GUICtrlListView_InsertColumn($hListView3, 0, "IP Address", 150) $whiteipinput = GUICtrlCreateInput("", 12, 393, 120, 21) $Button7 = GUICtrlCreateButton("Add", 135, 393, 50, 21) $Button8 = GUICtrlCreateButton("Remove", 188, 393, 50, 21) $TabSheet4 = GUICtrlCreateTabItem("Statistics") $Combo1 = GUICtrlCreateCombo("Total failed login count", 12, 41, 505, 25, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL)) $Button3 = GUICtrlCreateButton("Block", 12, 393, 75, 25) $Button4 = GUICtrlCreateButton("UnBlock", 92, 393, 75, 25) $Button6 = GUICtrlCreateButton("Who?", 92+80, 393, 75, 25) $hListView = GUICtrlCreateListView("", 12, 65, 586, 326) _GUICtrlListView_SetExtendedListViewStyle($hListView, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES)) _GUICtrlListView_InsertColumn($hListView, 0, "IP Address", 150) _GUICtrlListView_InsertColumn($hListView, 1, "Count", 100) _GUICtrlListView_InsertColumn($hListView, 2, "Blocked", 100) _GUICtrlListView_InsertColumn($hListView, 3, "Date", 150) $Button5 = GUICtrlCreateButton("Load", 520, 41, 79, 21) GUICtrlCreateTabItem("") GUISetState(@SW_SHOW) $MenuItem1 = GUICtrlCreateMenu("Settings") ;~ $MenuItem5 = GUICtrlCreateMenuItem("Restet database", $MenuItem1) $MenuItem3 = GUICtrlCreateMenuItem("Enter license key", $MenuItem1) ;~ $MenuItem4 = GUICtrlCreateMenuItem("Exit", $MenuItem1) $MenuItem2 = GUICtrlCreateMenu("Abuot") ;~ $MenuItem7 = GUICtrlCreateMenuItem("IPCloudSynch", $MenuItem2) $MenuItem6 = GUICtrlCreateMenuItem("Version", $MenuItem2) #EndRegion ### END Koda GUI section ### GUICtrlSetFont($iMemo, 9, 400, 0, "Courier New") GUISetState() $initial_event_count = 20000 $update_event_count = 10000 $minutes_in_ban_list = GUICtrlRead($Input1) $display_results = "SELECT ip, count(*) as 'count', status, date FROM ip_list GROUP BY ip ORDER by count DESC;" Global $SQL_statistic_queries[17][2] = [ _ ["Show whitelist", "SELECT * FROM white_ip_list"], _ ["Last minute activity", "SELECT ip, date, status FROM ip_list WHERE date >= datetime('now','-1 minute') ORDER by date DESC"], _ ["Last hour activity", "SELECT ip, date, status FROM ip_list WHERE date >= datetime('now','-1 hour') ORDER by date DESC"], _ ["Last 100 records", "SELECT ip,* FROM ip_list ORDER by date DESC LIMIT 100"], _ ["Failed logins in 1 min", "SELECT ip, count(*) as 'count', status FROM ip_list WHERE date >= datetime('now','-1 minutes') GROUP BY ip ORDER by count DESC"], _ ["Failed logins in 1 hour", "SELECT ip, count(*) as 'count', status FROM ip_list WHERE date >= datetime('now','-1 hour') GROUP BY ip ORDER by count DESC"], _ ["Failed logins in 1 day", "SELECT ip, count(*) as 'count', status FROM ip_list WHERE date >= datetime('now','-1 day') GROUP BY ip ORDER by count DESC"], _ ["Failed logins in 1 month", "SELECT ip, count(*) as 'count', status FROM ip_list WHERE date >= datetime('now','-1 month') GROUP BY ip ORDER by count DESC"], _ ["Failed logins all time", "SELECT ip, count(*) as 'count', status FROM ip_list GROUP BY ip ORDER by count DESC"], _ ["Failed user in 1 day", "SELECT user, count(*) as 'count' FROM ip_list WHERE date >= datetime('now','-1 day') GROUP BY user ORDER by count DESC"], _ ["Failed user in 1 month", "SELECT user, count(*) as 'count' FROM ip_list WHERE date >= datetime('now','-1 month') GROUP BY user ORDER by count DESC"], _ ["Failed user all time", "SELECT user, count(*) as 'count' FROM ip_list GROUP BY user ORDER by count DESC"], _ ["Show Table ip_list", "SELECT * FROM ip_list LIMIT 1000"], _ ["Show Table blocked_ip_list", "SELECT * FROM blocked_ip_list"], _ ["Show Table white_ip_list", "SELECT * FROM white_ip_list"], _ ["", ""]] $string = '' FOR $cx=0 to UBound($SQL_statistic_queries)-1 $string = $string & "|" & $SQL_statistic_queries[$cx][0] Next $string = $string GUICtrlSetData($Combo1, $string) _SQLite_Startup() MemoWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) GUICtrlSetData($Progress1, 1) $db_name = @ScriptDir & "\SQLite_v5.db" If FileExists($db_name) Then $wevtutil_query = "*[System[band(Keywords,4503599627370496)]]" ;after initail run dont read all log only 24 h MemoWrite("Opening existing database. "& $db_name) $SQLite_db = _SQLite_Open($db_name) $initial_event_count = $update_event_count Else $wevtutil_query = "*[System[band(Keywords,8010000000000000)]]" MemoWrite("Creating new database at: "& $db_name) $SQLite_db = _SQLite_Open($db_name) MemoWrite("Creating database structure.") _SQLite_Exec($SQLite_db, "CREATE TABLE ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT,log_id CHAR(150) UNIQUE, date CHAR(150), ip CHAR(150), user CHAR(150), status CHAR(150))") _SQLite_Exec($SQLite_db, "CREATE TABLE blocked_ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT,ip CHAR(150), date_from DATETIME, date_to DATETIME, status CHAR(150))") _SQLite_Exec($SQLite_db, "CREATE TABLE white_ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT,ip CHAR(150) UNIQUE)") EndIf _Seciurity_check() Update_database() AdlibRegister(sheduled_tasks, 1000*30) display_resutls($display_results, $hListView ) display_resutls("SELECT * FROM white_ip_list", $hListView3) display_blocked_resutls("SELECT ip, date_from, date_to, status FROM blocked_ip_list ORDER by status") ; Loop until user exits While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit Case $Button2 $iSelect = ControlListView($GUI, "", $hListView2, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView2, "GetText", $iSelect) MemoWrite("Address: "&$sSelect& " is added to white list") SQLite_QUERY("INSERT INTO white_ip_list VALUES (null, '"& $sSelect &"');") Case $Button1 $iSelect = ControlListView($GUI, "", $hListView2, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView2, "GetText", $iSelect) MemoWrite("Address: "&$sSelect& " is removed from firewal") block_ip_remove($sSelect) Case $Button3 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) MemoWrite("Address: "&$sSelect& " is added to firewal for blocking") block_ip_add($sSelect,'525600') ; block select ip for a year 365*24*60 Case $Button4 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) MemoWrite("Address: "&$sSelect& " is removed from firewal") block_ip_remove($sSelect) Case $Button5 $sComboRead = GUICtrlRead($Combo1) Local $iIndex = _ArraySearch($SQL_statistic_queries, $sComboRead, 0, 0, 0, 1, 1, 0) If @error Then $display_results = $sComboRead display_resutls($sComboRead, $hListView) Else Global $display_results = $SQL_statistic_queries[$iIndex][1] MemoWrite($SQL_statistic_queries[$iIndex][1]) display_resutls($SQL_statistic_queries[$iIndex][1], $hListView) EndIf Case $Button6 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) _GeoLocate($sSelect) MemoWrite("Geolocated " & $sSelect) Case $Button1_geolocate $iSelect = ControlListView($GUI, "", $hListView2, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView2, "GetText", $iSelect) _GeoLocate($sSelect) MemoWrite("Geolocated " & $sSelect) Case $Button7 $sSelect = GUICtrlRead($whiteipinput) SQLite_QUERY("INSERT INTO white_ip_list VALUES (null, '"& $sSelect &"');") display_resutls("SELECT * FROM white_ip_list", $hListView3) Case $Button8 $iSelect = ControlListView($GUI, "", $hListView3, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView3, "GetText", $iSelect) SQLite_QUERY("DELETE FROM white_ip_list WHERE id='"& $sSelect &"';") display_resutls("SELECT * FROM white_ip_list", $hListView3) Case $MenuItem3 $license_input = InputBox("BFGuard","Enter license key") If @error OR $license_input="" Then MsgBox($MB_OK,"BFGuard","License was not changed") Else RegDelete("HKEY_LOCAL_MACHINE\Software\BFGuard", "License") RegWrite("HKEY_LOCAL_MACHINE\Software\BFGuard", "License", "REG_SZ", $license_input) _Seciurity_check() EndIf Case $MenuItem6 MsgBox($MB_OK,"BFGuard","BFGuard 1.0") _Seciurity_check() EndSwitch WEnd EndFunc ;==>_Main Func sheduled_tasks() Global $Checkbox1,$display_results, $hListView, $hListView3 Update_database() if _IsChecked($Checkbox1) Then $attempts_time=GUICtrlRead($minutes) $attempts_count=GUICtrlRead($atempts) if _Seciurity_check() == True Then auto_ban("SELECT ip, count(ip) as total FROM ip_list WHERE date >= datetime('now','-"&$attempts_time&" minutes') GROUP BY IP HAVING total >= "&$attempts_count&" AND status IS NOT 'Blocked' ") EndIf if _IsChecked($Checkbox2) Then auto_ban_remove("SELECT ip FROM blocked_ip_list WHERE date_to <= datetime('now') AND status='Blocked'") EndIf display_resutls($display_results, $hListView) display_resutls("SELECT * FROM white_ip_list", $hListView3) display_blocked_resutls("SELECT ip, date_from, date_to, status FROM blocked_ip_list ORDER by status") EndFunc Func block_ip_add($WhatIP, $minutes_in_ban_list = 60) Global $SQLite_db Local $whiteip _ADD_BlackListIp($WhatIP) ;_SQLite_Query($SQLite_db, "SELECT ip FROM white_ip_list LIMIT 1;", $hQuery) ; the query _SQLite_QuerySingleRow($SQLite_db, "SELECT ip FROM white_ip_list WHERE ip = '"&$WhatIP&"' LIMIT 1;", $whiteip) if $whiteip[0] = $WhatIP Then MemoWrite("Cant block IP it is in white list") Else RunWait(@ComSpec & ' /c '& "netsh advfirewall firewall add rule name=[logBlock" & $WhatIP & "] dir=in interface=any action=block remoteip=" & $WhatIP, "", @SW_HIDE) ;blocked_ip_list (ID ,ip, date_from, date_to, active) $minutes_in_ban_list = GUICtrlRead($Input1) SQLite_QUERY("INSERT INTO blocked_ip_list VALUES (null, '"& $WhatIP &"', datetime('now','localtime'), datetime('now','localtime', '+"& $minutes_in_ban_list &" minutes'), 'Blocked');") SQLite_QUERY("UPDATE ip_list SET status='Blocked' WHERE ip='"& $WhatIP &"';") EndIf EndFunc Func block_ip_remove($WhatIP) RunWait(@ComSpec & ' /c '& "netsh advfirewall firewall delete rule name=[logBlock" & $WhatIP & "] dir=in", "", @SW_HIDE) SQLite_QUERY("UPDATE blocked_ip_list SET status='Unblocked' WHERE ip='"& $WhatIP &"' AND status='Blocked';") SQLite_QUERY("UPDATE ip_list SET status='Unblocked' WHERE ip='"& $WhatIP &"' AND status='Blocked';") EndFunc Func Update_database() Global $wevtutil_query, $initial_event_count, $Label1 GUICtrlSetData($Label1, "Reading event log from windows") ;MemoWrite("Generating event log from windows") ;$cmd = 'wevtutil qe Security "/q:'& $wevtutil_query &'" /c:'& $initial_event_count &' /rd:true /f:xml>C:\wevtutil.xml' $cmd = 'wevtutil qe Security /q:' & Chr(34) & $wevtutil_query & Chr(34) & ' /c:' & $initial_event_count & ' /rd:true /f:xml>C:\wevtutil.xml' ;MemoWrite($cmd) FileDelete ( "c:\wevtutil.xml" ) Local $foo = RunWait(@ComSpec & ' /c '& $cmd, @SystemDir, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) GUICtrlSetData($Label1, "Reading event log done.") ;MemoWrite("Generating event log done.") Global $Label1, $Progress1, $sql_qry ; MemoWrite("NEW MODE DATA UPDATE") ; MemoWrite("Reading event log.") Local $rawdata = FileRead("c:\wevtutil.xml") ; ##### reading supplied file directly ! ; MemoWrite("Reading event log done.") ;MemoWrite("Phrasing event log.") GUICtrlSetData($Progress1, 30) Local $captures = StringRegExp($rawdata, "(?i)<TimeCreated SystemTime='(.*?)'/><EventRecordID>(.*?)</EventRecordID>.*?<Data Name='TargetUserName'>(.*?)</Data>.*?<Data Name='IpAddress'>(.*?)</Data>", 3) ;MemoWrite("Phrasing event log done.") ;MemoWrite("Inserting data to SQLite.") ;_ArrayDisplay($captures) If IsArray($captures) Then Local $sql If Not _SQLite_Exec($SQLite_db, "begin;") = $SQLITE_OK Then ;MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg(),5) MemoWrite("SQLite Error: "& _SQLite_ErrMsg()) MemoWrite("SQLite QUERY: "& "begin;") EndIf For $i = 0 To (UBound($captures) / 4) - 1 $reformated_time = StringSplit($captures[4 * $i], "T") $reformated_time = $reformated_time[1] & " " & StringLeft($reformated_time[2],8) $sql &= "('" & $reformated_time & "', '" & $captures[4 * $i + 1] & "', '" & $captures[4 * $i + 2] & "', '" & $captures[4 * $i + 3] & "')," If Mod($i + 1, 500) = 0 Or $i = (UBound($captures) / 4) - 1 Then $sql = "INSERT OR IGNORE INTO ip_list (date, log_id, user, ip) VALUES " & StringTrimRight($sql, 1) If Not _SQLite_Exec($SQLite_db, $sql) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg(), 5) MemoWrite("SQLite Error: "& _SQLite_ErrMsg()) MemoWrite("SQLite QUERY: "& $sql) EndIf $sql = "" EndIf Next GUICtrlSetData($Progress1, 70) If Not _SQLite_Exec($SQLite_db, "commit;") = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg(),5) MemoWrite("SQLite Error: "& _SQLite_ErrMsg()) MemoWrite("SQLite QUERY: "& "commit;") EndIf Else ;MemoWrite("No new event to read.") EndIf ; MemoWrite("Inserting data to SQLite done.") GUICtrlSetData($Progress1, 100) EndFunc Func display_resutls($sql, $hListView) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval Global $SQLite_db $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then Do ; delte list colums _GUICtrlListView_DeleteColumn($hListView, 0) Until _GUICtrlListView_GetColumnCount($hListView) = 0 ;Create list colums dpending on query Local $hQuery, $aNames _SQLite_Query($SQLite_db, $sql&"", $hQuery) _SQLite_FetchNames($hQuery, $aNames) $count = UBound($aNames)-1 For $rx=0 to $count $lenght = 100 if $aNames[$rx] = "ip" Then $lenght = 150 if $aNames[$rx] = "date" Then $lenght = 130 _GUICtrlListView_InsertColumn($hListView, $rx, $aNames[$rx], $lenght) Next _ArrayDelete($aResult, 0) _GUICtrlListView_DeleteAllItems($hListView) _GUICtrlListView_AddArray($hListView, $aResult) ;_ArrayDisplay($aResult, "Query Result") ;~ $iRows = UBound($aResult)-1 ;~ $iCols = UBound($aResult, 2)-1 ;~ For $i = 1 To $iRows ;~ $string = "" ;~ For $x = 0 To $iCols ;~ $string = $string & $aResult[$i][$x] & " " ;~ Next ;~ MemoWrite($string) ;~ Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg(),5) MemoWrite("SQLite Error: "& _SQLite_ErrMsg()) MemoWrite("SQLite QUERY: "& $sql) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc Func display_blocked_resutls($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval Global $SQLite_db, $hListView2 $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDelete($aResult, 0) _GUICtrlListView_DeleteAllItems($hListView2) _GUICtrlListView_AddArray($hListView2, $aResult) ;_ArrayDisplay($aResult, "Query Result") ;~ $iRows = UBound($aResult)-1 ;~ $iCols = UBound($aResult, 2)-1 ;~ For $i = 1 To $iRows ;~ $string = "" ;~ For $x = 0 To $iCols ;~ $string = $string & $aResult[$i][$x] & " " ;~ Next ;~ MemoWrite($string) ;~ Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg(),5) MemoWrite("SQLite Error: "& _SQLite_ErrMsg()) MemoWrite("SQLite QUERY: "& $sql) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc Func auto_ban($sql) if _Seciurity_check() = True Then ;~ MemoWrite("true.") Local $aResult, $iRows, $iColumns, $iRval, $minutes_in_ban_list Global $SQLite_db, $hListView $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) ;~ MemoWrite($sql) If $iRval = $SQLITE_OK Then ;_ArrayDelete($aResult, 0) ;_GUICtrlListView_DeleteAllItems($hListView) ;_GUICtrlListView_AddArray($hListView, $aResult) ;_ArrayDisplay($aResult, "Query Result") $iRows = UBound($aResult)-1 For $i = 1 To $iRows if _IsChecked($Checkbox2) Then $minutes_in_ban_list = GUICtrlRead($Input1) Else $minutes_in_ban_list = 548640000 ; ban for 10 years EndIf block_ip_add($aResult[$i][0], $minutes_in_ban_list) MemoWrite("Auto blocking IP: " &$aResult[$i][0]& " for: "& $minutes_in_ban_list & " minutes.") Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & $sql,5) MemoWrite("SQLite Error: "& _SQLite_ErrMsg()) MemoWrite("SQLite QUERY: "& $sql) EndIf Else ;~ MemoWrite("false.") EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc Func auto_ban_remove($sql) MemoWrite("Autoban remove: "&$sql) Local $aResult, $iRows, $iColumns, $iRval, $minutes_in_ban_list Global $SQLite_db, $hListView $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ;_ArrayDelete($aResult, 0) ;_GUICtrlListView_DeleteAllItems($hListView) ;_GUICtrlListView_AddArray($hListView, $aResult) ;_ArrayDisplay($aResult, "Query Result") $iRows = UBound($aResult)-1 For $i = 1 To $iRows block_ip_remove($aResult[$i][0]) ;blocked_ip_list (ID ,ip, date_from, date_to, active) MemoWrite("Auto blocking IP: " &$aResult[$i][0]& " REMOVED") Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()& @CRLF & $sql,5) MemoWrite("SQLite Error: "& _SQLite_ErrMsg()) MemoWrite("SQLite QUERY: "& $sql) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc Func SQLite_QUERY($sql_qry) Global $SQLite_db If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then MemoWrite("SQLite Error: "&_SQLite_ErrMsg() & " QUERY: "& $sql_qry) MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg() & @CRLF & $sql_qry, 5) EndIf EndFunc Func _IsChecked($iControlID) Return BitAND(GUICtrlRead($iControlID), $GUI_CHECKED) = $GUI_CHECKED EndFunc ;==>_IsChecked ; Write a line to the memo control Func MemoWrite($sMessage) $time = @YEAR & "-"& @MON & "-"& @MDAY & " "& @HOUR & ":"& @MIN & ":"& @SEC GUICtrlSetData($iMemo, $time &" -> "& $sMessage & @CRLF, 1) FileWriteLine("log.txt", $sMessage & @CRLF) EndFunc ;==>MemoWrite Func _GeoLocate($ip) ;by llewxam $url = "http://freegeoip.net/csv/"&$ip $WEBdata = BinaryToString(InetRead($url,1)) $geo_data = StringSplit($WEBdata, ',') ;~ "184.22.136.136","US","United States","PA","Pennsylvania","Scranton","18501","41.4090","-75.6624","577","570" ;~ ShellExecute("http://www.infosniper.net/index.php?ip_address=" & $ip & "&map_source=1&overview_map=1&lang=1&map_type=1&zoom_level=7") if IsArray($geo_data) And UBound($geo_data) > 3 Then ;_ArrayDisplay($geo_data, "GEO Loaction") MsgBox($MB_OK, "BFGuard", "IP: "&clean_duble_quotes($geo_data[1])&@CRLF &"Country: "&clean_duble_quotes($geo_data[3])&@CRLF&"State: "&clean_duble_quotes($geo_data[5])&@CRLF&"City: "&clean_duble_quotes($geo_data[6])&@CRLF) Else MsgBox($MB_OK, "BFGuard", "Cant get any data for this ip", 5) EndIf EndFunc ;==>_GeoLocate Func clean_duble_quotes($str) $str = StringTrimRight(StringTrimLeft($str,1),1) Return($str) EndFunc Func _ADD_BlackListIp($ip) EndFunc Func _Seciurity_check() Return True EndFunc Func encryptted($data) $key = "756874587855478554482011"; // 32 byte key --> AES-256 $IV = '8979879879875464654654' _Crypt_Startup() $key = _CryptImportKey($CALG_AES_256, $key, 32) Const $KP_IV = 1 _CryptSetKeyParam($key, $KP_IV, $IV, 0, "str") ; So könnte man den Modus auf ECB stellen, dann wird der IV nicht verwendet, ; die Verschlüsselung ist schlechter. Const $KP_MODE = 4 Const $CRYPT_MODE_ECB = 2 ;~ _CryptSetKeyParam($key, $KP_MODE, $CRYPT_MODE_ECB, 0, "dword*") $crypt = _Crypt_EncryptData($data, $key, $CALG_USERKEY) $crypt = Hex($crypt) ;~ ConsoleWrite("Encrypted: " & $crypt & @LF) ;~ $decrypt = _Crypt_DecryptData(Binary('0x' & $crypt), $key, $CALG_USERKEY) ;~ ConsoleWrite("Decrypted: " & BinaryToString($decrypt) & @LF) _Crypt_DestroyKey($key) _Crypt_Shutdown() Return $crypt EndFunc Func decryptted($data) $key = "756874587855478554482011"; // 32 byte key --> AES-256 $IV = '8979879879875464654654' _Crypt_Startup() $key = _CryptImportKey($CALG_AES_256, $key, 32) Const $KP_IV = 1 _CryptSetKeyParam($key, $KP_IV, $IV, 0, "str") ; So könnte man den Modus auf ECB stellen, dann wird der IV nicht verwendet, ; die Verschlüsselung ist schlechter. Const $KP_MODE = 4 Const $CRYPT_MODE_ECB = 2 ;~ _CryptSetKeyParam($key, $KP_MODE, $CRYPT_MODE_ECB, 0, "dword*") ;~ $crypt = _Crypt_EncryptData($data, $key, $CALG_USERKEY) ;~ $crypt = Hex($crypt) ;~ ConsoleWrite("Encrypted: " & $crypt & @LF) $decrypt = _Crypt_DecryptData(Binary('0x' & $data), $key, $CALG_USERKEY) ;~ ConsoleWrite("Decrypted: " & BinaryToString($decrypt) & @LF) _Crypt_DestroyKey($key) _Crypt_Shutdown() Return BinaryToString($decrypt) EndFunc Func _CryptImportKey($CALG, $bKey, $iKeyLength = -1) ; Author: ProgAndy If $iKeyLength < 1 Then $iKeyLength = BinaryLen($bKey) Local $blob = DllStructCreate("align 1;BYTE bType;BYTE bVersion;WORD reserved;dword aiKeyAlg;dword keysize;byte key[" & $iKeyLength & "]") DllStructSetData($blob, 1, 0x8) DllStructSetData($blob, 2, 2) DllStructSetData($blob, 4, $CALG) DllStructSetData($blob, 5, $iKeyLength) DllStructSetData($blob, 6, Binary($bKey)) Local $aRet = DllCall(__Crypt_DllHandle(), "bool", "CryptImportKey", "handle", __Crypt_Context(), "ptr", DllStructGetPtr($blob), "dword", DllStructGetSize($blob), "ptr", 0, "dword", 0, "ptr*", 0) If @error Then Return SetError(2, @error, 0) Return SetError(Not $aRet[0], 0, $aRet[6]) EndFunc Func _CryptSetKeyParam($hKey, $iParam, $vValue, $iFlags=0, $sValType=Default) ; Author: ProgAndy If Not $sValType Or $sValType = Default Then $sValType = "ptr" Local $aRet = DllCall(__Crypt_DllHandle(), "bool", "CryptSetKeyParam", "handle", $hKey, "uint", $iParam, $sValType, $vValue, "dword", $iFlags) If @error Then Return SetError(2, @error, 0) Return SetError(Not $aRet[0], 0, $aRet[0]) EndFunc Edited October 29, 2014 by slaughter Fire 1 In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
llewxam Posted March 9, 2014 Share Posted March 9, 2014 I had encountered the same issues with _EventLog__Read not giving valid data, and submitted a request to GaryFrost since he authored most of the event log UDF, and a bug report since I got no replies to a help request on the forum. Unfortunately I have left the job I was working at when trying to write an auto-blocker, so my ability to help will be pretty limited now. I still have access to the server I was concerned with, which was also 2008 R2 x64, but will only have access for a limited time.My thought process was to look for IPs that were flagged with an Event 4625 5 times within 30 seconds (the AdlibRegister time I was going to set to re-scan the event log), and add a firewall rule to block that IP for 5 minutes, in case there was some extraordinarily stupid reason why an IP you wanted accessing that server got flagged. Plus I would assume that if a brute force attempt started getting blocked the attacker would realize they were being blocked and would move on.I was going to use an INI file, a SQLite database would be just as well. To do the firewall rule addition I was just going to use:RunWait("netsh advfirewall firewall add rule name=[logBlock" & $WhatIP & "] dir=in interface=any action=block remoteip=" & $WhatIP)followed by:RunWait("netsh advfirewall firewall delete rule name=[logBlock" & $WhatIP & "] dir=in interface=any action=block remoteip=" & $WhatIP)at which time I would remove the offending IP address from the INI/SQLite.I will test the code you have submitted and see what else I might be able to contribute.Ian My projects: IP Scanner - Multi-threaded ping tool to scan your available networks for used and available IP addresses, shows ping times, resolves IPs in to host names, and allows individual IPs to be pinged. INFSniff - Great technicians tool - a tool which scans DriverPacks archives for INF files and parses out the HWIDs to a database file, and rapidly scans the local machine's HWIDs, searches the database for matches, and installs them. PPK3 (Persistent Process Killer V3) - Another for the techs - suppress running processes that you need to keep away, helpful when fighting spyware/viruses. Sync Tool - Folder sync tool with lots of real time information and several checking methods. USMT Front End - Front End for Microsoft's User State Migration Tool, including all files needed for USMT 3.01 and 4.01, 32 bit and 64 bit versions. Audit Tool - Computer audit tool to gather vital hardware, Windows, and Office information for IT managers and field techs. Capabilities include creating a customized site agent. CSV Viewer - Displays CSV files with automatic column sizing and font selection. Lines can also be copied to the clipboard for data extraction. MyDirStat - Lists number and size of files on a drive or specified path, allows for deletion within the app. 2048 Game - My version of 2048, fun tile game. Juice Lab - Ecigarette liquid making calculator. Data Protector - Secure notes to save sensitive information. VHD Footer - Add a footer to a forensic hard drive image to allow it to be mounted or used as a virtual machine hard drive. Find in File - Searches files containing a specified phrase. Link to comment Share on other sites More sharing options...
slaughter Posted March 9, 2014 Author Share Posted March 9, 2014 I had encountered the same issues with _EventLog__Read not giving valid data, and submitted a request to GaryFrost since he authored most of the event log UDF, and a bug report since I got no replies to a help request on the forum. Unfortunately I have left the job I was working at when trying to write an auto-blocker, so my ability to help will be pretty limited now. I still have access to the server I was concerned with, which was also 2008 R2 x64, but will only have access for a limited time. My thought process was to look for IPs that were flagged with an Event 4625 5 times within 30 seconds (the AdlibRegister time I was going to set to re-scan the event log), and add a firewall rule to block that IP for 5 minutes, in case there was some extraordinarily stupid reason why an IP you wanted accessing that server got flagged. Plus I would assume that if a brute force attempt started getting blocked the attacker would realize they were being blocked and would move on. I was going to use an INI file, a SQLite database would be just as well. To do the firewall rule addition I was just going to use: RunWait("netsh advfirewall firewall add rule name=[logBlock" & $WhatIP & "] dir=in interface=any action=block remoteip=" & $WhatIP) followed by:RunWait("netsh advfirewall firewall delete rule name=[logBlock" & $WhatIP & "] dir=in interface=any action=block remoteip=" & $WhatIP) at which time I would remove the offending IP address from the INI/SQLite. I will test the code you have submitted and see what else I might be able to contribute. Ian Thanks for command line, already found it but your post will help more I will use SQLite to tack when to add and remove IP blocks. Will be updates later today or tomorrow, as now its very good whether and i must go outside to enjoy it. In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
jchd Posted March 9, 2014 Share Posted March 9, 2014 slaughter, I also enjoy a sunny day so I post this short hint. You're not inserting "all in one batch" by using a single exec command with many insert statements inside. Parsing such a very long SQL statement can take more time than needed. Instead, I recommend you use a transaction to enclose your bulk inserts: corresponding SQL is begin; insert into mytable values (...); insert into mytable values (...); ... insert into mytable values (...); commit; With recent SQLite versions you can also insert multiple rows in one SQL statement, like this: begin; insert into mytable values (1st row...), (2nd row...), ..., (Nth row...); insert into mytable values (N+1 row...), (more rows...); commit; In general doing so cuts bulk insert time by something close to N. I don't run a server so my c:wevtutil.xml file is empty. I'd like to give you more advice to speed up your processing, but I'd need a sample data file with reasonably enough data to play with. If you can post one here or PM it I'd be grateful. 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) Link to comment Share on other sites More sharing options...
slaughter Posted March 9, 2014 Author Share Posted March 9, 2014 (edited) slaughter, I also enjoy a sunny day so I post this short hint. You're not inserting "all in one batch" by using a single exec command with many insert statements inside. Parsing such a very long SQL statement can take more time than needed. Instead, I recommend you use a transaction to enclose your bulk inserts: corresponding SQL is begin; insert into mytable values (...); insert into mytable values (...); ... insert into mytable values (...); commit; With recent SQLite versions you can also insert multiple rows in one SQL statement, like this: begin; insert into mytable values (1st row...), (2nd row...), ..., (Nth row...); insert into mytable values (N+1 row...), (more rows...); commit; In general doing so cuts bulk insert time by something close to N. I don't run a server so my c:wevtutil.xml file is empty. I'd like to give you more advice to speed up your processing, but I'd need a sample data file with reasonably enough data to play with. If you can post one here or PM it I'd be grateful. Thanks for SQL tips! just begin and commit decreased time from xxx seconds to x seconds link to log file wevtutil.xml pas au3 Edited March 9, 2014 by slaughter In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
jchd Posted March 9, 2014 Share Posted March 9, 2014 Thank you for the link. I'll play with it later today and let you know what I can do to decrease run time if possible. 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) Link to comment Share on other sites More sharing options...
slaughter Posted March 9, 2014 Author Share Posted March 9, 2014 Updated script in first post. Added firewall rules management. In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
jchd Posted March 10, 2014 Share Posted March 10, 2014 (edited) Ah! I'm a bit late it seems. I've worked on the first version only. Anyway, look at what I came up with: you can remove your original code included under the If 0 Then branch. The progress bar doesn't make much sense with small files. expandcollapse popup#RequireAdmin #include <GUIConstantsEx.au3> #include <Constants.au3> #include <Array.au3> #include <File.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> Global $iMemo _Main() Func _Main() Local $hEventLog, $aEvent Global $SQLite_db ; Create GUI GUICreate("EventLog", 604, 604) $Progress1 = GUICtrlCreateProgress(8, 25, 590, 25) $Label1 = GUICtrlCreateLabel("Reading event log", 8, 8, 430, 17) $iMemo = GUICtrlCreateEdit("", 2, 60, 600, 542) GUICtrlSetFont($iMemo, 9, 400, 0, "Courier New") GUISetState() $read_events = 5000 _SQLite_Startup() MemoWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) $db_name = @ScriptDir & "\SQLite_v2.db" If FileExists($db_name) Then MemoWrite("Opening existing database. "& $db_name) $SQLite_db = _SQLite_Open($db_name) Else MemoWrite("Creating new database at: "& $db_name) $SQLite_db = _SQLite_Open($db_name) MemoWrite("Creating database structure.") _SQLite_Exec($SQLite_db, "CREATE TABLE ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT, log_id CHAR UNIQUE, date CHAR, ip CHAR, user CHAR)") EndIf GUICtrlSetData($Label1, "Reading event log from windows") MemoWrite("Generating event log from windows") GUICtrlSetData($Progress1, 1) $cmd = 'wevtutil qe Security "/q:*[System[band(Keywords,8010000000000000)]]" /c:'& $read_events &' /rd:true /f:xml>C:\wevtutil.xml' FileDelete ( "c:\wevtutil.xml" ) Local $foo = RunWait(@ComSpec & ' /c '& $cmd, @SystemDir, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) ;$data = FileRead('C:\wevtutil.xml') ;FileDelete ( "c:\wevtutil.xml" ) ;MemoWrite($data) ;GUICtrlSetData($Label1, "Reading event log: "& $read_events &"/" & $read_events) GUICtrlSetData($Label1, "Reading event log done.") MemoWrite("Generating event log done.") MemoWrite("Reading event log.") If 0 Then ; Define a variable to pass to _FileReadToArray. Local $aArray = 0 ; Read the current script file into an array using the variable defined previously. If Not _FileReadToArray("wevtutil.xml", $aArray, 0) Then MsgBox($MB_SYSTEMMODAL, "", "There was an error reading the file. @error: " & @error) ; An error occurred reading the current script file. EndIf ; Display the array in _ArrayDisplay. ;_ArrayDisplay($aArray) MemoWrite("Reading event log done.") MemoWrite("Phrasing event log.") $total_records = UBound($aArray) Local $IPArray[0][5] Local $sql_qry = "" For $vElement In $aArray $size = UBound($IPArray) + 1 ReDim $IPArray[$size][5] $ix = $size -1 $percent = Round(100 / $total_records * $size) GUICtrlSetData($Progress1, $percent) GUICtrlSetData($Label1, "Phrasing event log: "& $ix &"/" & $total_records) $date = "<TimeCreated SystemTime='(.*?)'/>" $array = StringRegExp($vElement, $date, 1) If IsArray($array) Then $IPArray[$ix][1] = $array[0] Else $IPArray[$ix][1] = "none" EndIf $EventID = "<EventRecordID>(.*?)</EventRecordID>" $array = StringRegExp($vElement, $EventID, 1) If IsArray($array) Then $IPArray[$ix][0] = $array[0] Else $IPArray[$ix][0] = "none" EndIf $network = "<Data Name='IpAddress'>(.*?)</Data>" $array = StringRegExp($vElement, $network, 1) If IsArray($array) Then $IPArray[$ix][2] = $array[0] Else $IPArray[$ix][2] = "none" EndIf $account = "<Data Name='TargetUserName'>(.*?)</Data>" $array = StringRegExp($vElement, $account, 1) If IsArray($array) Then ;_ArrayDisplay($array) $IPArray[$ix][3] = $array[0] Else $IPArray[$ix][3] = "none" EndIf $sql_qry = "INSERT OR IGNORE INTO ip_list VALUES (null, '"& $IPArray[$ix][0] &"', '"& $IPArray[$ix][1] &"', '"& $IPArray[$ix][2] &"', '"& $IPArray[$ix][3] &"');"& @CRLF & $sql_qry ;MemoWrite($sql_qry) ;~ If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then ;~ MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) ;~ EndIf Next MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf MemoWrite("Inserting data to SQLite done.") Else Local $rawdata = FileRead("wevtutil1.xml") ; ##### reading supplied file directly ! Local $captures = StringRegExp($rawdata, "(?i)<TimeCreated SystemTime='(.*?)'/><EventRecordID>(.*?)</EventRecordID>.*?<Data Name='TargetUserName'>(.*?)</Data>.*?<Data Name='IpAddress'>(.*?)</Data>", 3) MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") ;~ _ArrayDisplay($captures) If IsArray($captures) Then Local $sql If Not _SQLite_Exec($SQLite_db, "begin;") = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf For $i = 0 To (UBound($captures) / 4) - 1 $sql &= "('" & $captures[4 * $i] & "', '" & $captures[4 * $i + 1] & "', '" & $captures[4 * $i + 2] & "', '" & $captures[4 * $i + 3] & "')," If Mod($i + 1, 500) = 0 Or $i = (UBound($captures) / 4) - 1 Then $sql = "INSERT OR IGNORE INTO ip_list (date, log_id, user, ip) VALUES " & StringTrimRight($sql, 1) If Not _SQLite_Exec($SQLite_db, $sql) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf $sql = "" EndIf Next If Not _SQLite_Exec($SQLite_db, "commit;") = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf EndIf MemoWrite("Inserting data to SQLite done.") EndIf MemoWrite("IP address, failed authentication tires") display_resutls("SELECT ip, count(*) as 'Cnt' FROM ip_list GROUP BY ip;") MemoWrite("Username, failed authentication tires") display_resutls("SELECT user, count(*) as 'Cnt' FROM ip_list GROUP BY user;") ; Loop until user exits Do Until GUIGetMsg() = $GUI_EVENT_CLOSE EndFunc ;==>_Main Func display_resutls($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval Global $SQLite_db $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDisplay($aResult, "Query Result") $iRows = UBound($aResult)-1 $iCols = UBound($aResult, 2)-1 For $i = 1 To $iRows $string = "" For $x = 0 To $iCols $string = $string & $aResult[$i][$x] & " " Next MemoWrite($string) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc ; Write a line to the memo control Func MemoWrite($sMessage) GUICtrlSetData($iMemo, $sMessage & @CRLF, 1) EndFunc ;==>MemoWrite What I've done is very simple: read the input in one shot, regexp all data at once in a 1D array and group 500 row values per insert, all in one transaction. Edited March 10, 2014 by jchd slaughter 1 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) Link to comment Share on other sites More sharing options...
llewxam Posted March 10, 2014 Share Posted March 10, 2014 (edited) I don't want to make a fork in your code, but I made some changes: expandcollapse popup#RequireAdmin #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=C:\Program Files (x86)\AutoIt3\Icons\au3script_v9.ico #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <GUIConstantsEx.au3> #include <Constants.au3> #include <Array.au3> #include <File.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <GuiListView.au3> #include <GuiImageList.au3> #include <WindowsConstants.au3> $AdlibTimer = 1000 * 30 AdlibRegister(update_display, $AdlibTimer) Global $iMemo _Main() Func _Main() Local $hEventLog, $aEvent Global $SQLite_db, $hListView, $GUI, $wevtutil_query, $initial_event_count, $Label1, $Progress1, $sql_qry ; Create GUI $GUI = GUICreate("EventLog", 604, 604) $Progress1 = GUICtrlCreateProgress(8, 25, 590, 25) $Label1 = GUICtrlCreateLabel("Reading event log", 8, 8, 430, 17) $iMemo = GUICtrlCreateEdit("", 2, 360, 600, 242) $hListView = _GUICtrlListView_Create($GUI, "", 2, 75, 600, 280) _GUICtrlListView_SetExtendedListViewStyle($hListView, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES)) ;_GUICtrlListView_InsertColumn($hListView, 0, "Date", 100) _GUICtrlListView_InsertColumn($hListView, 1, "IP Address", 150) _GUICtrlListView_InsertColumn($hListView, 2, "Count", 100) _GUICtrlListView_InsertColumn($hListView, 3, "Status", 100) $Button1 = GUICtrlCreateButton("Block", 2, 55, 75, 15) $Button2 = GUICtrlCreateButton("UnBlock", 80, 55, 75, 15) ;$Button3 = GUICtrlCreateButton("exit", 2, 55, 75, 15) GUICtrlSetFont($iMemo, 9, 400, 0, "Courier New") GUISetState() $initial_event_count = 10000 $update_event_count = 100 _SQLite_Startup() MemoWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) GUICtrlSetData($Progress1, 1) $wevtutil_query = "*[System[band(Keywords,8010000000000000)]]" $db_name = @ScriptDir & "\SQLite_v3.db" If FileExists($db_name) Then $wevtutil_query = "*[System[band(Keywords,8010000000000000) and TimeCreated[timediff(@SystemTime) < 3600000]]]" MemoWrite("Opening existing database. " & $db_name) $SQLite_db = _SQLite_Open($db_name) $initial_event_count = $update_event_count Else $wevtutil_query = "*[System[band(Keywords,8010000000000000)]]" MemoWrite("Creating new database at: " & $db_name) $SQLite_db = _SQLite_Open($db_name) MemoWrite("Creating database structure.") ;~ _SQLite_Exec($SQLite_db, "CREATE TABLE ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT,log_id CHAR(150) UNIQUE, date CHAR(150), ip CHAR(150), user CHAR(150), status (char(10))") _SQLite_Exec($SQLite_db, "CREATE TABLE ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT,log_id CHAR(150) UNIQUE, date CHAR(150), ip CHAR(150), user CHAR(150), status CHAR(10))") EndIf Update_database() ;~ MemoWrite("IP address, failed authentication tires") ;~ display_resutls("SELECT ip, count(*) as 'Cnt' FROM ip_list GROUP BY ip;") ;~ MemoWrite("Username, failed authentication tires") ;~ display_resutls("SELECT user, count(*) as 'Cnt' FROM ip_list GROUP BY user;") $per_sec = @YEAR & "-" & @MON & "-" & @MDAY & "T" & @HOUR & ":" & @MIN & ":" & @SEC $per_min = @YEAR & "-" & @MON & "-" & @MDAY & "T" & @HOUR & ":" & @MIN $per_hour = @YEAR & "-" & @MON & "-" & @MDAY & "T" & @HOUR $per_day = @YEAR & "-" & @MON & "-" & @MDAY ;~ MemoWrite("Login attempts: per sec") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_sec&"%' GROUP BY ip ORDER by count DESC;") ;~ MemoWrite("Login attempts: per min") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_min&"%' GROUP BY ip ORDER by count DESC;") ;~ MemoWrite("Login attempts: per hour") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_hour&"%' GROUP BY ip ORDER by count DESC;") ;~ MemoWrite("Login attempts: per day") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_day&"%' GROUP BY ip ORDER by count DESC;") MemoWrite("Login attempts: all time") display_resutls("SELECT ip, count(*) as 'count', status FROM ip_list GROUP BY ip ORDER by count DESC;") ; Loop until user exits While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit Case $Button1 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) MemoWrite("Address: " & $sSelect & " is added to firewal for blocking") $WhatIP = $sSelect RunWait(@ComSpec & ' /c ' & "netsh advfirewall firewall add rule name=[logBlock_" & $WhatIP & "] dir=in interface=any action=block remoteip=" & $WhatIP, "", @SW_HIDE) _SQLite_Exec($SQLite_db, "UPDATE ip_list SET status='blocked' WHERE ip='" & $WhatIP & "'") _GUICtrlListView_SetItemText($hListView, $iSelect, "blocked", 2) Case $Button2 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) MemoWrite("Address: " & $sSelect & " is removed from firewal") $WhatIP = $sSelect RunWait(@ComSpec & ' /c ' & "netsh advfirewall firewall delete rule name=[logBlock_" & $WhatIP & "] dir=in", "", @SW_HIDE) _SQLite_Exec($SQLite_db, "UPDATE ip_list SET status='unblocked' WHERE ip='" & $WhatIP & "'") _GUICtrlListView_SetItemText($hListView, $iSelect, "unblocked", 2) EndSwitch WEnd EndFunc ;==>_Main Func update_display() AdlibUnRegister() Update_database() MemoWrite("Updating login attempts: all time") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list GROUP BY ip ORDER by count DESC;") display_resutls("SELECT ip, count(*) as 'count', status FROM ip_list GROUP BY ip ORDER by count DESC;") AdlibRegister(update_display, $AdlibTimer) EndFunc ;==>update_display Func Update_database() Global $wevtutil_query, $initial_event_count, $Label1 GUICtrlSetData($Label1, "Reading event log from windows") MemoWrite("Generating event log from windows") $cmd = 'wevtutil qe Security "/q:' & $wevtutil_query & '" /c:' & $initial_event_count & ' /rd:true /f:xml>C:\wevtutil.xml' $wevtutil_query = "*[System[band(Keywords,8010000000000000) and TimeCreated[timediff(@SystemTime) < 3600000]]]" ;after initail run dont read all log only 24 h ;~ MemoWrite($cmd) FileDelete("c:\wevtutil.xml") Local $foo = RunWait(@ComSpec & ' /c ' & $cmd, @SystemDir, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) GUICtrlSetData($Label1, "Reading event log done.") MemoWrite("Generating event log done.") Global $Label1, $Progress1, $sql_qry MemoWrite("Reading event log.") Local $aArray = 0 If Not _FileReadToArray("C:\wevtutil.xml", $aArray, 0) Then ;MsgBox($MB_SYSTEMMODAL, "", "There was an error reading the file. @error: " & @error) ; An error occurred reading the current script file. MemoWrite("!!Warning no events in last 24h, or cant generate event log file at all.") EndIf MemoWrite("Reading event log done.") If IsArray($aArray) Then MemoWrite("Phrasing event log.") $total_records = UBound($aArray) Local $IPArray[0][5] Local $sql_qry = "" For $vElement In $aArray $size = UBound($IPArray) + 1 ReDim $IPArray[$size][5] $ix = $size - 1 $percent = Round(100 / $total_records * $size) GUICtrlSetData($Progress1, $percent) GUICtrlSetData($Label1, "Phrasing event log: " & $ix & "/" & $total_records) $date = "<TimeCreated SystemTime='(.*?)'/>" $array = StringRegExp($vElement, $date, 1) If IsArray($array) Then $IPArray[$ix][1] = $array[0] Else $IPArray[$ix][1] = "none" EndIf $EventID = "<EventRecordID>(.*?)</EventRecordID>" $array = StringRegExp($vElement, $EventID, 1) If IsArray($array) Then $IPArray[$ix][0] = $array[0] Else $IPArray[$ix][0] = "none" EndIf $network = "<Data Name='IpAddress'>(.*?)</Data>" $array = StringRegExp($vElement, $network, 1) If IsArray($array) Then $IPArray[$ix][2] = $array[0] Else $IPArray[$ix][2] = "none" EndIf $account = "<Data Name='TargetUserName'>(.*?)</Data>" $array = StringRegExp($vElement, $account, 1) If IsArray($array) Then ;_ArrayDisplay($array) $IPArray[$ix][3] = $array[0] Else $IPArray[$ix][3] = "none" EndIf $sql_qry = "INSERT OR IGNORE INTO ip_list VALUES (null, '" & $IPArray[$ix][0] & "', '" & $IPArray[$ix][1] & "', '" & $IPArray[$ix][2] & "', '" & $IPArray[$ix][3] & "', 'unblocked');" & @CRLF & $sql_qry Next MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") $sql_qry = "begin;" & @CRLF & $sql_qry & @CRLF & "commit;" If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf MemoWrite("Inserting data to SQLite done.") Else MemoWrite("No data fo phrasing and inserting to SQLite.") GUICtrlSetData($Progress1, 100) EndIf EndFunc ;==>Update_database Func display_resutls($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval Global $SQLite_db, $hListView $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDelete($aResult, 0) _GUICtrlListView_DeleteAllItems($hListView) _GUICtrlListView_AddArray($hListView, $aResult) ;_ArrayDisplay($aResult, "Query Result") $iRows = UBound($aResult) - 1 $iCols = UBound($aResult, 2) - 1 For $i = 1 To $iRows $string = "" For $x = 0 To $iCols $string = $string & $aResult[$i][$x] & " " Next MemoWrite($string) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc ;==>display_resutls ; Write a line to the memo control Func MemoWrite($sMessage) GUICtrlSetData($iMemo, $sMessage & @CRLF, 1) EndFunc ;==>MemoWriteAdded a table to the SQL for "status", being blocked or unblocked so the status will persist through app restarts, as well as added the status to the GUI. So far so good! I am also going to try to focus on the speed of the scan, I haven't look at how you are doing it at all yet, just testing basic function. Also added a variable for the AdlibRegister which I set to 30 seconds, and use an AdlibUnregister before running update_display and AblibRegister at the end, so be safe while performing an initial scan.Ian Edited March 10, 2014 by llewxam My projects: IP Scanner - Multi-threaded ping tool to scan your available networks for used and available IP addresses, shows ping times, resolves IPs in to host names, and allows individual IPs to be pinged. INFSniff - Great technicians tool - a tool which scans DriverPacks archives for INF files and parses out the HWIDs to a database file, and rapidly scans the local machine's HWIDs, searches the database for matches, and installs them. PPK3 (Persistent Process Killer V3) - Another for the techs - suppress running processes that you need to keep away, helpful when fighting spyware/viruses. Sync Tool - Folder sync tool with lots of real time information and several checking methods. USMT Front End - Front End for Microsoft's User State Migration Tool, including all files needed for USMT 3.01 and 4.01, 32 bit and 64 bit versions. Audit Tool - Computer audit tool to gather vital hardware, Windows, and Office information for IT managers and field techs. Capabilities include creating a customized site agent. CSV Viewer - Displays CSV files with automatic column sizing and font selection. Lines can also be copied to the clipboard for data extraction. MyDirStat - Lists number and size of files on a drive or specified path, allows for deletion within the app. 2048 Game - My version of 2048, fun tile game. Juice Lab - Ecigarette liquid making calculator. Data Protector - Secure notes to save sensitive information. VHD Footer - Add a footer to a forensic hard drive image to allow it to be mounted or used as a virtual machine hard drive. Find in File - Searches files containing a specified phrase. Link to comment Share on other sites More sharing options...
slaughter Posted March 10, 2014 Author Share Posted March 10, 2014 Big update In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
slaughter Posted March 10, 2014 Author Share Posted March 10, 2014 Ah! I'm a bit late it seems. I've worked on the first version only. Anyway, look at what I came up with: you can remove your original code included under the If 0 Then branch. The progress bar doesn't make much sense with small files. expandcollapse popup#RequireAdmin #include <GUIConstantsEx.au3> #include <Constants.au3> #include <Array.au3> #include <File.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> Global $iMemo _Main() Func _Main() Local $hEventLog, $aEvent Global $SQLite_db ; Create GUI GUICreate("EventLog", 604, 604) $Progress1 = GUICtrlCreateProgress(8, 25, 590, 25) $Label1 = GUICtrlCreateLabel("Reading event log", 8, 8, 430, 17) $iMemo = GUICtrlCreateEdit("", 2, 60, 600, 542) GUICtrlSetFont($iMemo, 9, 400, 0, "Courier New") GUISetState() $read_events = 5000 _SQLite_Startup() MemoWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) $db_name = @ScriptDir & "\SQLite_v2.db" If FileExists($db_name) Then MemoWrite("Opening existing database. "& $db_name) $SQLite_db = _SQLite_Open($db_name) Else MemoWrite("Creating new database at: "& $db_name) $SQLite_db = _SQLite_Open($db_name) MemoWrite("Creating database structure.") _SQLite_Exec($SQLite_db, "CREATE TABLE ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT, log_id CHAR UNIQUE, date CHAR, ip CHAR, user CHAR)") EndIf GUICtrlSetData($Label1, "Reading event log from windows") MemoWrite("Generating event log from windows") GUICtrlSetData($Progress1, 1) $cmd = 'wevtutil qe Security "/q:*[System[band(Keywords,8010000000000000)]]" /c:'& $read_events &' /rd:true /f:xml>C:\wevtutil.xml' FileDelete ( "c:\wevtutil.xml" ) Local $foo = RunWait(@ComSpec & ' /c '& $cmd, @SystemDir, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) ;$data = FileRead('C:\wevtutil.xml') ;FileDelete ( "c:\wevtutil.xml" ) ;MemoWrite($data) ;GUICtrlSetData($Label1, "Reading event log: "& $read_events &"/" & $read_events) GUICtrlSetData($Label1, "Reading event log done.") MemoWrite("Generating event log done.") MemoWrite("Reading event log.") If 0 Then ; Define a variable to pass to _FileReadToArray. Local $aArray = 0 ; Read the current script file into an array using the variable defined previously. If Not _FileReadToArray("wevtutil.xml", $aArray, 0) Then MsgBox($MB_SYSTEMMODAL, "", "There was an error reading the file. @error: " & @error) ; An error occurred reading the current script file. EndIf ; Display the array in _ArrayDisplay. ;_ArrayDisplay($aArray) MemoWrite("Reading event log done.") MemoWrite("Phrasing event log.") $total_records = UBound($aArray) Local $IPArray[0][5] Local $sql_qry = "" For $vElement In $aArray $size = UBound($IPArray) + 1 ReDim $IPArray[$size][5] $ix = $size -1 $percent = Round(100 / $total_records * $size) GUICtrlSetData($Progress1, $percent) GUICtrlSetData($Label1, "Phrasing event log: "& $ix &"/" & $total_records) $date = "<TimeCreated SystemTime='(.*?)'/>" $array = StringRegExp($vElement, $date, 1) If IsArray($array) Then $IPArray[$ix][1] = $array[0] Else $IPArray[$ix][1] = "none" EndIf $EventID = "<EventRecordID>(.*?)</EventRecordID>" $array = StringRegExp($vElement, $EventID, 1) If IsArray($array) Then $IPArray[$ix][0] = $array[0] Else $IPArray[$ix][0] = "none" EndIf $network = "<Data Name='IpAddress'>(.*?)</Data>" $array = StringRegExp($vElement, $network, 1) If IsArray($array) Then $IPArray[$ix][2] = $array[0] Else $IPArray[$ix][2] = "none" EndIf $account = "<Data Name='TargetUserName'>(.*?)</Data>" $array = StringRegExp($vElement, $account, 1) If IsArray($array) Then ;_ArrayDisplay($array) $IPArray[$ix][3] = $array[0] Else $IPArray[$ix][3] = "none" EndIf $sql_qry = "INSERT OR IGNORE INTO ip_list VALUES (null, '"& $IPArray[$ix][0] &"', '"& $IPArray[$ix][1] &"', '"& $IPArray[$ix][2] &"', '"& $IPArray[$ix][3] &"');"& @CRLF & $sql_qry ;MemoWrite($sql_qry) ;~ If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then ;~ MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) ;~ EndIf Next MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf MemoWrite("Inserting data to SQLite done.") Else Local $rawdata = FileRead("wevtutil1.xml") ; ##### reading supplied file directly ! Local $captures = StringRegExp($rawdata, "(?i)<TimeCreated SystemTime='(.*?)'/><EventRecordID>(.*?)</EventRecordID>.*?<Data Name='TargetUserName'>(.*?)</Data>.*?<Data Name='IpAddress'>(.*?)</Data>", 3) MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") ;~ _ArrayDisplay($captures) If IsArray($captures) Then Local $sql If Not _SQLite_Exec($SQLite_db, "begin;") = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf For $i = 0 To (UBound($captures) / 4) - 1 $sql &= "('" & $captures[4 * $i] & "', '" & $captures[4 * $i + 1] & "', '" & $captures[4 * $i + 2] & "', '" & $captures[4 * $i + 3] & "')," If Mod($i + 1, 500) = 0 Or $i = (UBound($captures) / 4) - 1 Then $sql = "INSERT OR IGNORE INTO ip_list (date, log_id, user, ip) VALUES " & StringTrimRight($sql, 1) If Not _SQLite_Exec($SQLite_db, $sql) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf $sql = "" EndIf Next If Not _SQLite_Exec($SQLite_db, "commit;") = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf EndIf MemoWrite("Inserting data to SQLite done.") EndIf MemoWrite("IP address, failed authentication tires") display_resutls("SELECT ip, count(*) as 'Cnt' FROM ip_list GROUP BY ip;") MemoWrite("Username, failed authentication tires") display_resutls("SELECT user, count(*) as 'Cnt' FROM ip_list GROUP BY user;") ; Loop until user exits Do Until GUIGetMsg() = $GUI_EVENT_CLOSE EndFunc ;==>_Main Func display_resutls($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval Global $SQLite_db $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDisplay($aResult, "Query Result") $iRows = UBound($aResult)-1 $iCols = UBound($aResult, 2)-1 For $i = 1 To $iRows $string = "" For $x = 0 To $iCols $string = $string & $aResult[$i][$x] & " " Next MemoWrite($string) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc ; Write a line to the memo control Func MemoWrite($sMessage) GUICtrlSetData($iMemo, $sMessage & @CRLF, 1) EndFunc ;==>MemoWrite What I've done is very simple: read the input in one shot, regexp all data at once in a 1D array and group 500 row values per insert, all in one transaction. Would this method be faster than using now? I'm just concern that if any value from line is missing like TargetUserName it can get not correct associated fields values or am I wrong? In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
slaughter Posted March 10, 2014 Author Share Posted March 10, 2014 I don't want to make a fork in your code, but I made some changes: expandcollapse popup#RequireAdmin #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=C:\Program Files (x86)\AutoIt3\Icons\au3script_v9.ico #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <GUIConstantsEx.au3> #include <Constants.au3> #include <Array.au3> #include <File.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <GuiListView.au3> #include <GuiImageList.au3> #include <WindowsConstants.au3> $AdlibTimer = 1000 * 30 AdlibRegister(update_display, $AdlibTimer) Global $iMemo _Main() Func _Main() Local $hEventLog, $aEvent Global $SQLite_db, $hListView, $GUI, $wevtutil_query, $initial_event_count, $Label1, $Progress1, $sql_qry ; Create GUI $GUI = GUICreate("EventLog", 604, 604) $Progress1 = GUICtrlCreateProgress(8, 25, 590, 25) $Label1 = GUICtrlCreateLabel("Reading event log", 8, 8, 430, 17) $iMemo = GUICtrlCreateEdit("", 2, 360, 600, 242) $hListView = _GUICtrlListView_Create($GUI, "", 2, 75, 600, 280) _GUICtrlListView_SetExtendedListViewStyle($hListView, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES)) ;_GUICtrlListView_InsertColumn($hListView, 0, "Date", 100) _GUICtrlListView_InsertColumn($hListView, 1, "IP Address", 150) _GUICtrlListView_InsertColumn($hListView, 2, "Count", 100) _GUICtrlListView_InsertColumn($hListView, 3, "Status", 100) $Button1 = GUICtrlCreateButton("Block", 2, 55, 75, 15) $Button2 = GUICtrlCreateButton("UnBlock", 80, 55, 75, 15) ;$Button3 = GUICtrlCreateButton("exit", 2, 55, 75, 15) GUICtrlSetFont($iMemo, 9, 400, 0, "Courier New") GUISetState() $initial_event_count = 10000 $update_event_count = 100 _SQLite_Startup() MemoWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) GUICtrlSetData($Progress1, 1) $wevtutil_query = "*[System[band(Keywords,8010000000000000)]]" $db_name = @ScriptDir & "\SQLite_v3.db" If FileExists($db_name) Then $wevtutil_query = "*[System[band(Keywords,8010000000000000) and TimeCreated[timediff(@SystemTime) < 3600000]]]" MemoWrite("Opening existing database. " & $db_name) $SQLite_db = _SQLite_Open($db_name) $initial_event_count = $update_event_count Else $wevtutil_query = "*[System[band(Keywords,8010000000000000)]]" MemoWrite("Creating new database at: " & $db_name) $SQLite_db = _SQLite_Open($db_name) MemoWrite("Creating database structure.") ;~ _SQLite_Exec($SQLite_db, "CREATE TABLE ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT,log_id CHAR(150) UNIQUE, date CHAR(150), ip CHAR(150), user CHAR(150), status (char(10))") _SQLite_Exec($SQLite_db, "CREATE TABLE ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT,log_id CHAR(150) UNIQUE, date CHAR(150), ip CHAR(150), user CHAR(150), status CHAR(10))") EndIf Update_database() ;~ MemoWrite("IP address, failed authentication tires") ;~ display_resutls("SELECT ip, count(*) as 'Cnt' FROM ip_list GROUP BY ip;") ;~ MemoWrite("Username, failed authentication tires") ;~ display_resutls("SELECT user, count(*) as 'Cnt' FROM ip_list GROUP BY user;") $per_sec = @YEAR & "-" & @MON & "-" & @MDAY & "T" & @HOUR & ":" & @MIN & ":" & @SEC $per_min = @YEAR & "-" & @MON & "-" & @MDAY & "T" & @HOUR & ":" & @MIN $per_hour = @YEAR & "-" & @MON & "-" & @MDAY & "T" & @HOUR $per_day = @YEAR & "-" & @MON & "-" & @MDAY ;~ MemoWrite("Login attempts: per sec") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_sec&"%' GROUP BY ip ORDER by count DESC;") ;~ MemoWrite("Login attempts: per min") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_min&"%' GROUP BY ip ORDER by count DESC;") ;~ MemoWrite("Login attempts: per hour") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_hour&"%' GROUP BY ip ORDER by count DESC;") ;~ MemoWrite("Login attempts: per day") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_day&"%' GROUP BY ip ORDER by count DESC;") MemoWrite("Login attempts: all time") display_resutls("SELECT ip, count(*) as 'count', status FROM ip_list GROUP BY ip ORDER by count DESC;") ; Loop until user exits While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit Case $Button1 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) MemoWrite("Address: " & $sSelect & " is added to firewal for blocking") $WhatIP = $sSelect RunWait(@ComSpec & ' /c ' & "netsh advfirewall firewall add rule name=[logBlock_" & $WhatIP & "] dir=in interface=any action=block remoteip=" & $WhatIP, "", @SW_HIDE) _SQLite_Exec($SQLite_db, "UPDATE ip_list SET status='blocked' WHERE ip='" & $WhatIP & "'") _GUICtrlListView_SetItemText($hListView, $iSelect, "blocked", 2) Case $Button2 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) MemoWrite("Address: " & $sSelect & " is removed from firewal") $WhatIP = $sSelect RunWait(@ComSpec & ' /c ' & "netsh advfirewall firewall delete rule name=[logBlock_" & $WhatIP & "] dir=in", "", @SW_HIDE) _SQLite_Exec($SQLite_db, "UPDATE ip_list SET status='unblocked' WHERE ip='" & $WhatIP & "'") _GUICtrlListView_SetItemText($hListView, $iSelect, "unblocked", 2) EndSwitch WEnd EndFunc ;==>_Main Func update_display() AdlibUnRegister() Update_database() MemoWrite("Updating login attempts: all time") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list GROUP BY ip ORDER by count DESC;") display_resutls("SELECT ip, count(*) as 'count', status FROM ip_list GROUP BY ip ORDER by count DESC;") AdlibRegister(update_display, $AdlibTimer) EndFunc ;==>update_display Func Update_database() Global $wevtutil_query, $initial_event_count, $Label1 GUICtrlSetData($Label1, "Reading event log from windows") MemoWrite("Generating event log from windows") $cmd = 'wevtutil qe Security "/q:' & $wevtutil_query & '" /c:' & $initial_event_count & ' /rd:true /f:xml>C:\wevtutil.xml' $wevtutil_query = "*[System[band(Keywords,8010000000000000) and TimeCreated[timediff(@SystemTime) < 3600000]]]" ;after initail run dont read all log only 24 h ;~ MemoWrite($cmd) FileDelete("c:\wevtutil.xml") Local $foo = RunWait(@ComSpec & ' /c ' & $cmd, @SystemDir, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) GUICtrlSetData($Label1, "Reading event log done.") MemoWrite("Generating event log done.") Global $Label1, $Progress1, $sql_qry MemoWrite("Reading event log.") Local $aArray = 0 If Not _FileReadToArray("C:\wevtutil.xml", $aArray, 0) Then ;MsgBox($MB_SYSTEMMODAL, "", "There was an error reading the file. @error: " & @error) ; An error occurred reading the current script file. MemoWrite("!!Warning no events in last 24h, or cant generate event log file at all.") EndIf MemoWrite("Reading event log done.") If IsArray($aArray) Then MemoWrite("Phrasing event log.") $total_records = UBound($aArray) Local $IPArray[0][5] Local $sql_qry = "" For $vElement In $aArray $size = UBound($IPArray) + 1 ReDim $IPArray[$size][5] $ix = $size - 1 $percent = Round(100 / $total_records * $size) GUICtrlSetData($Progress1, $percent) GUICtrlSetData($Label1, "Phrasing event log: " & $ix & "/" & $total_records) $date = "<TimeCreated SystemTime='(.*?)'/>" $array = StringRegExp($vElement, $date, 1) If IsArray($array) Then $IPArray[$ix][1] = $array[0] Else $IPArray[$ix][1] = "none" EndIf $EventID = "<EventRecordID>(.*?)</EventRecordID>" $array = StringRegExp($vElement, $EventID, 1) If IsArray($array) Then $IPArray[$ix][0] = $array[0] Else $IPArray[$ix][0] = "none" EndIf $network = "<Data Name='IpAddress'>(.*?)</Data>" $array = StringRegExp($vElement, $network, 1) If IsArray($array) Then $IPArray[$ix][2] = $array[0] Else $IPArray[$ix][2] = "none" EndIf $account = "<Data Name='TargetUserName'>(.*?)</Data>" $array = StringRegExp($vElement, $account, 1) If IsArray($array) Then ;_ArrayDisplay($array) $IPArray[$ix][3] = $array[0] Else $IPArray[$ix][3] = "none" EndIf $sql_qry = "INSERT OR IGNORE INTO ip_list VALUES (null, '" & $IPArray[$ix][0] & "', '" & $IPArray[$ix][1] & "', '" & $IPArray[$ix][2] & "', '" & $IPArray[$ix][3] & "', 'unblocked');" & @CRLF & $sql_qry Next MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") $sql_qry = "begin;" & @CRLF & $sql_qry & @CRLF & "commit;" If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf MemoWrite("Inserting data to SQLite done.") Else MemoWrite("No data fo phrasing and inserting to SQLite.") GUICtrlSetData($Progress1, 100) EndIf EndFunc ;==>Update_database Func display_resutls($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval Global $SQLite_db, $hListView $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDelete($aResult, 0) _GUICtrlListView_DeleteAllItems($hListView) _GUICtrlListView_AddArray($hListView, $aResult) ;_ArrayDisplay($aResult, "Query Result") $iRows = UBound($aResult) - 1 $iCols = UBound($aResult, 2) - 1 For $i = 1 To $iRows $string = "" For $x = 0 To $iCols $string = $string & $aResult[$i][$x] & " " Next MemoWrite($string) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc ;==>display_resutls ; Write a line to the memo control Func MemoWrite($sMessage) GUICtrlSetData($iMemo, $sMessage & @CRLF, 1) EndFunc ;==>MemoWrite Added a table to the SQL for "status", being blocked or unblocked so the status will persist through app restarts, as well as added the status to the GUI. So far so good! I am also going to try to focus on the speed of the scan, I haven't look at how you are doing it at all yet, just testing basic function. Also added a variable for the AdlibRegister which I set to 30 seconds, and use an AdlibUnregister before running update_display and AblibRegister at the end, so be safe while performing an initial scan. Ian Thanks for tips! I have used your idea of status its more easy to manage all IP lists, just haven't got chance to update AdlibRegister functionality as you say it may be a problem on initial read. In future update will be fixed this issue In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
jchd Posted March 10, 2014 Share Posted March 10, 2014 Try it: the input format is always the same and when a field is empty it becomes an empty string in the DB. A trigger can turn that into a NULL if that's is what you want. It's hundreds of times faster. 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) Link to comment Share on other sites More sharing options...
llewxam Posted March 10, 2014 Share Posted March 10, 2014 (edited) Made some more tweaks late last night, I won't post a full update since other things are no doubt being changed.Updates, you'll know where to put them: $Button1 = GUICtrlCreateButton("Block", 2, 55, 75, 15) $Button2 = GUICtrlCreateButton("UnBlock", 80, 55, 75, 15) $Button3 = GUICtrlCreateButton("Geolocate", 158, 55, 75, 15)Case $Button3 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) _GeoLocate($sSelect) MemoWrite("Geolocated " & $sSelect)Func _GeoLocate($ip) ShellExecute("http://www.infosniper.net/index.php?ip_address=" & $ip & "&map_source=1&overview_map=1&lang=1&map_type=1&zoom_level=7") EndFunc ;==>_GeoLocateHelpful for determining if you really want to block an unknown address manually. As for an auto-mode, would be nice to add a timestamp to the database of when the IP was blocked, and on each new scheduled scan look at all blocked IPs and count the seconds since the block, and if a set amount of time has passed like 5 minutes then set to unblock and remove the IP from the database. Also, for the scans, you could use the highest EventID for where to start looking for new events to avoid duplicates.Ian Edited March 10, 2014 by llewxam My projects: IP Scanner - Multi-threaded ping tool to scan your available networks for used and available IP addresses, shows ping times, resolves IPs in to host names, and allows individual IPs to be pinged. INFSniff - Great technicians tool - a tool which scans DriverPacks archives for INF files and parses out the HWIDs to a database file, and rapidly scans the local machine's HWIDs, searches the database for matches, and installs them. PPK3 (Persistent Process Killer V3) - Another for the techs - suppress running processes that you need to keep away, helpful when fighting spyware/viruses. Sync Tool - Folder sync tool with lots of real time information and several checking methods. USMT Front End - Front End for Microsoft's User State Migration Tool, including all files needed for USMT 3.01 and 4.01, 32 bit and 64 bit versions. Audit Tool - Computer audit tool to gather vital hardware, Windows, and Office information for IT managers and field techs. Capabilities include creating a customized site agent. CSV Viewer - Displays CSV files with automatic column sizing and font selection. Lines can also be copied to the clipboard for data extraction. MyDirStat - Lists number and size of files on a drive or specified path, allows for deletion within the app. 2048 Game - My version of 2048, fun tile game. Juice Lab - Ecigarette liquid making calculator. Data Protector - Secure notes to save sensitive information. VHD Footer - Add a footer to a forensic hard drive image to allow it to be mounted or used as a virtual machine hard drive. Find in File - Searches files containing a specified phrase. Link to comment Share on other sites More sharing options...
slaughter Posted March 10, 2014 Author Share Posted March 10, 2014 Made some more tweaks late last night, I won't post a full update since other things are no doubt being changed. Updates, you'll know where to put them: $Button1 = GUICtrlCreateButton("Block", 2, 55, 75, 15) $Button2 = GUICtrlCreateButton("UnBlock", 80, 55, 75, 15) $Button3 = GUICtrlCreateButton("Geolocate", 158, 55, 75, 15) Case $Button3 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) _GeoLocate($sSelect) MemoWrite("Geolocated " & $sSelect) Func _GeoLocate($ip) ShellExecute("http://www.infosniper.net/index.php?ip_address=" & $ip & "&map_source=1&overview_map=1&lang=1&map_type=1&zoom_level=7") EndFunc ;==>_GeoLocate Helpful for determining if you really want to block an unknown address manually. Ian Your code added to script. Relay great idea! As for an auto-mode, would be nice to add a timestamp to the database of when the IP was blocked, and on each new scheduled scan look at all blocked IPs and count the seconds since the block, and if a set amount of time has passed like 5 minutes then set to unblock and remove the IP from the database. Whats about auto-mode I haven't tested it yet so cant say how it will work. About time stamp it was already done, check updated script in first post, if you will look table blocked_ip_list contains date_from and date_to so it basically is time stamps. Date_from = date when ip was added to block list, date_to when it should be removed. And function block_ip_remove shuld remove any ip's than curent date >= date_to. Also, for the scans, you could use the highest EventID for where to start looking for new events to avoid duplicates. About scan. Duplicates cant occur because I use EventRecordID as unique so record cant be inserted, but for performance it can be next step for improvement. In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
slaughter Posted March 10, 2014 Author Share Posted March 10, 2014 (edited) Ah! I'm a bit late it seems. I've worked on the first version only. Anyway, look at what I came up with: you can remove your original code included under the If 0 Then branch. The progress bar doesn't make much sense with small files. expandcollapse popup#RequireAdmin #include <GUIConstantsEx.au3> #include <Constants.au3> #include <Array.au3> #include <File.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> Global $iMemo _Main() Func _Main() Local $hEventLog, $aEvent Global $SQLite_db ; Create GUI GUICreate("EventLog", 604, 604) $Progress1 = GUICtrlCreateProgress(8, 25, 590, 25) $Label1 = GUICtrlCreateLabel("Reading event log", 8, 8, 430, 17) $iMemo = GUICtrlCreateEdit("", 2, 60, 600, 542) GUICtrlSetFont($iMemo, 9, 400, 0, "Courier New") GUISetState() $read_events = 5000 _SQLite_Startup() MemoWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) $db_name = @ScriptDir & "\SQLite_v2.db" If FileExists($db_name) Then MemoWrite("Opening existing database. "& $db_name) $SQLite_db = _SQLite_Open($db_name) Else MemoWrite("Creating new database at: "& $db_name) $SQLite_db = _SQLite_Open($db_name) MemoWrite("Creating database structure.") _SQLite_Exec($SQLite_db, "CREATE TABLE ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT, log_id CHAR UNIQUE, date CHAR, ip CHAR, user CHAR)") EndIf GUICtrlSetData($Label1, "Reading event log from windows") MemoWrite("Generating event log from windows") GUICtrlSetData($Progress1, 1) $cmd = 'wevtutil qe Security "/q:*[System[band(Keywords,8010000000000000)]]" /c:'& $read_events &' /rd:true /f:xml>C:\wevtutil.xml' FileDelete ( "c:\wevtutil.xml" ) Local $foo = RunWait(@ComSpec & ' /c '& $cmd, @SystemDir, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) ;$data = FileRead('C:\wevtutil.xml') ;FileDelete ( "c:\wevtutil.xml" ) ;MemoWrite($data) ;GUICtrlSetData($Label1, "Reading event log: "& $read_events &"/" & $read_events) GUICtrlSetData($Label1, "Reading event log done.") MemoWrite("Generating event log done.") MemoWrite("Reading event log.") If 0 Then ; Define a variable to pass to _FileReadToArray. Local $aArray = 0 ; Read the current script file into an array using the variable defined previously. If Not _FileReadToArray("wevtutil.xml", $aArray, 0) Then MsgBox($MB_SYSTEMMODAL, "", "There was an error reading the file. @error: " & @error) ; An error occurred reading the current script file. EndIf ; Display the array in _ArrayDisplay. ;_ArrayDisplay($aArray) MemoWrite("Reading event log done.") MemoWrite("Phrasing event log.") $total_records = UBound($aArray) Local $IPArray[0][5] Local $sql_qry = "" For $vElement In $aArray $size = UBound($IPArray) + 1 ReDim $IPArray[$size][5] $ix = $size -1 $percent = Round(100 / $total_records * $size) GUICtrlSetData($Progress1, $percent) GUICtrlSetData($Label1, "Phrasing event log: "& $ix &"/" & $total_records) $date = "<TimeCreated SystemTime='(.*?)'/>" $array = StringRegExp($vElement, $date, 1) If IsArray($array) Then $IPArray[$ix][1] = $array[0] Else $IPArray[$ix][1] = "none" EndIf $EventID = "<EventRecordID>(.*?)</EventRecordID>" $array = StringRegExp($vElement, $EventID, 1) If IsArray($array) Then $IPArray[$ix][0] = $array[0] Else $IPArray[$ix][0] = "none" EndIf $network = "<Data Name='IpAddress'>(.*?)</Data>" $array = StringRegExp($vElement, $network, 1) If IsArray($array) Then $IPArray[$ix][2] = $array[0] Else $IPArray[$ix][2] = "none" EndIf $account = "<Data Name='TargetUserName'>(.*?)</Data>" $array = StringRegExp($vElement, $account, 1) If IsArray($array) Then ;_ArrayDisplay($array) $IPArray[$ix][3] = $array[0] Else $IPArray[$ix][3] = "none" EndIf $sql_qry = "INSERT OR IGNORE INTO ip_list VALUES (null, '"& $IPArray[$ix][0] &"', '"& $IPArray[$ix][1] &"', '"& $IPArray[$ix][2] &"', '"& $IPArray[$ix][3] &"');"& @CRLF & $sql_qry ;MemoWrite($sql_qry) ;~ If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then ;~ MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) ;~ EndIf Next MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf MemoWrite("Inserting data to SQLite done.") Else Local $rawdata = FileRead("wevtutil1.xml") ; ##### reading supplied file directly ! Local $captures = StringRegExp($rawdata, "(?i)<TimeCreated SystemTime='(.*?)'/><EventRecordID>(.*?)</EventRecordID>.*?<Data Name='TargetUserName'>(.*?)</Data>.*?<Data Name='IpAddress'>(.*?)</Data>", 3) MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") ;~ _ArrayDisplay($captures) If IsArray($captures) Then Local $sql If Not _SQLite_Exec($SQLite_db, "begin;") = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf For $i = 0 To (UBound($captures) / 4) - 1 $sql &= "('" & $captures[4 * $i] & "', '" & $captures[4 * $i + 1] & "', '" & $captures[4 * $i + 2] & "', '" & $captures[4 * $i + 3] & "')," If Mod($i + 1, 500) = 0 Or $i = (UBound($captures) / 4) - 1 Then $sql = "INSERT OR IGNORE INTO ip_list (date, log_id, user, ip) VALUES " & StringTrimRight($sql, 1) If Not _SQLite_Exec($SQLite_db, $sql) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf $sql = "" EndIf Next If Not _SQLite_Exec($SQLite_db, "commit;") = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf EndIf MemoWrite("Inserting data to SQLite done.") EndIf MemoWrite("IP address, failed authentication tires") display_resutls("SELECT ip, count(*) as 'Cnt' FROM ip_list GROUP BY ip;") MemoWrite("Username, failed authentication tires") display_resutls("SELECT user, count(*) as 'Cnt' FROM ip_list GROUP BY user;") ; Loop until user exits Do Until GUIGetMsg() = $GUI_EVENT_CLOSE EndFunc ;==>_Main Func display_resutls($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval Global $SQLite_db $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDisplay($aResult, "Query Result") $iRows = UBound($aResult)-1 $iCols = UBound($aResult, 2)-1 For $i = 1 To $iRows $string = "" For $x = 0 To $iCols $string = $string & $aResult[$i][$x] & " " Next MemoWrite($string) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc ; Write a line to the memo control Func MemoWrite($sMessage) GUICtrlSetData($iMemo, $sMessage & @CRLF, 1) EndFunc ;==>MemoWrite What I've done is very simple: read the input in one shot, regexp all data at once in a 1D array and group 500 row values per insert, all in one transaction. Just now tested your code. And you know... IT IS ASS KICKING expandcollapse popup#RequireAdmin #include <GUIConstantsEx.au3> #include <Constants.au3> #include <Array.au3> #include <File.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <GuiListView.au3> #include <GuiImageList.au3> #include <WindowsConstants.au3> #include <ButtonConstants.au3> #include <ComboConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <ListViewConstants.au3> #include <ProgressConstants.au3> #include <StaticConstants.au3> #include <TabConstants.au3> #include <WindowsConstants.au3> #include <Date.au3> Global $iMemo _Main() Func _Main() Local $hEventLog, $aEvent Global $SQLite_db,$hListView,$hListView2,$GUI,$wevtutil_query, $initial_event_count, $Label1, $Progress1, $sql_qry, $minutes_in_ban_list $GUI = GUICreate("Smart Windows firewall control", 621, 436, 450, 295, BitOR($GUI_SS_DEFAULT_GUI,$WS_MAXIMIZEBOX,$WS_SIZEBOX,$WS_THICKFRAME,$WS_TABSTOP)) $Tab1 = GUICtrlCreateTab(8, 8, 601, 417) GUICtrlSetResizing(-1, $GUI_DOCKLEFT+$GUI_DOCKRIGHT+$GUI_DOCKTOP+$GUI_DOCKBOTTOM+$GUI_DOCKWIDTH+$GUI_DOCKHEIGHT) $TabSheet1 = GUICtrlCreateTabItem("Log entrys") $iMemo = GUICtrlCreateEdit("", 16, 88, 585, 329) GUICtrlSetData(-1, "Edit1") $Progress1 = GUICtrlCreateProgress(16, 36, 582, 17) $Label1 = GUICtrlCreateLabel("Reading event log", 16, 61, 584, 17) $TabSheet2 = GUICtrlCreateTabItem("Blocked IP") $hListView2 = GUICtrlCreateListView("", 12, 41, 586, 350) _GUICtrlListView_SetExtendedListViewStyle($hListView2, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES)) _GUICtrlListView_InsertColumn($hListView2, 0, "IP Address", 150) _GUICtrlListView_InsertColumn($hListView2, 1, "From", 130) _GUICtrlListView_InsertColumn($hListView2, 2, "To", 130) _GUICtrlListView_InsertColumn($hListView2, 3, "Status", 100) $Button1 = GUICtrlCreateButton("Unblock", 12, 393, 99, 25) $Checkbox1 = GUICtrlCreateCheckbox("Auto block", 204, 393, 73, 25) $atempts = GUICtrlCreateInput("5", 284, 393, 25, 21) $minutes = GUICtrlCreateInput("1", 380, 393, 41, 21) GUICtrlCreateLabel("attempts in", 316, 393, 55, 17, $SS_CENTERIMAGE) GUICtrlCreateLabel("min. For", 428, 393, 41, 17, $SS_CENTERIMAGE) $Button2 = GUICtrlCreateButton("White list IP", 116, 393, 75, 25) $Input1 = GUICtrlCreateInput("1", 476, 393, 41, 21) $Label3 = GUICtrlCreateLabel("day's.", 524, 393, 31, 17, $SS_CENTERIMAGE) $TabSheet3 = GUICtrlCreateTabItem("Statistics") $Combo1 = GUICtrlCreateCombo("Total failed login count", 12, 41, 505, 25, BitOR($CBS_DROPDOWN,$CBS_AUTOHSCROLL)) GUICtrlSetData(-1, "Failed logins in 1 min|Failed logins in 1 hour|Failed logins in 1 day|Failed logins in 1 month|Failed logins in 1 year|Failed logins all time|Failed username"&Chr(39)&"s in 1 day|Failed username"&Chr(39)&"s in 1 month|") $Button3 = GUICtrlCreateButton("Block", 12, 393, 75, 25) $Button4 = GUICtrlCreateButton("UnBlock", 92, 393, 75, 25) $Button6 = GUICtrlCreateButton("Geolocate", 92+80, 393, 75, 25) $hListView = GUICtrlCreateListView("", 12, 65, 586, 326) _GUICtrlListView_SetExtendedListViewStyle($hListView, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES)) _GUICtrlListView_InsertColumn($hListView, 0, "IP Address", 150) _GUICtrlListView_InsertColumn($hListView, 1, "Count", 100) _GUICtrlListView_InsertColumn($hListView, 2, "Blocked", 100) _GUICtrlListView_InsertColumn($hListView, 3, "data", 100) $Button5 = GUICtrlCreateButton("Load", 520, 41, 79, 21) GUICtrlCreateTabItem("") GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### ; Create GUI ;~ $GUI = GUICreate("EventLog", 604, 604) ;~ $Progress1 = GUICtrlCreateProgress(8, 25, 590, 25) ;~ $Label1 = GUICtrlCreateLabel("Reading event log", 8, 8, 430, 17) ;~ $iMemo = GUICtrlCreateEdit("", 2, 360, 600, 242) ;~ $hListView = _GUICtrlListView_Create($GUI, "", 2, 75, 600, 280) ;~ _GUICtrlListView_InsertColumn($hListView, 1, "IP Address", 150) ;~ _GUICtrlListView_InsertColumn($hListView, 2, "Count", 50) ;~ $Button1 = GUICtrlCreateButton("Block", 2, 55, 75, 15) ;~ $Button2 = GUICtrlCreateButton("UnBlock", 80, 55, 75, 15) ;$Button3 = GUICtrlCreateButton("exit", 2, 55, 75, 15) GUICtrlSetFont($iMemo, 9, 400, 0, "Courier New") GUISetState() $initial_event_count = 10000 $update_event_count = 100 $minutes_in_ban_list = 2 _SQLite_Startup() MemoWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) GUICtrlSetData($Progress1, 1) $wevtutil_query = "*[System[band(Keywords,8010000000000000)]]" $db_name = @ScriptDir & "\SQLite_v4.db" If FileExists($db_name) Then $wevtutil_query = "*[System[band(Keywords,8010000000000000) and TimeCreated[timediff(@SystemTime) < 3600000]]]" MemoWrite("Opening existing database. "& $db_name) $SQLite_db = _SQLite_Open($db_name) $initial_event_count = $update_event_count Else $wevtutil_query = "*[System[band(Keywords,8010000000000000)]]" MemoWrite("Creating new database at: "& $db_name) $SQLite_db = _SQLite_Open($db_name) MemoWrite("Creating database structure.") _SQLite_Exec($SQLite_db, "CREATE TABLE ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT,log_id CHAR(150) UNIQUE, date CHAR(150), ip CHAR(150), user CHAR(150), status CHAR(150))") _SQLite_Exec($SQLite_db, "CREATE TABLE blocked_ip_list (ID INTEGER PRIMARY KEY AUTOINCREMENT,ip CHAR(150), date_from DATETIME, date_to DATETIME, status CHAR(150))") EndIf Update_database() AdlibRegister(sheduled_tasks, 1000*10) ;~ MemoWrite("IP address, failed authentication tires") ;~ display_resutls("SELECT ip, count(*) as 'Cnt' FROM ip_list GROUP BY ip;") ;~ MemoWrite("Username, failed authentication tires") ;~ display_resutls("SELECT user, count(*) as 'Cnt' FROM ip_list GROUP BY user;") $per_sec = @YEAR &"-"& @MON &"-"& @MDAY &"T"& @HOUR&":"& @MIN&":"& @SEC $per_min = @YEAR &"-"& @MON &"-"& @MDAY &"T"& @HOUR&":"& @MIN $per_hour = @YEAR &"-"& @MON &"-"& @MDAY &"T"& @HOUR $per_day = @YEAR &"-"& @MON &"-"& @MDAY ;~ MemoWrite("Login attempts: per sec") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_sec&"%' GROUP BY ip ORDER by count DESC;") ;~ MemoWrite("Login attempts: per min") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_min&"%' GROUP BY ip ORDER by count DESC;") ;~ MemoWrite("Login attempts: per hour") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_hour&"%' GROUP BY ip ORDER by count DESC;") ;~ MemoWrite("Login attempts: per day") ;~ display_resutls("SELECT ip, count(*) as 'count' FROM ip_list WHERE date LIKE '"&$per_day&"%' GROUP BY ip ORDER by count DESC;") display_resutls("SELECT ip, count(*) as 'count', status FROM ip_list GROUP BY ip ORDER by count DESC;") display_blocked_resutls("SELECT ip, date_from, date_to, status FROM blocked_ip_list ORDER by status") ; Loop until user exits While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit Case $Button1 $iSelect = ControlListView($GUI, "", $hListView2, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView2, "GetText", $iSelect) MemoWrite("Address: "&$sSelect& " is removed from firewal") block_ip_remove($sSelect) Case $Button3 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) MemoWrite("Address: "&$sSelect& " is added to firewal for blocking") block_ip_add($sSelect,'525600') ; block select ip for a year 365*24*60 Case $Button4 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) MemoWrite("Address: "&$sSelect& " is removed from firewal") block_ip_remove($sSelect) Case $Button5 $sComboRead = GUICtrlRead($Combo1) Case $Button6 $iSelect = ControlListView($GUI, "", $hListView, "GetSelected") $sSelect = ControlListView($GUI, "", $hListView, "GetText", $iSelect) _GeoLocate($sSelect) MemoWrite("Geolocated " & $sSelect) EndSwitch WEnd EndFunc ;==>_Main Func sheduled_tasks() Global $Checkbox1 display_resutls("SELECT ip, count(*) as 'count', status FROM ip_list GROUP BY ip ORDER by count DESC;") display_blocked_resutls("SELECT ip, date_from, date_to, status FROM blocked_ip_list ORDER by status") Update_database() if _IsChecked($Checkbox1) Then $attempts_time=GUICtrlRead($minutes) $attempts_count=GUICtrlRead($atempts) auto_ban("SELECT ip, COUNT(*) as 'count' FROM ip_list WHERE date >= date('now','-"&$attempts_time&" minutes') AND count >= "&$attempts_count&" AND status!='Blocked' GROUP BY ip ORDER by count DESC") auto_ban_remove("SELECT ip FROM blocked_ip_list WHERE date_to >= date('now') AND status='Blocked'") EndIf EndFunc Func block_ip_add($WhatIP, $minutes_in_ban_list = 60) RunWait(@ComSpec & ' /c '& "netsh advfirewall firewall add rule name=[logBlock" & $WhatIP & "] dir=in interface=any action=block remoteip=" & $WhatIP, "", @SW_HIDE) ;blocked_ip_list (ID ,ip, date_from, date_to, active) SQLite_QUERY("INSERT INTO blocked_ip_list VALUES (null, '"& $WhatIP &"', datetime('now'), datetime('now', '+"& $minutes_in_ban_list &" minutes'), 'Blocked');") SQLite_QUERY("UPDATE ip_list SET status='Blocked' WHERE ip='"& $WhatIP &"';") EndFunc Func block_ip_remove($WhatIP) RunWait(@ComSpec & ' /c '& "netsh advfirewall firewall delete rule name=[logBlock" & $WhatIP & "] dir=in", "", @SW_HIDE) SQLite_QUERY("UPDATE blocked_ip_list SET status='Unblocked' WHERE ip='"& $WhatIP &"' AND status='Blocked';") SQLite_QUERY("UPDATE ip_list SET status='Unblocked' WHERE ip='"& $WhatIP &"' AND status='Blocked';") EndFunc Func Update_database() Global $wevtutil_query, $initial_event_count, $Label1 GUICtrlSetData($Label1, "Reading event log from windows") MemoWrite("Generating event log from windows") $cmd = 'wevtutil qe Security "/q:'& $wevtutil_query &'" /c:'& $initial_event_count &' /rd:true /f:xml>C:\wevtutil.xml' $wevtutil_query = "*[System[band(Keywords,8010000000000000) and TimeCreated[timediff(@SystemTime) < 3600000]]]" ;after initail run dont read all log only 24 h ;~ MemoWrite($cmd) FileDelete ( "c:\wevtutil.xml" ) Local $foo = RunWait(@ComSpec & ' /c '& $cmd, @SystemDir, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) GUICtrlSetData($Label1, "Reading event log done.") MemoWrite("Generating event log done.") Global $Label1, $Progress1, $sql_qry If 0 Then MemoWrite("OLD MODE DATA UPDATE") MemoWrite("Reading event log.") Local $aArray = 0 If Not _FileReadToArray("C:\wevtutil.xml", $aArray,0) Then ;MsgBox($MB_SYSTEMMODAL, "", "There was an error reading the file. @error: " & @error) ; An error occurred reading the current script file. MemoWrite("!!Warning no events in last 24h, or cant generate event log file at all.") EndIf MemoWrite("Reading event log done.") If IsArray($aArray) Then MemoWrite("Phrasing event log.") $total_records = UBound($aArray) Local $IPArray[0][5] Local $sql_qry = "" For $vElement In $aArray $size = UBound($IPArray) + 1 ReDim $IPArray[$size][5] $ix = $size -1 $percent = Round(100 / $total_records * $size) GUICtrlSetData($Progress1, $percent) GUICtrlSetData($Label1, "Phrasing event log: "& $ix &"/" & $total_records) $date = "<TimeCreated SystemTime='(.*?)'/>" $array = StringRegExp($vElement, $date, 1) If IsArray($array) Then $IPArray[$ix][1] = $array[0] Else $IPArray[$ix][1] = "none" EndIf $EventID = "<EventRecordID>(.*?)</EventRecordID>" $array = StringRegExp($vElement, $EventID, 1) If IsArray($array) Then $IPArray[$ix][0] = $array[0] Else $IPArray[$ix][0] = "none" EndIf $network = "<Data Name='IpAddress'>(.*?)</Data>" $array = StringRegExp($vElement, $network, 1) If IsArray($array) Then $IPArray[$ix][2] = $array[0] Else $IPArray[$ix][2] = "none" EndIf $account = "<Data Name='TargetUserName'>(.*?)</Data>" $array = StringRegExp($vElement, $account, 1) If IsArray($array) Then ;_ArrayDisplay($array) $IPArray[$ix][3] = $array[0] Else $IPArray[$ix][3] = "none" EndIf $sql_qry = "INSERT OR IGNORE INTO ip_list VALUES (null, '"& $IPArray[$ix][0] &"', '"& $IPArray[$ix][1] &"', '"& $IPArray[$ix][2] &"', '"& $IPArray[$ix][3] &"', '');"& @CRLF & $sql_qry Next MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") $sql_qry = "begin;" & @CRLF & $sql_qry & @CRLF & "commit;" If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf MemoWrite("Inserting data to SQLite done.") Else MemoWrite("No data fo phrasing and inserting to SQLite.") GUICtrlSetData($Progress1, 100) EndIf Else MemoWrite("NEW MODE DATA UPDATE") MemoWrite("Reading event log.") Local $rawdata = FileRead("c:\wevtutil.xml") ; ##### reading supplied file directly ! MemoWrite("Reading event log done.") MemoWrite("Phrasing event log.") Local $captures = StringRegExp($rawdata, "(?i)<TimeCreated SystemTime='(.*?)'/><EventRecordID>(.*?)</EventRecordID>.*?<Data Name='TargetUserName'>(.*?)</Data>.*?<Data Name='IpAddress'>(.*?)</Data>", 3) MemoWrite("Phrasing event log done.") MemoWrite("Inserting data to SQLite.") ;_ArrayDisplay($captures) If IsArray($captures) Then Local $sql If Not _SQLite_Exec($SQLite_db, "begin;") = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf For $i = 0 To (UBound($captures) / 4) - 1 $sql &= "('" & $captures[4 * $i] & "', '" & $captures[4 * $i + 1] & "', '" & $captures[4 * $i + 2] & "', '" & $captures[4 * $i + 3] & "')," If Mod($i + 1, 500) = 0 Or $i = (UBound($captures) / 4) - 1 Then $sql = "INSERT OR IGNORE INTO ip_list (date, log_id, user, ip) VALUES " & StringTrimRight($sql, 1) If Not _SQLite_Exec($SQLite_db, $sql) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf $sql = "" EndIf Next If Not _SQLite_Exec($SQLite_db, "commit;") = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf Else MemoWrite("No new event to read.") EndIf MemoWrite("Inserting data to SQLite done.") EndIf EndFunc Func display_resutls($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval Global $SQLite_db, $hListView $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDelete($aResult, 0) _GUICtrlListView_DeleteAllItems($hListView) _GUICtrlListView_AddArray($hListView, $aResult) ;_ArrayDisplay($aResult, "Query Result") ;~ $iRows = UBound($aResult)-1 ;~ $iCols = UBound($aResult, 2)-1 ;~ For $i = 1 To $iRows ;~ $string = "" ;~ For $x = 0 To $iCols ;~ $string = $string & $aResult[$i][$x] & " " ;~ Next ;~ MemoWrite($string) ;~ Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc Func display_blocked_resutls($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval Global $SQLite_db, $hListView2 $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then _ArrayDelete($aResult, 0) _GUICtrlListView_DeleteAllItems($hListView2) _GUICtrlListView_AddArray($hListView2, $aResult) ;_ArrayDisplay($aResult, "Query Result") ;~ $iRows = UBound($aResult)-1 ;~ $iCols = UBound($aResult, 2)-1 ;~ For $i = 1 To $iRows ;~ $string = "" ;~ For $x = 0 To $iCols ;~ $string = $string & $aResult[$i][$x] & " " ;~ Next ;~ MemoWrite($string) ;~ Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc Func auto_ban($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval, $minutes_in_ban_list Global $SQLite_db, $hListView $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ;_ArrayDelete($aResult, 0) ;_GUICtrlListView_DeleteAllItems($hListView) ;_GUICtrlListView_AddArray($hListView, $aResult) ;_ArrayDisplay($aResult, "Query Result") $iRows = UBound($aResult)-1 $iCols = UBound($aResult, 2)-1 For $i = 1 To $iRows block_ip_add($aResult[$i][0], $minutes_in_ban_list) MemoWrite("Auto blocking IP: " &$WhatIP& " until: "& $date_to) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc Func auto_ban_remove($sql) ;MemoWrite("Reading date from SQLite db.") Local $aResult, $iRows, $iColumns, $iRval, $minutes_in_ban_list Global $SQLite_db, $hListView $iRval = _SQLite_GetTable2d($SQLite_db, $sql, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ;_ArrayDelete($aResult, 0) ;_GUICtrlListView_DeleteAllItems($hListView) ;_GUICtrlListView_AddArray($hListView, $aResult) ;_ArrayDisplay($aResult, "Query Result") $iRows = UBound($aResult)-1 $iCols = UBound($aResult, 2)-1 For $i = 1 To $iRows block_ip_remove($aResult[$i][0]) ;blocked_ip_list (ID ,ip, date_from, date_to, active) MemoWrite("Auto blocking IP: " &$WhatIP& " REMOVED") Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf ;MemoWrite("Reading date from SQLite db done.") EndFunc Func SQLite_QUERY($sql_qry) Global $SQLite_db MemoWrite($sql_qry) If Not _SQLite_Exec($SQLite_db, $sql_qry) = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) EndIf EndFunc Func _IsChecked($iControlID) Return BitAND(GUICtrlRead($iControlID), $GUI_CHECKED) = $GUI_CHECKED EndFunc ;==>_IsChecked ; Write a line to the memo control Func MemoWrite($sMessage) $time = _Date_Time_SystemTimeToDateTimeStr(_Date_Time_GetLocalTime()) GUICtrlSetData($iMemo, $time &" -> "& $sMessage & @CRLF, 1) EndFunc ;==>MemoWrite Func _GeoLocate($ip) ;by llewxam ShellExecute("http://www.infosniper.net/index.php?ip_address=" & $ip & "&map_source=1&overview_map=1&lang=1&map_type=1&zoom_level=7") EndFunc ;==>_GeoLocate TEST RESULTS for jchd code (14 sec.) 03/11/2014 00:07:20 -> Creating new database at: C:\Users\slau\Desktop\SQLite_v4.db 03/11/2014 00:07:20 -> Creating database structure. 03/11/2014 00:07:20 -> Generating event log from windows 03/11/2014 00:07:22 -> Generating event log done. 03/11/2014 00:07:34 -> Generating event log done. 03/11/2014 00:07:34 -> NEW MODE DATA UPDATE 03/11/2014 00:07:34 -> Reading event log. 03/11/2014 00:07:34 -> Reading event log done. 03/11/2014 00:07:34 -> Phrasing event log. 03/11/2014 00:07:34 -> Phrasing event log done. 03/11/2014 00:07:34 -> Inserting data to SQLite. 03/11/2014 00:07:34 -> Inserting data to SQLite done. TEST RESULTS for MY OLD code ( ~240 sec.) 03/11/2014 00:10:20 -> Creating new database at: C:\Users\slau\Desktop\SQLite_v4.db 03/11/2014 00:10:20 -> Creating database structure. 03/11/2014 00:10:21 -> Generating event log from windows 03/11/2014 00:10:22 -> Generating event log done. 03/11/2014 00:10:22 -> OLD MODE DATA UPDATE 03/11/2014 00:10:22 -> Reading event log. 03/11/2014 00:10:23 -> Reading event log done. 03/11/2014 00:10:23 -> Phrasing event log. 03/11/2014 00:14:07 -> Phrasing event log done. 03/11/2014 00:14:07 -> Inserting data to SQLite. 03/11/2014 00:14:08 -> Inserting data to SQLite done. And the winner is!!!! jchd Great work. But one more testing these modes noticed that found count is not same in one ip Why? for this i atach bigger log if you wuld like to test LOGFILE pasword as before Edited March 10, 2014 by slaughter In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
llewxam Posted March 11, 2014 Share Posted March 11, 2014 (edited) Lots of variable errors on the above script: $minutes,$atempts,$WhatIP,$date_toAlso, error: _Date_Time_SystemTimeToDateTimeStr() called with Const or expression on ByRef-param(s).IanEdit:Fixed _Date_Time_SystemTimeToDateTimeStr() error by doing this:$Temp = _Date_Time_GetLocalTime() $time = _Date_Time_SystemTimeToDateTimeStr($Temp)Hmm, doesn't like being nested? Also, set globals for above variables, not yet tested but passed syntax check/compile.Edit2:Blocked IP tab uses a different time format and the wrong time than my local time, probably due to the same _Date_Time_SystemTimeToDateTimeStr issue, so my fix looks like it is not a fix....Edit3:Sorry for so many edits!! I love the White List idea, that was a concern I had a while ago and I'm glad you thought of it too. Also very thankful for the option to choose different types and time intervals for failed login statistics, looking forward to that functioning!!! Last request for now, how about setting the auto block interval to minutes? Edited March 11, 2014 by llewxam My projects: IP Scanner - Multi-threaded ping tool to scan your available networks for used and available IP addresses, shows ping times, resolves IPs in to host names, and allows individual IPs to be pinged. INFSniff - Great technicians tool - a tool which scans DriverPacks archives for INF files and parses out the HWIDs to a database file, and rapidly scans the local machine's HWIDs, searches the database for matches, and installs them. PPK3 (Persistent Process Killer V3) - Another for the techs - suppress running processes that you need to keep away, helpful when fighting spyware/viruses. Sync Tool - Folder sync tool with lots of real time information and several checking methods. USMT Front End - Front End for Microsoft's User State Migration Tool, including all files needed for USMT 3.01 and 4.01, 32 bit and 64 bit versions. Audit Tool - Computer audit tool to gather vital hardware, Windows, and Office information for IT managers and field techs. Capabilities include creating a customized site agent. CSV Viewer - Displays CSV files with automatic column sizing and font selection. Lines can also be copied to the clipboard for data extraction. MyDirStat - Lists number and size of files on a drive or specified path, allows for deletion within the app. 2048 Game - My version of 2048, fun tile game. Juice Lab - Ecigarette liquid making calculator. Data Protector - Secure notes to save sensitive information. VHD Footer - Add a footer to a forensic hard drive image to allow it to be mounted or used as a virtual machine hard drive. Find in File - Searches files containing a specified phrase. Link to comment Share on other sites More sharing options...
jchd Posted March 11, 2014 Share Posted March 11, 2014 Thanks for supplying a larger sample input. Nonetheless I don't find any discrepancy between the results of running your old code and mine. The only difference I can see is that I insert rows in the same order found in the input while you're inserting in the reverse order, hence rowids are not equal. To test I created a separate ip_listold table with the same DDL and loaded it with the (old) code in the If 0 Then branch. Then I used SQLite Expert (an absolute must have if you work with SQLite) to run each of the following SQL in turn: select log_id, date, ip, user from ip_list except select log_id, date, ip, user from ip_listold; SELECT ip, count(*) as 'Cnt' FROM ip_list GROUP BY ip except SELECT ip, count(*) as 'Cnt' FROM ip_listold GROUP BY ip; SELECT user, count(*) as 'Cnt' FROM ip_list GROUP BY user except SELECT user, count(*) as 'Cnt' FROM ip_listold GROUP BY user; All 3 above queries returned no rows. Thus I'm fairly confident that the code I provided doesn't change or ignore data from the input. I suspect what you experience is due to later modification in the code you use. I'm still based on the original posted code. If you have issues finding where things go astray with your new code let me know and I'll take a look. 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) Link to comment Share on other sites More sharing options...
kaesereibe Posted March 11, 2014 Share Posted March 11, 2014 (edited) On every servers 0 entries for 8010000000000000If the screenshots shows your servers, then its very poorly configured and definitely a security issue! Edited March 11, 2014 by jNizM BIN 2 DEC | ConvertTemp | DEC 2 BIN | GetWeekday | HEX 2 RGB | INT 2 HEX | QueryPerformance Link to comment Share on other sites More sharing options...
slaughter Posted March 11, 2014 Author Share Posted March 11, 2014 On every servers 0 entries for 8010000000000000 If the screenshots shows your servers, then its very poorly configured! On every servers 0 entries for 8010000000000000 If the screenshots shows your servers, then its very poorly configured! If you servers is in internal LAN and none of public services that supports event log (RDP, SQL) are exposed you will have 0 entries. Thats individual thing and it does not have anything with server configuration. Some of servers are protected by firewall configuration, but some of my clients does not want to buy firewalls, and uses ISP supplied. So thus would be good solution in this cases. In progress: Windows Server bruteforce GUARD Admin Tools (Remote client control) Bypasing firewall Old stuff: [font="Verdana;"]MD5 Auto Update Calendar XP SS multi usser server & client Autoit Remote Control (ARC)[/font] Link to comment Share on other sites More sharing options...
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