Jump to content
Sign in to follow this  
slaughter

Windows Server bruteforce GUARD

Recommended Posts

slaughter

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:

1img.png

2img.png

3img.png

3img.png

4img.png

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)

#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 by slaughter
  • Like 1

Share this post


Link to post
Share on other sites
llewxam

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.

Share this post


Link to post
Share on other sites
slaughter

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.

Share this post


Link to post
Share on other sites
jchd

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 here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

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

Share this post


Link to post
Share on other sites
slaughter

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 by slaughter

Share this post


Link to post
Share on other sites
jchd

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 here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

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

Share this post


Link to post
Share on other sites
jchd

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.

#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 by jchd
  • Like 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 here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

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

Share this post


Link to post
Share on other sites
llewxam

I don't want to make a fork in your code, but I made some changes:

 

#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

Edited 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.

Share this post


Link to post
Share on other sites
slaughter

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.

#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?

Share this post


Link to post
Share on other sites
slaughter

I don't want to make a fork in your code, but I made some changes:

 

#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

Share this post


Link to post
Share on other sites
jchd

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 here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

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

Share this post


Link to post
Share on other sites
llewxam

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. 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 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.

Share this post


Link to post
Share on other sites
slaughter

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.

Share this post


Link to post
Share on other sites
slaughter

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.

#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

 

#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?

Untitledbbbb.jpg

for this i atach bigger log if you wuld like to test LOGFILE pasword as before

Edited by slaughter

Share this post


Link to post
Share on other sites
llewxam

Lots of variable errors on the above script: $minutes,$atempts,$WhatIP,$date_to

Also, error: _Date_Time_SystemTimeToDateTimeStr() called with Const or expression on ByRef-param(s).

Ian

Edit:

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 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.

Share this post


Link to post
Share on other sites
jchd

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 here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

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

Share this post


Link to post
Share on other sites
slaughter

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

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

Create an account

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

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×