Страница 1 из 1

PowerShell: sql audit , from application log

Добавлено: 27 окт 2015 13:14
M.K

Код: Выделить всё

# если 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



}