Код: Выделить всё
# если sql сервере ведет лог в application , то скрипт позволит выджернуть кто , когда и откуда стучался.
# отдельно составлояет файл с неудачными коннектами. переделанный скрипт с технэта
# .... folder with config and reports
$path="C:\temp\sql"
# working period
$data_peryod = "-1" # период за 1 день
# files with list of sql servers
$Sql_Servers_List = "servers_sql.txt" # список sql серверов
# analysing more events ID - Y or N
$podrob = "N"
###################################################################
$timestamp = Get-Date -UFormat %d%m%y
# Table Creation
$LogonActivityTable = New-Object system.Data.DataTable “Logon/Logoff Activity”
$hostnames = Get-Content $path\$Sql_Servers_List |? {Test-Connection $_ -Quiet -Count 2}
$endDate = get-date
$startDate = (get-date).AddDays($data_peryod)
#test path
$tp2 = Test-Path $path\report
if ($tp2 -ne "True") { New-Item $path\report -type directory }
$tp2 = Test-Path $path\report\$timestamp
if ($tp2 -ne "True") { New-Item $path\report\$timestamp -type directory }
foreach ($hostname in $hostnames) {
$LogonActivityTable = New-Object system.Data.DataTable “Logon/Logoff Activity”
# Create Columns
$date = New-Object system.Data.DataColumn "Date",([string])
$status = New-Object system.Data.DataColumn "Status",([string])
$user = New-Object system.Data.DataColumn "User",([string])
$ipaddress = New-Object system.Data.DataColumn "IPAddress",([string])
$comments = New-Object system.Data.DataColumn "Comments",([string])
# Add Columns to Table
$LogonActivityTable.columns.add($date)
$LogonActivityTable.columns.add($status)
$LogonActivityTable.columns.add($user)
$LogonActivityTable.columns.add($ipaddress)
$LogonActivityTable.columns.add($comments)
# Store each event from the Security Log with the specificed dates and computer in an array
$log = Get-Eventlog -LogName Application -ComputerName $hostname -after $startDate -before $endDate
foreach ($i in $log){
# Logon Successful Events
if (($i.EventID -eq 18453) -or ($i.EventID -eq 18454) -or ($i.EventID -eq 18455) ) {
# Create a Row
$row = $LogonActivityTable.NewRow()
# Enter Data into the Row
$row.date = $i.TimeGenerated
$row.status = "Success"
$row.user = $i.ReplacementStrings[0]
$row.ipaddress = $i.ReplacementStrings[1]
$row.comments = ""
# Add the Row to the Table
$LogonActivityTable.Rows.Add($row)
}
# Logon Failure Events
# Local
if (( $podrob -eq "Y") -or ( $podrob -eq "y")) {
if (($i.EventID -eq 18450) -or ($i.EventID -eq 18451) -or ($i.EventID -eq 18452) -or ($i.EventID -eq 18457) -or ($i.EventID -eq 18458) -or ($i.EventID -eq 18459) -or ($i.EventID -eq 18460) -or ($i.EventID -eq 18461) -or ($i.EventID -eq 18741) ) {
# Create a Row
$row = $LogonActivityTable.NewRow()
# Enter Data into the Row
$row.date = $i.TimeGenerated
$row.status = "Failure"
$row.user = $i.ReplacementStrings[0]
$row.ipaddress = $i.ReplacementStrings[1]
$row.comments = ""
# Add the Row to the Table
$LogonActivityTable.Rows.Add($row)
}
}
if ($i.EventID -eq 18456) {
# Create a Row
$row = $LogonActivityTable.NewRow()
# Enter Data into the Row
$row.date = $i.TimeGenerated
$row.status = "Failure"
$row.user = $i.ReplacementStrings[0]
$row.ipaddress = $i.ReplacementStrings[2]
$row.comments = $i.ReplacementStrings[1]
# Add the Row to the Table
$LogonActivityTable.Rows.Add($row)
}
if ($i.EventID -eq 18470) {
# Create a Row
$row = $LogonActivityTable.NewRow()
# Enter Data into the Row
$row.date = $i.TimeGenerated
$row.status = "Failure"
$row.user = $i.ReplacementStrings[0]
$row.ipaddress = $i.ReplacementStrings[1]
$row.comments = "Reason: The account is disabled"
# Add the Row to the Table
$LogonActivityTable.Rows.Add($row)
}
<# BackUp
if ($i.EventID -eq 18265 ) {
# Create a Row
$row = $LogonActivityTable.NewRow()
# Enter Data into the Row
$row.date = $i.TimeGenerated
#$row.type = "Logon - Local"
$row.status = "Backup Succesfull"
$row.user = $i.ReplacementStrings[0]
$row.ipaddress = $i.ReplacementStrings[6]
# Add the Row to the Table
$LogonActivityTable.Rows.Add($row)
}
#>
}
$msg = "Hostname: " + $hostname + " Start: " + $startDate + " End: " + $endDate +"`n"
$msg | Out-File $path\report\$timestamp\$hostname-sql-failure.txt
$LogonActivityTable | where {$_.User -ne 'NT AUTHORITY\SYSTEM'} | where {$_.Status -eq 'Failure'} | group User | select Count,Name | ft >> $path\report\$timestamp\$hostname-sql-failure.txt
$LogonActivityTable | where {$_.Status -eq 'Failure'} | ft >> $path\report\$timestamp\$hostname-sql-failure.txt
$msg | Out-File $path\report\$timestamp\$hostname-sql-all.txt
$LogonActivityTable | group User | select Count,Name | ft >> $path\report\$timestamp\$hostname-sql-all.txt
$LogonActivityTable | ft >> $path\report\$timestamp\$hostname-sql-all.txt
$msg | Out-File $path\report\$timestamp\$hostname-who-ls.txt
$LogonActivityTable | where {$_.User -ne 'NT AUTHORITY\SYSTEM'} | group User | select Count,Name | ft >> $path\report\$timestamp\$hostname-who-ls.txt
$LogonActivityTable | where {$_.User -ne 'NT AUTHORITY\SYSTEM'} | ft >> $path\report\$timestamp\$hostname-who-ls.txt
}