Script Repository


Save information about approved user updates to SQL

February 22, 2021
1666

The script exports information about an approved user update to an external SQL database. The exported data will include information on who requested the operation, when, what was changed, and also who approved it and when.

To use the script with Adaxes, create a business rule triggered after updating a user that runs the script.

Parameters:

  • $databaseHost - Specifies the SQL Server that hosts the database to export information to.
  • $databaseName - Specifies the database name.
  • $databaseUsername - Specifies the username to use when connecting to the database. Use $NULL if you want to use the credentials of the Adaxes service account.
  • $databasePassword - Specifies the password to use when connecting to the database. Use $NULL if you want to use the credentials of the Adaxes service account.
Edit Remove
PowerShell
$databaseHost = "host.company.com" # TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me
$databaseUsername = $NULL # TODO: modify me
$databasePassword = $NULL # TODO: modify me

# Build filter
$filter = New-Object "System.Text.StringBuilder"
[void]$filter.Append("(&(objectClass=adm-ApprovalRequest)(adm-ApprovalState=1)")

# Add Target Object GUID
$targetObjectGuid = [Guid]$Context.TargetObject.Get("objectGUID")
$targetObjectGuidFilter = [Softerra.Adaxes.Ldap.FilterBuilder]::Create("adm-TargetObjectGuid", $targetObjectGuid)
[void]$filter.Append($targetObjectGuidFilter)

# Add Initiator GUID
$initiatorGuid = [Guid]$Context.Initiator.UserAdsObject.Get("objectGuid")
$initiatorGuidFilter = [Softerra.Adaxes.Ldap.FilterBuilder]::Create("adm-ApprovalRequestorGuid", $initiatorGuid)
[void]$filter.Append($initiatorGuidFilter)

# Add time limit
$startTime = [System.DateTime]::UtcNow.AddMinutes(-5)
$dateGenerilized = [Softerra.Adaxes.Utils.Transform]::ToGeneralizedTime($startTime)
[void]$filter.Append("(whenChanged>=$dateGenerilized)")

# Finish building filter
[void]$filter.Append(")")

# Bind to the Approval Requests container
$path = $Context.GetWellKnownContainerPath("ApprovalRequests")
$searcher = $Context.BindToObject($path)

# Set search parameters
$searcher.SearchFilter = $filter.ToString()
$searcher.SearchScope = "ADS_SCOPE_SUBTREE"
$searcher.PageSize = 500
$searcher.SetPropertiesToLoad(@("objectGuid"))

try
{
    # Find Approval Requests related to the operation
    $searchResultIterator = $searcher.ExecuteSearch()
    $searchResults = $searchResultIterator.FetchAll()

    if ($searchResults.Count -eq 0)
    {
        $Context.LogMessage("Approval Request not found. Probably, the operation did not require an approval", "Warning")
        return
    }
    elseif ($searchResults.Count -gt 1)
    {
        $Context.LogMessage("Found more than one Approval Requests. Request information will not be imported into SQL Server", "Warning")
        return
    }

    $requestGuid = [Guid]$searchResults[0].Properties["objectGuid"].Value
}
finally
{
    # Release resources
    $searchResultIterator.Dispose()
}

# Build connection string
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
{
    $connectionString = $connectionString + "Integrated Security=SSPI;"
}
else
{
    $connectionString = $connectionString +
        "User ID=$databaseUsername;Password=$databasePassword;"
}

try
{
    # Connect to SQL server
    $connection = New-Object "System.Data.SqlClient.SqlConnection"  $connectionString
    $connection.Open()
    
    try
    {
        # Get all request IDs from the database 
        $command = $connection.CreateCommand()
        $command.CommandText = "SELECT RequestGUID FROM Requests" # TODO: modify me
        
        $reader = $command.ExecuteReader()
        $existingRequestGuids = New-Object "System.Collections.Generic.HashSet[System.Guid]" 
        while ($reader.Read())
        {
            $existingRequestGuid = [Guid]$reader["requestGUID"]
            [void]$existingRequestGuids.Add($existingRequestGuid)
        }
    }
    finally
    {
        $reader.Close()
        $command.Dispose()
    }
    
    # Check whether the Approval Request has already been added to the database
    if ($existingRequestGuids.Contains($requestGuid))
    {
        $Context.LogMessage("Request with ID '$requestGuid' already exists in Requests table. Request information will not be imported into SQL Server", "Warning")
        return
    }
    
    # Bind to the Approval Request
    $request = $Context.BindToObject("Adaxes://<GUID=$requestGuid>")

    # Get Approval Request information
    $targetObjectName = [Softerra.Adaxes.Utils.ObjectNameHelper]::GetObjectName($request.TargetObject.AdsPath, 'IncludeParentPath')
    $processedBy = [Softerra.Adaxes.Utils.ObjectNameHelper]::GetObjectName($request.ProcessedBy.AdsPath, 'IncludeParentPath')
    $approveDate = ($request.Get("whenChanged")).ToLocalTime()
    $operationDescription = $request.DescriptionOfOperationToApprove
    
    # Update data in SQL
    $command = $connection.CreateCommand()
    $command.CommandText = "
        INSERT INTO Requests (RequestGUID, TargetObjectName, ProcessedBy, DateApproved, OperationDescription)
        VALUES(@RequestGUID, @TargetObjectName, @ProcessedBy, @DateApproved, @OperationDescription)" # TODO: modify me
    $command.Parameters.Add("@RequestGUID", $requestGuid) | Out-Null
    $command.Parameters.Add("@TargetObjectName", $targetObjectName) | Out-Null
    $command.Parameters.Add("@ProcessedBy", $processedBy) | Out-Null
    $command.Parameters.Add("@DateApproved", $approveDate) | Out-Null
    $command.Parameters.Add("@OperationDescription", $operationDescription) | Out-Null
    
    $command.ExecuteNonQuery() | Out-Null;
    $command.Dispose()
}
finally
{
    # Release resources
    $connection.Close()
}

Comments ( 0 )
No results found.
Leave a comment