Ytukyg

I have an issue when I run PowerShell Script that suppose to extract Apllication event data from Windows event Viewer to SQL database. I get this error:
SID problem

The source code:

param(

[parameter(Mandatory=$true)][string]$LogName,

[parameter(Mandatory=$true)][string]$SQLServer)





# Check event log for events written since this script was last run

# or all events if this is the first run of the script

# and then upload them to SQL Server efficiently



# Create a simplified version of the log name for use elsewhere in the 

script

$LogNameSimplified = $LogName.Replace("/","_")

$LogNameSimplified = $LogNameSimplified.Replace(" ","")

$LogNameSimplified = $LogNameSimplified.Replace("-","")

Write-Host "SQL table name: $LogNameSimplified"



# Registry key to store last run date & time

$RegKey = "HKCU:SoftwareRCMTechEventCollector"

# SQL Database that holds the table for the events

$SQLDatabase = "EventCollection"



function Get-UserFromSID ($SID){

# Does what it says on the tin

$SIDObject = New-Object -TypeName 

System.Security.Principal.SecurityIdentifier($SID)

$User = $SIDObject.Translate([System.Security.Principal.NTAccount])

$User.Value

}



# Initialise LastRun variable, make it old enough that all events will be 

collected on first run

# Always use ISO 8601 format

[datetime]$LastRunExeDll = "1977-01-01T00:00:00"



if(Test-Path $RegKey){

# Registry key exists, check LastRun value

$LastRunValue = (Get-ItemProperty -Path $RegKey -Name $LogNameSimplified - 

ErrorAction SilentlyContinue).$LogNameSimplified

if($LastRunValue -ne $null){

    $LastRunExeDll = $LastRunValue

}

}else{

# Registry key does not exist, create it, then set the NewsID value and run 

full script

Write-Host "Registry key not present"

New-Item -Path $RegKey -Force | Out-Null

}



# Get the events logged since LastRun date & time

Write-Host ("Collecting events from "+(Get-Date -Date $LastRunExeDll -Format 

s))

$Events = Get-WinEvent -FilterHashtable @{logname=$LogName; 

starttime=$LastRunExeDll} -ErrorAction SilentlyContinue

Write-Host ("Found "+$Events.Count+" events")



if($Events.Count -gt 0){

# Process event data into a DataTable ready for upload to SQL Server

# Create DataTable

$DataTable = New-Object System.Data.DataTable

$DataTable.TableName = $LogNameSimplified

# Define Columns

$Column1 = New-Object system.Data.DataColumn TimeCreated,([datetime])

$Column2 = New-Object system.Data.DataColumn MachineName,([string])

$Column3 = New-Object system.Data.DataColumn UserId,([string])

$Column4 = New-Object system.Data.DataColumn Id,([int])

$Column5 = New-Object system.Data.DataColumn Message,([string])

# Add the Columns

$DataTable.Columns.Add($Column1)

$DataTable.Columns.Add($Column2)

$DataTable.Columns.Add($Column3)

$DataTable.Columns.Add($Column4)

$DataTable.Columns.Add($Column5)

# Add event data to DataTable

foreach($Event in $Events){

    $Row = $DataTable.NewRow()

    $Row.TimeCreated = $Event.TimeCreated 

    $Row.MachineName = $Event.MachineName

    $Row.UserId = Get-UserFromSID -SID $Event.UserId

    $Row.Id = $Event.Id

    $Row.Message = $Event.Message

    $DataTable.Rows.Add($Row)

}



# Bulk copy the data into SQL Server

try{

    $SQLConnection = New-Object -TypeName 

System.Data.SqlClient.SqlConnection -ArgumentList "Data 

Source=$SQLServer;Integrated Security=SSPI;Database=$SQLDatabase"

    $SQLConnection.Open()

    $SQLBulkCopy = New-Object -TypeName System.Data.SqlClient.SqlBulkCopy - 

ArgumentList $SQLConnection

    $SQLBulkCopy.DestinationTableName = "dbo.$LogNameSimplified"

    $SQLBulkCopy.BulkCopyTimeout = 60

    $SQLBulkCopy.WriteToServer($Datatable)

    # Create/update the LastRun value - assuming all the above has worked - 

in ISO 8601 format

    New-ItemProperty -Path $RegKey -Name $LogNameSimplified -Value (Get-Date 

-Format s) -Force | Out-Null

    Write-Host "Data uploaded to SQL Server"

}

catch{

    Write-Host "Problem uploading data to SQL Server"

    Write-Error $error[0]

}

}

i think it's a problem with a function Get-UserFromSID. Tried read documentations of SID got the idea of it, just dont know how to write some data into SQL base. It's like I need permission to write that data just dont know how to.