SQL Audit DSC

#Set-TargetResource

# Avoid loading all of the sqlps module - this is faster and more stable
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

if( $SQLInstance = "MSSQLSERVER" ) {
    $SQLInstance = ""
}

$server = New-Object Microsoft.SqlServer.Management.Smo.Server("$SQLServer\$SQLInstance")
$audit = $server.Audits | Where-Object -FilterScript  { $_.Name -match ( '^' + $AuditName + '$' ) }

if ( $audit -eq $null ){
    Write-Verbose -Message "Creating audit $AuditName."
    $audit = New-Object Microsoft.SqlServer.Management.Smo.Audit($server, $AuditName)
    $audit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]'ApplicationLog'
    $audit.QueueDelay = 1000
    $audit.OnFailure = [Microsoft.SqlServer.Management.Smo.OnFailureAction]'Continue'
    $audit.Guid = [guid]::NewGuid().Guid
    $audit.Create()
    $audit.Enable()
}

#If audit spec doesn't exist already, create it. If it exists but isn't mapped to the audit, maps it.
$auditSpec = $server.ServerAuditSpecifications | Where-Object -FilterScript { $_.Name -match ( '^' + $AuditSpecName + '$' ) }

if ( $auditSpec -eq $null ){
    Write-Verbose -Message "Creating server audit specification $AuditSpecName."
    $auditSpec = New-Object Microsoft.SqlServer.Management.Smo.ServerAuditSpecification($server, $AuditSpecName)
    $auditSpec.AuditName = $AuditName
    #Add actions to audit spec
    #TODO: Instead of iterating, use the AddAuditSpecificationDetail(ICollection<AuditSpecificationDetail>) method.
    foreach ( $action in $AuditActions ){
        $specDetail = New-Object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail([Microsoft.SqlServer.Management.Smo.AuditActionType]$action)
        $auditSpec.AddAuditSpecificationDetail($specDetail)
    }
    $auditSpec.Create()
    $auditSpec.Enable()
} elseif ( $audit.EnumServerAuditSpecification() -notmatch ( '^' + $AuditSpecName + '$' ) ){
    Write-Verbose -Message "Attaching server audit specification $AuditSpecName to audit $AuditName."
    $auditSpec.AuditName = $AuditName
    $auditSpec.Alter()
    $auditSpec.Enable()
}

#If the specs don't match $AuditActions, wipe the specs and replace them.
$auditSpecActions = $auditSpec.EnumAuditSpecificationDetails().Action | ForEach-Object -Process { $_.ToString() }

if ( Compare-Object -ReferenceObject ($AuditActions | Sort-Object) -DifferenceObject ($auditSpecActions | Sort-Object) ){
    Write-Verbose -Message "Replacing details in server audit specification $AuditSpecName with those specified."
    #TODO: Instead of iterating, use the RemoveAuditSpecificationDetail(ICollection<AuditSpecificationDetail>) method.
    foreach ( $action in $auditSpec.EnumAuditSpecificationDetails() ){
        $auditSpec.RemoveAuditSpecificationDetail($action)
    }
    #TODO: Instead of iterating, use the AddAuditSpecificationDetail(ICollection<AuditSpecificationDetail>) method.
    foreach ( $action in $AuditActions ){
        $specDetail = New-Object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail([Microsoft.SqlServer.Management.Smo.AuditActionType]$action)
        $auditSpec.AddAuditSpecificationDetail($specDetail)
    }
    $auditSpec.Alter()
    $auditSpec.Enable()
}