Report on the launch of programs on users' computers
Hello!
The report that the user runs on his computer is extremely important. From many points of view. Especially in terms of information security.
Information about the launch of programs on users' computers is stored in the security log. Of course, considered the Windows environment. I did not find a ready solution in the Internet, so I made my implementation.
The script runs on the server. At the output we have a set of files with reports on the launch of programs.
Picture to attract attention:
The basic idea is this. Current security log events are saved in the evt file on the client computer. The file is copied to the server, where information from it is uploaded to SQL Server. Then a SQL query generates a report and saves it to a file.
Now how this is implemented.
You must create the Log, Logs, CheckComps, Logi_ForReports, and Computer folders. I have folders on drive F. In the Log folder, create a list.txt file with a list of computers that need to be checked. Each computer name with a new line. I created 2 files list.txt and list7.txt for XP and sevens, respectively. In the Computer folder create the file is_computer_online_listComps.vbs
The check procedure is started with a bat file. A link to it can be made, for example, on the desktop.
The main script is_computer_online_listComps.vbs reads the list of computers from a text file and runs a report generation bat file for each. For XP, it is the is_computer_online.bat file, for 7 it is is_computer_online7.bat.
Note.
On the server, you need to install logparser .
Everything described should work on the administrator’s computer. Only need to install Microsoft SQL SERVER 2008 NATIVE CLIENT and Microsoft SQL Server 2008 Command Line Utilities. But I did not check.
Bat file:
Bat-file runs the script. The script saves the security log events to an evt-file and starts the main batch file mo2csv.bat.
mo2csv.bat does the following:
Bat file:
Bat-file runs the script. The script saves the security log events to the evt-file and runs the main mo7.bat batch file.
mo7.bat does the following:
')
On SQL Server, you need to create the FU_GET_FULL_QTY_TEST function:
Archive with scripts can be downloaded here .
I know, it seems a lot of batch files and scripts. But it’s enough to set up once and use later
Update.
EventID 592 to create a process, 593 to complete.
Audit settings.
A piece from the secsetup.inf file:
EventID 4688 to create a process, 4689 to complete.
Audit settings.
For Russian:
For English:
Of course, if there is a domain, the audit settings should be specified in the domain policies.
And who does how to report on the launch of programs on users' computers? Share it.
The report that the user runs on his computer is extremely important. From many points of view. Especially in terms of information security.
Information about the launch of programs on users' computers is stored in the security log. Of course, considered the Windows environment. I did not find a ready solution in the Internet, so I made my implementation.
The script runs on the server. At the output we have a set of files with reports on the launch of programs.
Picture to attract attention:
The basic idea is this. Current security log events are saved in the evt file on the client computer. The file is copied to the server, where information from it is uploaded to SQL Server. Then a SQL query generates a report and saves it to a file.
Now how this is implemented.
You must create the Log, Logs, CheckComps, Logi_ForReports, and Computer folders. I have folders on drive F. In the Log folder, create a list.txt file with a list of computers that need to be checked. Each computer name with a new line. I created 2 files list.txt and list7.txt for XP and sevens, respectively. In the Computer folder create the file is_computer_online_listComps.vbs
The contents of the is_computer_online_listComps.vbs file:
on error resume next dim gsFileName dim gsRunCmd dim gix dim giy dim giz if Wscript.Arguments.Count = 1 then gsFileName = Wscript.Arguments(0) gsOS = "XP" elseif Wscript.Arguments.Count = 2 then gsFileName = Wscript.Arguments(0) gsOS = Wscript.Arguments(1) else gsFileName = InputBox(" ", "", "F:\Log\list.txt") gsOS = InputBox(" :" & VBNewLine & "'XP' - Windows 2000/XP" & VBNewLine & "'7' - Windows 7", "", "XP") end if gsOS = uCase(gsOS) wscript.echo "gsOS: " & gsOS if inStr(gsOS, "XP") = 0 and inStr(gsOS, "7") = 0 then MsgBox " !", vbInformation, "" Wscript.Quit end if WScript.Echo " : " & gsFileName Set objFSO = CreateObject("Scripting.FileSystemObject") Set objTextFileOpen = objFSO.OpenTextFile(gsFileName, 1) gix = 0 giy = 0 Set WshShell = CreateObject("WScript.Shell") do until objTextFileOpen.AtEndOfStream gsComputerName = objTextFileOpen.Readline giy = giy + 1 loop objTextFileOpen.Close wscript.echo " : " & giy Set objTextFileOpen = objFSO.OpenTextFile(gsFileName, 1) do until objTextFileOpen.AtEndOfStream gsComputerName = objTextFileOpen.Readline gix = gix + 1 giz = gix * 100 giz = giz / giy giz = Round(giz, 1) giOst = giy - gix if fuPing(gsComputerName) then wscript.echo gsComputerName & VBTab & " : " & giOst & ", : " & giz & "%" if inStr(gsOS, "XP") then gsRunCmd = "f:\Computer\is_computer_online.bat " & gsComputerName & " y" elseif inStr(gsOS, "7") then gsRunCmd = "f:\Computer\is_computer_online7.bat " & gsComputerName & " y" end if WshShell.Run gsRunCmd if giOst <> 0 then WScript.Sleep 180000 ' ! 180 . end if else wscript.echo gsComputerName & VBTab & " : " & giOst & ", : " & giz & "%. ." end if loop objTextFileOpen.Close WScript.Echo " !" function fuPing(NetworkDevice) lBoo = false set objPING = GetObject("winmgmts:{impersonationLevel=impersonate}")._ ExecQuery ("select * from Win32_PingStatus where address ='" & NetworkDevice & "'") For Each PING In objPing if PING.StatusCode = 0 then 'WScript.Echo "* " & NetworkDevice & " !" lBoo = true else 'WScript.Echo "* ." end if next fuPing = lBoo end function The check procedure is started with a bat file. A link to it can be made, for example, on the desktop.
bat file
cscript //nologo "f:\Computer\is_computer_online_listComps.vbs" %1 %2 The main script is_computer_online_listComps.vbs reads the list of computers from a text file and runs a report generation bat file for each. For XP, it is the is_computer_online.bat file, for 7 it is is_computer_online7.bat.
Note.
On the server, you need to install logparser .
Everything described should work on the administrator’s computer. Only need to install Microsoft SQL SERVER 2008 NATIVE CLIENT and Microsoft SQL Server 2008 Command Line Utilities. But I did not check.
XP computer unit
Bat file:
is_computer_online.bat
cscript //nologo "f:\Computer\is_computer_online.vbs" %1 %2 Bat-file runs the script. The script saves the security log events to an evt-file and starts the main batch file mo2csv.bat.
is_computer_online.vbs
on error resume next dim gsComputerName dim gsUseLogFile dim gsLogFilename dim gbFlag dim gsTableName dim gsCompName dim gsRunCmd if Wscript.Arguments.Count = 1 then gsComputerName = Wscript.Arguments(0) gsUseLogFile = "n" elseif Wscript.Arguments.Count = 2 then gsComputerName = Wscript.Arguments(0) gsUseLogFile = Wscript.Arguments(1) else gsComputerName = InputBox(" ", "", "") gsUseLogFile = InputBox(" log- ?" & VBNewline & "[y/n]", "", "y") end if WScript.Echo "* " & gsComputerName gsLogFilename = "f:\Log\" & gsComputerName & ".log" if lCase(gsUseLogFile) = "y" then gbFlag = false WScript.Echo "* " & gsLogFilename set objFSO = CreateObject("Scripting.FileSystemObject") if not objFSO.FileExists(gsLogFilename) then WScript.Echo "* . ..." set objTextFileWriteLog = objFSO.OpenTextFile(gsLogFilename, 8, True) objTextFileWriteLog.writeLine "n" objTextFileWriteLog.close WScript.Echo "* ." end if set objTextFileOpen = objFSO.OpenTextFile(gsLogFilename, 1) do until objTextFileOpen.AtEndOfStream record = trim(objTextFileOpen.Readline) if record = "n" then WScript.Echo "* ." if fuPing(gsComputerName) then gbFlag = true if fuBackup(gsComputerName) then WScript.Sleep 15000 ' <- 15 fuUploadEvents gsComputerName wscript.sleep 10000 end if end if elseif record = "y" then WScript.Echo "* " & gsComputerName & " ." else WScript.Echo "* " & gsComputerName & " log-." end if loop objTextFileOpen.close if gbFlag then set objTextFileWriteLog = objFSO.OpenTextFile(gsLogFilename, 2, True) objTextFileWriteLog.writeLine "y" objTextFileWriteLog.close WScript.Echo "* ." end if else 'if fuPing(gsComputerName) then if fuBackup(gsComputerName) then WScript.Sleep 15000 ' <- 15 fuUploadEvents gsComputerName wscript.sleep 10000 end if 'end if end if wscript.sleep 1000 function fuPing(NetworkDevice) lBoo = false set objPING = GetObject("winmgmts:{impersonationLevel=impersonate}")._ ExecQuery ("select * from Win32_PingStatus where address ='" & NetworkDevice & "'") For Each PING In objPing if PING.StatusCode = 0 then WScript.Echo "* " & NetworkDevice & " !" lBoo = true else WScript.Echo "* ." end if next fuPing = lBoo end function function fuBackup(lsComputername) lsEvtBackupFilename = "c:\" & lsComputername & ".evt" lsEvtBackupFilenameRemote = "\\" & lsComputername & "\c$\" & lsComputername & ".evt" lbFlag = false set lObjFSO = CreateObject("Scripting.FileSystemObject") if lObjFSO.FileExists(lsEvtBackupFilenameRemote) then WScript.Echo "* . ..." lbFlag = true else Wscript.Echo "* ..." Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Backup)}!\\" & lsComputername & "\root\cimv2") Set colLogFiles = objWMIService.ExecQuery ("Select * from Win32_NTEventLogFile where LogFileName='Security'") For Each objLogfile in colLogFiles errBackupLog = objLogFile.BackupEventLog(lsEvtBackupFilename) If errBackupLog = 0 Then Wscript.Echo "* ." lbFlag = true Else Wscript.Echo "* ." End If Next end if fuBackup = lbFlag end function function fuUploadEvents(lsComputername) WScript.Echo "* ..." gsCompName = lCase(lsComputername) gsTableName = fuGetTableName(gsCompName) gsTableName = uCase(gsTableName) gsOutputFilename = "f:\Computer\" & gsCompName & ".csv" gsOutputFilenameSQL = "f:\Computer\" & gsCompName & "_sql.csv" Set WshShell = CreateObject("WScript.Shell") gsRunCmd = "f:\Computer\mo2csv.bat " & gsCompName & " " & gsOutputFilename & " " & gsOutputFilenameSQL & " " & gsTableName WScript.Echo "* : '" & gsRunCmd & "'" WshShell.Run gsRunCmd end function function fuGetTableName(lsCompName) lsTmp = lsCompName if InStr(lsTmp, "-") then lsTmp = Replace(lsTmp, "-", "_") end if fuGetTableName = lsTmp end function mo2csv.bat does the following:
- It takes the evt-file from the remote computer to the server.
- Converts evt file to evtx.
- It uploads only the start / stop program events from the evtx file to the text csv file.
- The information from the text file downloads to SQL Server.
- Backs up the original evt-file to the Logi_ForReports folder (all of a sudden the user will erase his log, and we have a copy).
- Deletes a temporary evtx file.
- Generates and executes sql query to SQL Server.
- Removes temporary files (in the case of debugging or to study the work of the script, this section can be commented out).
- Moves reports to the CheckComps folder.
mo2csv.bat
Perhaps, in the batch file you will need to replace SQLCMD.EXE with “c: \ Program Files \ Microsoft SQL Server \ 100 \ Tools \ Binn \ SQLCMD.EXE”, and LogParser.exe with “c: \ Program Files (x86) \ Log Parser 2.2 \ LogParser.exe "(or" c: \ Program Files \ Log Parser 2.2 \ LogParser.exe ").
Server name with SQL Server SQL-SRV, SEC instance name and database name quickly. Replace with your own.
@echo off @set WDate=%date:~-10% @echo * %1 (Windows XP)... move \\%1\c$\%1.evt f:\Logs\ @echo * . @echo * evt evtx... wevtutil epl f:\Logs\%1.evt f:\Logs\%1.evtx /lf:true @echo * . @echo * . : f:\Logs\%1.evtx, : %2 LogParser.exe file:"f:\Computer\get_info_from_log.sql"?source=f:\Logs\%1.evtx+output_file=%2 -i:EVT -o:TSV -headers:ON -oSeparator:tab -oTsFormat:"dd.MM.yyyy hh:mm:ss" -fileMode:1 @echo * . @echo * %2. %3... cscript F:\Computer\update_csvFile_forSQLCheck.vbs %2 %3 //NoLogo @echo * . @echo * SQL Server. : %3, %4... LogParser.exe file:"f:\Computer\get_info_from_log_2SQL.sql"?source=%3+output_file=%4 -i:TSV -headerRow:ON -iSeparator:tab -iTsFormat:"dd.MM.yyyy hh:mm:ss" -o:SQL -server:"SQL-SRV\SEC" -database:quickly -driver:"SQL Server" -createTable:ON @echo * . @echo * ... move f:\Logs\%1.evt f:\Logi_ForReports\%1_%WDate%_sec.evt @echo * . 'f:\Logi_ForReports\%1_%WDate%_sec.evt' @echo * evtx ... del f:\Logs\%1.evtx @echo * . @echo * sql-... cscript "F:\Computer\create_SQL_full.vbs" %1 1 //nologo @echo * . @echo * sql-... SQLCMD.EXE -S SQL-SRV\SEC -d quickly -E -if:\Computer\%1-1.sql -o "f:\Computer\%1. .csv" -W -R -s ";" -w 4000 @echo * . @echo * ... cscript F:\Computer\update_result_file.vbs "f:\Computer\%1. .csv" //nologo @echo * . @echo * ... del f:\Computer\%1-1.sql del %2 del %3 del f:\Computer\%1_dbg.txt del "f:\Computer\%1. .csv" @echo * . @echo * -... move "f:\Computer\%1. .xls" "f:\CheckComps\%1. .xls" @echo * . @echo on NotePerhaps, in the batch file you will need to replace SQLCMD.EXE with “c: \ Program Files \ Microsoft SQL Server \ 100 \ Tools \ Binn \ SQLCMD.EXE”, and LogParser.exe with “c: \ Program Files (x86) \ Log Parser 2.2 \ LogParser.exe "(or" c: \ Program Files \ Log Parser 2.2 \ LogParser.exe ").
Server name with SQL Server SQL-SRV, SEC instance name and database name quickly. Replace with your own.
get_info_from_log.sql
SELECT RecordNumber as id, eventid as eId, TimeGenerated as Tg, resolve_sid(sid) as UserName, computername as Computer, EXTRACT_TOKEN(Strings, 0, '|') as image_unique_id, EXTRACT_TOKEN(Strings, 1, '|') as image into %output_file% FROM %source% where ((EventID in (592; 593)) and (TO_UPPERCASE(resolve_sid(sid)) <> 'NT AUTHORITY\NETWORK SERVICE') and (TO_UPPERCASE(resolve_sid(sid)) <> 'NT AUTHORITY\SYSTEM')) and TimeGenerated >= TO_TIMESTAMP('01.03.2011 00:00:00','dd.MM.yyyy hh:mm:ss') order by recordnumber asc get_info_from_log_2SQL.sql
SELECT * into %output_file% FROM %source% update_csvFile_forSQLCheck.vbs
On Error Resume Next dim gsSimbolSplitFields dim sgSimbolSplitAdmin dim gbInsideBlock dim gIx dim gbDebug dim gbWriteString Dim gArrBlock_admin gsSimbolSplitFields = vbTab sgSimbolSplitAdmin = ";" gbInsideBlock = false gbIERuning = false gbIE = false giBlockPlus = 0 giIEPlus = 0 gsDateBlock = "01.01.2011 00:00:00" TgBlockStop = "01.01.2011 00:00:00" idBlockStop = "" gIx = 0 gArrBlock_admin = Array (sgSimbolSplitAdmin & sgSimbolSplitAdmin, _ sgSimbolSplitAdmin & sgSimbolSplitAdmin, _ sgSimbolSplitAdmin & sgSimbolSplitAdmin, _ sgSimbolSplitAdmin & sgSimbolSplitAdmin) gbDebug = true 'gbDebug = false if Wscript.Arguments.Count = 1 then sgFilename = Wscript.Arguments(0) sgFilenameOut = fuRemoveExtention(sgFilename) & "_sql.csv" gsLogFilename = fuRemoveExtention(sgFilename) & "_dbg.txt" elseif Wscript.Arguments.Count = 2 then sgFilename = Wscript.Arguments(0) sgFilenameOut = Wscript.Arguments(1) gsLogFilename = fuRemoveExtention(sgFilename) & "_dbg.txt" elseif Wscript.Arguments.Count = 3 then sgFilename = Wscript.Arguments(0) sgFilenameOut = Wscript.Arguments(1) gsLogFilename = Wscript.Arguments(2) else sgFilename = InputBox(" ", "", "f:\comp-6475.csv") sgFilenameOut = InputBox(" ", "", fuRemoveExtention(sgFilename) & "_sql.csv") gsLogFilename = InputBox(" ", "", fuRemoveExtention(sgFilename) & "_dbg.txt") end if Set objFSO = CreateObject("Scripting.FileSystemObject") if not objFSO.FileExists(sgFilename) then wscript.echo " , !" Wscript.Quit end if Set objTextFileOpen = objFSO.OpenTextFile(sgFilename, 1) Set objTextFileWrite = objFSO.CreateTextFile(sgFilenameOut, True) if gbDebug then if not objFSO.FileExists(gsLogFilename) then set objTextFileWriteLog = objFSO.OpenTextFile(gsLogFilename, 8, True) else set objTextFileWriteLog = objFSO.CreateTextFile(gsLogFilename, True) end if end if Do Until objTextFileOpen.AtEndOfStream record = trim(objTextFileOpen.Readline) gIx = gIx + 1 gbWriteString = true fuPrint gIx & ". '" & record & "'" if InStr(record, gsSimbolSplitFields) then arr = Split(record, gsSimbolSplitFields) id = arr(0) eId = arr(1) Tg = arr(2) UserName = arr(3) Computer = arr(4) image_unique_id = arr(5) image = arr(6) if InStr(lCase(image), "explorer.exe") then if eId = "592" then gbBlockBegin = true gbBlockEnd = false giBlockPlus = giBlockPlus + 1 fuPrint "explorer.exe " else gbBlockBegin = false gbBlockEnd = true giBlockPlus = giBlockPlus - 1 if giBlockPlus < 0 then giBlockPlus = 0 end if fuPrint "explorer.exe " end if else gbBlockBegin = false gbBlockEnd = false end if if InStr(lCase(image), "iexplore.exe") then gbIE = true fuPrint " iexplore.exe" if eId = "592" then fuPrint "iexplore.exe " giIEPlus = giIEPlus + 1 gbIERuning = true if giIEPlus = 1 then image_unique_idIEStart = image_unique_id end if else fuPrint "iexplore.exe " giIEPlus = giIEPlus - 1 gbIERuning = false end if else gbIE = false fuPrint " iexplore.exe" end if if gIx = 1 then objTextFileWrite.WriteLine record & gsSimbolSplitFields & "CompStart" fuPrint " , " elseif gIx = 2 then fuPrint " " if gbBlockBegin then fuPrint " , " gbInsideBlock = true gsDateBlock = Tg gsUserNameBlockStart = UserName image_unique_idBlockStart = image_unique_id objTextFileWrite.WriteLine record & gsSimbolSplitFields & gsDateBlock end if idPrev = id eIdPrev = eId TgPrev = Tg UserNamePrev = UserName ComputerPrev = Computer image_unique_idPrev = image_unique_id imagePrev = image else 'fuPrint " " '-- explorer.exe if gbBlockBegin then fuPrint " explorer.exe ( â„– " & giBlockPlus & ")" if giBlockPlus = 1 then giDiff = DateDiff("s", CDate(TgBlockStop), CDate(Tg)) if giDiff > 9 then if Len(idBlockStop) > 0 then fuPrint " explorer.exe. " record_convert_prev = idBlockStop & gsSimbolSplitFields & _ eIdBlockStop & gsSimbolSplitFields & _ TgBlockStop & gsSimbolSplitFields & _ UserNameBlockStop & gsSimbolSplitFields & _ ComputerBlockStop & gsSimbolSplitFields & _ image_unique_idBlockStart & gsSimbolSplitFields & _ imageBlockStop & gsSimbolSplitFields & _ gsDateBlock fuPrint record_convert_prev objTextFileWrite.WriteLine record_convert_prev end if gsDateBlock = Tg fuPrint " : '" & gsDateBlock & "'" image_unique_idBlockStart = image_unique_id fuPrint " : '" & image_unique_idBlockStart & "'" gsUserNameBlockStart = UserName fuPrint " : '" & gsUserNameBlockStart & "'" else fuPrint " explorer.exe! ." gbWriteString = false end if gbInsideBlock = true else if lCase(gsUserNameBlockStart) = lCase(UserName) then fuPrint "gsUserNameBlockStart: '" & gsUserNameBlockStart & "', UserName: '" & UserName & "'" fuPrint " . , . " record_convert_prev = "999" & gsSimbolSplitFields & _ "593" & gsSimbolSplitFields & _ Tg & gsSimbolSplitFields & _ UserName & gsSimbolSplitFields & _ Computer & gsSimbolSplitFields & _ image_unique_idBlockStart & gsSimbolSplitFields & _ "C:\WINDOWS\explorer.exe" & gsSimbolSplitFields & _ gsDateBlock fuPrint record_convert_prev objTextFileWrite.WriteLine record_convert_prev giBlockPlus = 1 gsDateBlock = Tg fuPrint " : '" & gsDateBlock & "'" image_unique_idBlockStart = image_unique_id fuPrint " : '" & image_unique_idBlockStart & "'" else 'fuPrint "gsUserNameBlockStart: '" & gsUserNameBlockStart & "', UserName: '" & UserName & "'" fuPrint " . , explorer. " gArrBlock_admin(giBlockPlus-2) = image_unique_id & sgSimbolSplitAdmin & UserName & sgSimbolSplitAdmin & Tg fuPrint gArrBlock_admin(giBlockPlus-2) 'gsDateBlock_admin = Tg objTextFileWrite.WriteLine record & gsSimbolSplitFields & Tg gbWriteString = false end if end if end if '-- explorer.exe if gbBlockEnd then fuPrint " explorer.exe ( " & giBlockPlus & ")" if giBlockPlus = 0 then fuPrint " , " idBlockStop = id eIdBlockStop = eId TgBlockStop = Tg UserNameBlockStop = UserName ComputerBlockStop = Computer image_unique_idBlockStop = image_unique_id imageBlockStop = image gbInsideBlock = false giIEPlus = 0 ' <-- IE else fuPrint " , , " for giY = 0 to UBound(gArrBlock_admin) arrA = Split(gArrBlock_admin(giY), sgSimbolSplitAdmin) gsImage_unique_id_A = arrA(0) gsUserName_A = arrA(1) gsTg_A = arrA(2) if gsImage_unique_id_A = image_unique_id then gsDateBlock_admin = gsTg_A end if next objTextFileWrite.WriteLine record & gsSimbolSplitFields & gsDateBlock_admin gbWriteString = false end if end if '-- if gbInsideBlock then if gbIE then if (((gbIERuning) and (giIEPlus = 1)) or ((not gbIERuning) and (giIEPlus = 0))) then fuPrint " IE " record_convert_prev = id & gsSimbolSplitFields & _ eId & gsSimbolSplitFields & _ Tg & gsSimbolSplitFields & _ UserName & gsSimbolSplitFields & _ Computer & gsSimbolSplitFields & _ image_unique_idIEStart & gsSimbolSplitFields & _ image & gsSimbolSplitFields & _ gsDateBlock fuPrint record_convert_prev objTextFileWrite.WriteLine record_convert_prev else fuPrint " IE! gbIERuning: " & gbIERuning & ", giIEPlus: " & giIEPlus record_convert_prev = id & gsSimbolSplitFields & _ eId & gsSimbolSplitFields & _ Tg & gsSimbolSplitFields & _ UserName & gsSimbolSplitFields & _ Computer & gsSimbolSplitFields & _ image_unique_idIEStart & gsSimbolSplitFields & _ image & gsSimbolSplitFields & _ gsDateBlock fuPrint record_convert_prev end if else if gbWriteString then fuPrint " " fuPrint record & gsSimbolSplitFields & gsDateBlock objTextFileWrite.WriteLine record & gsSimbolSplitFields & gsDateBlock else fuPrint " , " end if end if else fuPrint " . " end if '-- idPrev = id eIdPrev = eId TgPrev = Tg UserNamePrev = UserName ComputerPrev = Computer image_unique_idPrev = image_unique_id imagePrev = image end if end if fuPrint "----------------------------------------------" Loop objTextFileWrite.Close objTextFileOpen.Close if gbDebug then objTextFileWriteLog.close end if WScript.Echo "" WScript.Echo "* ." function fuRemoveExtention(lsFilename) lRes = lsFilename if InStr(lsFilename, ".") then lRes = Left(lsFilename, Len(lsFilename)-4) end if fuRemoveExtention = lRes end function function fuGetDateFromFullDate(lsFullDate) lRes = lsFullDate if InStr(lsFullDate, " ") then lArr = Split(lsFullDate, " ") lsDate = lArr(0) lsTime = lArr(1) lRes = lsDate end if fuGetDateFromFullDate = lRes end function function fuGetTimeFromFullDate(lsFullDate) lRes = lsFullDate if InStr(lsFullDate, " ") then lArr = Split(lsFullDate, " ") lsDate = lArr(0) lsTime = lArr(1) lRes = lsTime end if fuGetDateFromFullDate = lRes end function function fuPrint(lsStr) 'if gbDebug then ' wscript.echo lsStr 'end if if gbDebug then objTextFileWriteLog.writeLine lsStr end if fuPrint = true end function create_SQL_full.vbs
if Wscript.Arguments.Count = 1 then gsComputerName = Wscript.Arguments(0) gsSQLtype = "1" elseif Wscript.Arguments.Count = 2 then gsComputerName = Wscript.Arguments(0) gsSQLtype = Wscript.Arguments(1) else gsComputerName = InputBox(" ", "", "") gsSQLtype = InputBox(" sql-?" & VBNewline & "[1 - , 2 - , 3 - ]", "", "1") end if set objFSO = CreateObject("Scripting.FileSystemObject") if gsSQLtype = "1" then fuCreateSQLFile gsComputerName, "1" elseif gsSQLtype = "2" then fuCreateSQLFile gsComputerName, "2" elseif gsSQLtype = "3" then fuCreateSQLFile gsComputerName, "1" fuCreateSQLFile gsComputerName, "2" end if function fuGetTableName(lsCompName) lsTmp = lsCompName if InStr(lsTmp, "-") then lsTmp = Replace(lsTmp, "-", "_") end if fuGetTableName = lsTmp end function sub fuCreateSQLFile(lsComputerName, lsSQLtype) if lsSQLtype = "1" then lsTemplateFilename = "f:\Computer\template-short.sql" elseif gsSQLtype = "2" then lsTemplateFilename = "f:\Computer\template-full.sql" end if lsLogFilename = "f:\Computer\" & lsComputerName & "-" & lsSQLtype & ".sql" lsTableName = fuGetTableName(lsComputerName) if not objFSO.FileExists(lsLogFilename) then set objTextFileWriteLog = objFSO.OpenTextFile(lsLogFilename, 8, True) else set objTextFileWriteLog = objFSO.OpenTextFile(lsLogFilename, 2, True) end if Set objTextFileOpen = objFSO.OpenTextFile(lsTemplateFilename, 1) do until objTextFileOpen.AtEndOfStream record = objTextFileOpen.Readline if InStr(record, "WARNING__TABLE_NAME_FOR_CHANGE") then record = Replace(record, "WARNING__TABLE_NAME_FOR_CHANGE", lsTablename) end if objTextFileWriteLog.writeLine record loop objTextFileOpen.Close objTextFileWriteLog.Close end sub template-short.sql
SELECT TOP (100) PERCENT Computer AS [ ], UserName AS [ ], image AS , start_time AS [ ], stop_time AS [ ], dbo.FU_GET_FULL_QTY_TEST(CONVERT(VARCHAR, DATEDIFF(SECOND, start_time, stop_time) / 3600)) + ':' + dbo.FU_GET_FULL_QTY_TEST(CONVERT(VARCHAR, DATEDIFF(SECOND, start_time, stop_time) % 3600 / 60)) + ':' + dbo.FU_GET_FULL_QTY_TEST(CONVERT(VARCHAR, DATEDIFF(SECOND, start_time, stop_time) % 3600 % 60)) AS FROM (SELECT r.Computer, r.UserName, r.image_unique_id, r.image, r.Tg AS start_time, MIN(s.Tg) AS stop_time FROM (SELECT id, eId, Tg, UserName, Computer, image_unique_id, image FROM dbo.WARNING__TABLE_NAME_FOR_CHANGE WHERE (eId = 592) AND (Tg > CONVERT(DATETIME, '2013-01-01 00:00:00.000', 102)) ) AS r INNER JOIN (SELECT id, eId, Tg, UserName, Computer, image_unique_id, image FROM dbo.WARNING__TABLE_NAME_FOR_CHANGE WHERE (eId = 593)) AS s ON r.image_unique_id = s.image_unique_id AND r.image = s.image AND r.id < s.id AND r.Tg <= s.Tg GROUP BY r.UserName, r.Computer, r.image_unique_id, r.image, r.Tg) AS DERIVEDTBL ORDER BY ' ' DESC update_result_file.vbs
if Wscript.Arguments.Count = 1 then gsFileName = Wscript.Arguments(0) gsFileNameRes = fuRemoveExtention(gsFileName) & ".xls" elseif Wscript.Arguments.Count = 2 then gsFileName = Wscript.Arguments(0) gsFileNameRes = Wscript.Arguments(1) else gsFileName = InputBox(" ", "", "") gsFileNameRes = InputBox(" ", "", fuRemoveExtention(gsFileName) & ".xls") end if sgSimbolSplit = ";" gsSimbolSplitFields = vbTab Set objFSO = CreateObject("Scripting.FileSystemObject") Set objTextFileOpen = objFSO.OpenTextFile(gsFileName, 1) if not objFSO.FileExists(gsFileName) then wscript.echo " , !" objTextFileOpen.Close Wscript.Quit end if if not objFSO.FileExists(gsFileNameRes) then set objTextFileWriteRes = objFSO.OpenTextFile(gsFileNameRes, 8, True) else set objTextFileWriteRes = objFSO.CreateTextFile(gsFileNameRes, True) end if do until objTextFileOpen.AtEndOfStream record = objTextFileOpen.Readline if ((InStr(record, "--------")) or (Len(record) = 0) or (InStr(record, " ")) or (InStr(record, "rows affected"))) then 'wscript.echo " : '" & record & "'" else if InStr(record, sgSimbolSplit) then recordRes = Replace(record, sgSimbolSplit, gsSimbolSplitFields) else recordRes = record end if objTextFileWriteRes.writeLine recordRes end if loop objTextFileWriteRes.Close objTextFileOpen.Close WScript.Echo " ! " & gsFileNameRes function fuRemoveExtention(lsFilename) lRes = lsFilename if InStr(lsFilename, ".") then lRes = Left(lsFilename, Len(lsFilename)-4) end if fuRemoveExtention = lRes end function Block work with computers with seven
Bat file:
is_computer_online7.bat
cscript //nologo "f:\Computer\is_computer_online7.vbs" %1 %2 Bat-file runs the script. The script saves the security log events to the evt-file and runs the main mo7.bat batch file.
is_computer_online7.vbs
on error resume next dim gsComputerName dim gsUseLogFile dim gsLogFilename dim gbFlag dim gsTableName dim gsCompName dim gsRunCmd if Wscript.Arguments.Count = 1 then gsComputerName = Wscript.Arguments(0) gsUseLogFile = "n" elseif Wscript.Arguments.Count = 2 then gsComputerName = Wscript.Arguments(0) gsUseLogFile = Wscript.Arguments(1) else gsComputerName = InputBox(" ", "", "") gsUseLogFile = InputBox(" log- ?" & VBNewline & "[y/n]", "", "y") end if WScript.Echo "* " & gsComputerName if lCase(gsUseLogFile) = "y" then gbFlag = false gsLogFilename = "f:\Log\" & gsComputerName & ".log" WScript.Echo "* " & gsLogFilename set objFSO = CreateObject("Scripting.FileSystemObject") if not objFSO.FileExists(gsLogFilename) then WScript.Echo "* . ..." set objTextFileWriteLog = objFSO.OpenTextFile(gsLogFilename, 8, True) objTextFileWriteLog.writeLine "n" objTextFileWriteLog.close WScript.Echo "* ." end if set objTextFileOpen = objFSO.OpenTextFile(gsLogFilename, 1) do until objTextFileOpen.AtEndOfStream record = trim(objTextFileOpen.Readline) if record = "n" then WScript.Echo "* ." if fuPing(gsComputerName) then 'fuListInstalledSoftware gsComputerName gbFlag = true if fuBackup(gsComputerName) then WScript.Sleep 15000 ' <- 15 fuUploadEvents gsComputerName wscript.sleep 10000 end if end if elseif record = "y" then WScript.Echo "* " & gsComputerName & " ." else WScript.Echo "* " & gsComputerName & " log-." end if loop objTextFileOpen.close if gbFlag then set objTextFileWriteLog = objFSO.OpenTextFile(gsLogFilename, 2, True) objTextFileWriteLog.writeLine "y" objTextFileWriteLog.close WScript.Echo "* ." 'MsgBox " " & gsComputerName & " !", vbInformation, "" end if else 'if fuPing(gsComputerName) then if fuBackup(gsComputerName) then WScript.Sleep 15000 ' <- 15 fuUploadEvents gsComputerName wscript.sleep 60000 end if 'end if end if wscript.sleep 1000 function fuPing(NetworkDevice) lBoo = false set objPING = GetObject("winmgmts:{impersonationLevel=impersonate}")._ ExecQuery ("select * from Win32_PingStatus where address ='" & NetworkDevice & "'") For Each PING In objPing if PING.StatusCode = 0 then WScript.Echo "* " & NetworkDevice & " !" lBoo = true else WScript.Echo "* ." end if next fuPing = lBoo end function function fuBackup(lsComputername) lsEvtBackupFilename = "c:\" & lsComputername & ".evt" lsEvtBackupFilenameRemote = "\\" & lsComputername & "\c$\" & lsComputername & ".evt" lbFlag = false 'WScript.Echo "* lsEvtBackupFilename: " & lsEvtBackupFilename 'WScript.Echo "* lsEvtBackupFilenameRemote: " & lsEvtBackupFilenameRemote set lObjFSO = CreateObject("Scripting.FileSystemObject") if lObjFSO.FileExists(lsEvtBackupFilenameRemote) then WScript.Echo "* . ..." lbFlag = true else Wscript.Echo "* ..." Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Backup)}!\\" & lsComputername & "\root\cimv2") Set colLogFiles = objWMIService.ExecQuery ("Select * from Win32_NTEventLogFile where LogFileName='Security'") For Each objLogfile in colLogFiles errBackupLog = objLogFile.BackupEventLog(lsEvtBackupFilename) If errBackupLog = 0 Then Wscript.Echo "* ." lbFlag = true Else Wscript.Echo "* ." End If Next end if fuBackup = lbFlag end function function fuUploadEvents(lsComputername) WScript.Echo "* ..." gsCompName = lCase(lsComputername) gsTableName = fuGetTableName(gsCompName) gsTableName = uCase(gsTableName) Set WshShell = CreateObject("WScript.Shell") gsRunCmd = "f:\Computer\mo7.bat " & gsCompName & " " & gsTableName WScript.Echo "* : '" & gsRunCmd & "'" WshShell.Run gsRunCmd end function function fuGetTableName(lsCompName) lsTmp = lsCompName if InStr(lsTmp, "-") then lsTmp = Replace(lsTmp, "-", "_") end if fuGetTableName = lsTmp end function mo7.bat does the following:
- It takes the evt-file from the remote computer to the server.
- Converts evt file to evtx.
- Information from evtx-file uploads to SQL Server.
- Backs up the original evt-file to the Logi_ForReports folder (all of a sudden the user will erase his log, and we have a copy).
- Deletes a temporary evtx file.
- Generates and executes sql query to SQL Server.
- Removes temporary files (in the case of debugging or to study the work of the script, this section can be commented out).
- Moves reports to the CheckComps folder.
mo7.bat
, SQLCMD.EXE «c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE», LogParser.exe «c:\Program Files (x86)\Log Parser 2.2\LogParser.exe» ( «c:\Program Files\Log Parser 2.2\LogParser.exe»).
SQL Server' SQL-SRV, SEC quickly. .
@echo off @set WDate=%date:~-10% @echo * %1 (Windows 7)... move \\%1\c$\%1.evt f:\Logs\ @echo * . @echo * evt evtx... wevtutil epl f:\Logs\%1.evt f:\Logs\%1.evtx /lf:true @echo * . @echo * . : f:\Logs\%1.evtx LogParser.exe file:"f:\Computer\get_info_from_log7.sql"?source=f:\Logs\%1.evtx+output_file=%2 -i:EVT -o:SQL -server:"SQL-SRV\SEC" -database:quickly -driver:"SQL Server" -createTable:ON @echo * . @echo * ... move f:\Logs\%1.evt f:\Logi_ForReports\%1_%WDate%_sec.evt @echo * . 'f:\Logi_ForReports\%1_%WDate%_sec.evtx' @echo * sql-... cscript "F:\Computer\create_SQL_full7.vbs" %1 1 //nologo @echo * . @echo * sql-... SQLCMD.EXE -S SQL-SRV\SEC -d quickly -E -if:\Computer\%1-1.sql -o "f:\Computer\%1. .csv" -W -R -s ";" -w 4000 @echo * . @echo * ... cscript F:\Computer\update_result_file7.vbs "f:\Computer\%1. .csv" //nologo @echo * . @echo * ... del f:\Logs\%1.evtx del f:\Computer\%1-1.sql del "f:\Computer\%1. .csv" @echo * . @echo * -... move "f:\Computer\%1. .xls" "f:\CheckComps\%1. .xls" @echo * . @echo on Note, SQLCMD.EXE «c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE», LogParser.exe «c:\Program Files (x86)\Log Parser 2.2\LogParser.exe» ( «c:\Program Files\Log Parser 2.2\LogParser.exe»).
SQL Server' SQL-SRV, SEC quickly. .
get_info_from_log7.sql
SELECT RecordNumber as id, eventid as eId, TimeGenerated as Tg, --resolve_sid(sid) as UserName, EXTRACT_TOKEN(Strings, 1, '|') as UserName, computername as Computer, EXTRACT_TOKEN(Strings, 4, '|') as image_id, EXTRACT_TOKEN(Strings, 5, '|') as image, EXTRACT_TOKEN(Strings, 6, '|') as name into %output_file% FROM %source% where (EventID in (4688;4689)) and ( (TO_UPPERCASE(resolve_sid(sid)) <> 'NT AUTHORITY\NETWORK SERVICE') and (TO_UPPERCASE(resolve_sid(sid)) <> 'NT AUTHORITY\SYSTEM')) and TimeGenerated >= TO_TIMESTAMP('01.01.2013 00:00:00','dd.MM.yyyy hh:mm:ss') order by recordnumber desc create_SQL_full7.vbs
'on error resume next if Wscript.Arguments.Count = 1 then gsComputerName = Wscript.Arguments(0) gsSQLtype = "1" elseif Wscript.Arguments.Count = 2 then gsComputerName = Wscript.Arguments(0) gsSQLtype = Wscript.Arguments(1) else gsComputerName = InputBox(" ", "", "") gsSQLtype = InputBox(" sql-?" & VBNewline & "[1 - , 2 - , 3 - ]", "", "1") end if set objFSO = CreateObject("Scripting.FileSystemObject") if gsSQLtype = "1" then fuCreateSQLFile gsComputerName, "1" elseif gsSQLtype = "2" then fuCreateSQLFile gsComputerName, "2" elseif gsSQLtype = "3" then fuCreateSQLFile gsComputerName, "1" fuCreateSQLFile gsComputerName, "2" end if function fuGetTableName(lsCompName) lsTmp = lsCompName if InStr(lsTmp, "-") then lsTmp = Replace(lsTmp, "-", "_") end if fuGetTableName = lsTmp end function sub fuCreateSQLFile(lsComputerName, lsSQLtype) if lsSQLtype = "1" then lsTemplateFilename = "f:\Computer\template-short7.sql" elseif gsSQLtype = "2" then lsTemplateFilename = "f:\Computer\template-full7.sql" end if lsLogFilename = "f:\Computer\" & lsComputerName & "-" & lsSQLtype & ".sql" lsTableName = fuGetTableName(lsComputerName) 'WScript.Echo "* " & lsComputerName 'WScript.Echo "* " & lsTableName 'WScript.Echo "* sql- " & lsLogFilename if not objFSO.FileExists(lsLogFilename) then set objTextFileWriteLog = objFSO.OpenTextFile(lsLogFilename, 8, True) else set objTextFileWriteLog = objFSO.OpenTextFile(lsLogFilename, 2, True) end if Set objTextFileOpen = objFSO.OpenTextFile(lsTemplateFilename, 1) do until objTextFileOpen.AtEndOfStream record = objTextFileOpen.Readline if InStr(record, "WARNING__TABLE_NAME_FOR_CHANGE") then record = Replace(record, "WARNING__TABLE_NAME_FOR_CHANGE", lsTablename) end if objTextFileWriteLog.writeLine record loop objTextFileOpen.Close objTextFileWriteLog.Close end sub template-short7.sql
SELECT TOP (100) PERCENT Computer AS [ ], UserName AS [ ], program AS , start_time AS [ ], stop_time AS [ ], dbo.FU_GET_FULL_QTY_TEST(CONVERT(VARCHAR, DATEDIFF(SECOND, start_time, stop_time) / 3600)) + ':' + dbo.FU_GET_FULL_QTY_TEST(CONVERT(VARCHAR, DATEDIFF(SECOND, start_time, stop_time) % 3600 / 60)) + ':' + dbo.FU_GET_FULL_QTY_TEST(CONVERT(VARCHAR, DATEDIFF(SECOND, start_time, stop_time) % 3600 % 60)) AS FROM (SELECT r.Computer, s.UserName, r.programID, r.id AS R_ID, MIN(s.id) AS S_ID, r.program, r.Tg AS start_time, MIN(s.Tg) AS stop_time FROM (SELECT id, eId, Tg, UserName, Computer, image_id AS programID, image AS program FROM dbo.WARNING__TABLE_NAME_FOR_CHANGE WHERE (eId = 4688) AND (Tg > CONVERT(DATETIME, '2013-01-01 00:00:00.000', 102)) AND image not like '%.scr') AS r INNER JOIN (SELECT id, eId, Tg, UserName, Computer, image AS programID, name AS program FROM dbo.WARNING__TABLE_NAME_FOR_CHANGE WHERE (eId = 4689) AND name not like '%.scr') AS s ON r.programID = s.programID AND r.program = s.program AND r.UserName = s.UserName AND r.id <= s.id GROUP BY r.Computer, s.UserName, r.programID, r.id, r.program, r.Tg) AS DERIVEDTBL UNION ALL SELECT TOP (100) PERCENT Computer AS [ ], UserName AS [ ], program AS , start_time AS [ ], stop_time AS [ ], dbo.FU_GET_FULL_QTY_TEST(CONVERT(VARCHAR, DATEDIFF(SECOND, start_time, stop_time) / 3600)) + ':' + dbo.FU_GET_FULL_QTY_TEST(CONVERT(VARCHAR, DATEDIFF(SECOND, start_time, stop_time) % 3600 / 60)) + ':' + dbo.FU_GET_FULL_QTY_TEST(CONVERT(VARCHAR, DATEDIFF(SECOND, start_time, stop_time) % 3600 % 60)) AS FROM (SELECT r.Computer, s.UserName, r.programID, r.id AS R_ID, MIN(s.id) AS S_ID, r.program, r.Tg AS start_time, MIN(s.Tg) AS stop_time FROM (SELECT id, eId, Tg, UserName, Computer, image_id AS programID, image AS program FROM dbo.WARNING__TABLE_NAME_FOR_CHANGE WHERE (eId = 4688) AND (Tg > CONVERT(DATETIME, '2013-01-01 00:00:00.000', 102)) AND image like '%.scr') AS r INNER JOIN (SELECT id, eId, Tg, UserName, Computer, image AS programID, name AS program FROM dbo.WARNING__TABLE_NAME_FOR_CHANGE WHERE (eId = 4689) AND name like '%.scr') AS s ON r.programID = s.programID AND r.program = s.program AND r.id <= s.id GROUP BY r.Computer, s.UserName, r.programID, r.id, r.program, r.Tg) AS DERIVEDTBL2 ORDER BY ' ' DESC update_result_file7.vbs
'on error resume next if Wscript.Arguments.Count = 1 then gsFileName = Wscript.Arguments(0) gsFileNameRes = fuRemoveExtention(gsFileName) & ".xls" elseif Wscript.Arguments.Count = 2 then gsFileName = Wscript.Arguments(0) gsFileNameRes = Wscript.Arguments(1) else gsFileName = InputBox(" ", "", "") gsFileNameRes = InputBox(" ", "", fuRemoveExtention(gsFileName) & ".xls") end if sgSimbolSplit = ";" gsSimbolSplitFields = vbTab Set objFSO = CreateObject("Scripting.FileSystemObject") Set objTextFileOpen = objFSO.OpenTextFile(gsFileName, 1) if not objFSO.FileExists(gsFileName) then wscript.echo " , !" objTextFileOpen.Close Wscript.Quit end if if not objFSO.FileExists(gsFileNameRes) then set objTextFileWriteRes = objFSO.OpenTextFile(gsFileNameRes, 8, True) else set objTextFileWriteRes = objFSO.CreateTextFile(gsFileNameRes, True) end if do until objTextFileOpen.AtEndOfStream record = objTextFileOpen.Readline if ((InStr(record, "--------")) or (Len(record) = 0) or (InStr(record, " ")) or (InStr(record, "rows affected"))) then 'wscript.echo " : '" & record & "'" else if InStr(record, sgSimbolSplit) then recordRes = Replace(record, sgSimbolSplit, gsSimbolSplitFields) else recordRes = record end if objTextFileWriteRes.writeLine recordRes end if loop objTextFileWriteRes.Close objTextFileOpen.Close WScript.Echo " ! " & gsFileNameRes function fuRemoveExtention(lsFilename) lRes = lsFilename if InStr(lsFilename, ".") then lRes = Left(lsFilename, Len(lsFilename)-4) end if fuRemoveExtention = lRes end function ')
On SQL Server, you need to create the FU_GET_FULL_QTY_TEST function:
FU_GET_FULL_QTY_TEST
USE [quickly] GO /****** Object: UserDefinedFunction [dbo].[FU_GET_FULL_QTY_TEST] Script Date: 12/03/2013 13:03:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[FU_GET_FULL_QTY_TEST] (@short_qty varchar(255)) RETURNS varchar(255) AS BEGIN DECLARE @retMsg varchar(255) set @retMsg = @short_qty if len(@short_qty) <= 1 set @retMsg = '0' + @retMsg RETURN (@retMsg) END Archive with scripts can be downloaded here .
I know, it seems a lot of batch files and scripts. But it’s enough to set up once and use later
Update.
Security log settings for saving program start / stop events and event numbers
For XP
EventID 592 to create a process, 593 to complete.
Audit settings.
secedit /configure /cfg c:\XP\secsetup.inf /db secsetup.sdb /verbose /overwrite /quiet A piece from the secsetup.inf file:
[Event Audit] ; 0 - ; 1 - ; 2 - ; 3 - AuditSystemEvents = 3 AuditLogonEvents = 3 AuditObjectAccess = 3 AuditPrivilegeUse = 3 AuditPolicyChange = 3 AuditAccountManage = 3 AuditProcessTracking = 3 AuditAccountLogon = 3 For 7
EventID 4688 to create a process, 4689 to complete.
Audit settings.
For Russian:
auditpol.exe /set /category:" " /subcategory:" " /success:enable /failure:enable auditpol.exe /set /category:" " /subcategory:" " /success:enable /failure:enable For English:
auditpol.exe /set /category:"Detailed Tracking" /subcategory:"Process Creation" /success:enable /failure:enable auditpol.exe /set /category:"Detailed Tracking" /subcategory:"Process Termination" /success:enable /failure:enable Of course, if there is a domain, the audit settings should be specified in the domain policies.
And who does how to report on the launch of programs on users' computers? Share it.
Source: https://habr.com/ru/post/202914/
All Articles