none
Eventlog export to SQL script error RRS feed

  • Question

  • Hello everyone,

    I'm trying a script to export eventlog to a SQL db.

    i have a function on my script as follow

    function Get-UserFromSID ($SID){
        $SIDObject = New-Object -TypeName System.Security.Principal.SecurityIdentifier($SID)
        $User = $SIDObject.Translate([System.Security.Principal.NTAccount])
        $User.Value
    }

    but when i run the script i'm getting the following error.

    New-Object : A constructor was not found. Cannot find an appropriate constructor for type
    System.Security.Principal.SecurityIdentifier.
    At C:\Exploit\Tools\EventCollect.ps1:23 char:18
    +     $SIDObject = New-Object System.Security.Principal.SecurityIdentifier($SID)
    +                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (:) [New-Object], PSArgumentException
        + FullyQualifiedErrorId : CannotFindAppropriateCtor,Microsoft.PowerShell.Commands.NewObjectCommand

    You cannot call a method on a null-valued expression.
    At C:\Exploit\Tools\EventCollect.ps1:24 char:5
    +     $User = $SIDObject.Translate([System.Security.Principal.NTAccount])
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull

    Any idea about what i'm doing wrong? please help.

    Thank you

    Friday, March 9, 2018 4:18 PM

Answers

All replies

  • Please post a very short script that contains only the absolute minimum amount of code needed to reproduce the error.


    -- Bill Stewart [Bill_Stewart]

    Friday, March 9, 2018 4:50 PM
    Moderator
  • You must pass a full StringSid.

    $sid = 'S-1-5-21-194446983-321333823-1537898166-1130'
    New-Object -TypeName System.Security.Principal.SecurityIdentifier($SID)

    To use a binary sid you need the offset

    New-Object -TypeName System.Security.Principal.SecurityIdentifier($binarySID,0)


    \_(ツ)_/

    Friday, March 9, 2018 4:59 PM
  • 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 the last run date & time
    $RegKey = "HKCU:\Software\Event\EventCollector"
    # SQL Database that holds the table for the events
    $SQLDatabase = "EventCollection"
    
    function Get-UserFromSID ($SID){
        
        $SIDObject = New-Object 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 the first run
    
    [datetime]$LastRunExeDLL = "2018-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, crate 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
            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]
        }
    }

    Here is the full script
    Friday, March 9, 2018 5:06 PM
  • Also note that very often the "UserID" is blank and cannot be passed to the function.


    \_(ツ)_/

    Friday, March 9, 2018 5:17 PM
  • Jrv,

    You are right (again :-))! I found that the UserID is blank and cannot be passed to the function.

    Thank you.

    Best regards

    Monday, March 12, 2018 1:56 PM