PowerShell Auditing

The PowerShell Audit resources are packaged in AuditEvents.zip

Included should be a PowerShell script and 2 SQL scripts.


Auditing:#

The Audit Logging page provides the steps required to enable auditing. The resulting configuration will have a deployment of MindLink Anywhere with a logger that provides the audit events and a script to aggregate audited group events. The output files will be provided in a single folder for auditors to analyze.

We recommend the following practices:

  • Set up the serilog configuration to put audit json files into an audit directory
  • Set up the audit script to periodically scrape into the same audit directory
  • Have the directory monitored for audit content

Capture MCE group events with PowerShell#

Description#

Included in the PowerShell module are three commandlets:

  • Update-DatabaseForAuditing
    • Updates event tables to include a unique ID column to the event tables to be audited.
  • Get-AuditEvents
    • Produces a list of objects, each representing an audit event from the database.
  • Save-AuditEvents
    • Takes in a list of objects representing the audit events to save into a particular file.

Running all functions correctly will produce a singular json file containing audit records for when a user:

  • Gains read rights to a group
  • Loses read rights to a group
  • Joins a group
  • Leaves a group
  • Sends a message

If not specified, this filename of the results will contain the unix timestamp of the time at which running all functions was executed. If attempting to save results with a duplicate filename, numbers will be appended to ensure uniqueness and that no file is overridden.

The records will be in chronological order specified by the timestamp of when the event occurred.

  • Each record will contain the following information:
    • The type of the event
    • The time of the event
      • A timestamp as UNIX time
      • A human readable UTC timestring
    • The ID of the group
    • The name of the group
    • The security context of the group
    • The ID of the user
    • The name of the user
    • The linked identity of the user

If the event concerns a sent message, the message content will not be included, instead the message ID will be included for further auditing purposes.

How to run#

It is not a requirement that the SQL script and PowerShell script be kept within the same working directory as full file paths can be specified for both the SQL script and output json file.

Before being able to access the inner functions, it is required that you import the GenerateAuditEvents.ps1 script with:

Import_Module ./GenerateAuditEvents.ps1

When using this script for the first time in order to audit any events, Update-DatabaseForAuditing must be used to update the database. If not executed, you may come across an error messaging mentioning "Invalid column name 'GlobalEventId'". This column is required to fetch all the events from the relevent tables.

After successfully running the Get-AuditEvents cmdlet, a "LastEvents.json" file will be generated in the working directory of the script. This file contains cursor information about the previous execution, which is used in subsequent executions. This will retrieve the new events that have occurred since the last execution. Deleting this file will cause the next run to retrieve all events.

Updating the database with Update-DatabaseForAuditing specifies the following parameters:

  • DatabaseConnectionString: Mandatory parameter used to determine the connection string to connect to the MCE database.

  • SqlScriptPath: Mandatory parameter used to specify the path to the SQL script. The script, "PrimeDbForAuditing.sql", should have been included packaged with this PowerShell script.

  • UpdateType: Mandatory parameter used to specify how you are updating the table columns. Values can be:

  • Add: case-insensitive, will add a unique id column in the tables of the events.

  • Remove: case-insensitive, will remove a unique id column in the tables of the events.

Steps to prime the database for auditing:

  • Open PowerShell instance and navigate to script directory
  • Import-Module ./GenerateAuditEvents.ps1
  • Run Update-DatabaseForAuditing
PS> Update-DatabaseForAuditing -DatabaseConnectionString 'Server=.;Initial Catalog=Mce;Integrated Security=SSPI' -SqlScriptPath "PrimeDbForAuditing.sql" -UpdateType "Add"

Steps to reset the database from auditing changes:

  • Open PowerShell instance and navigate to script directory
  • Import-Module ./GenerateAuditEvents.ps1
  • Run Update-DatabaseForAuditing
PS> Update-DatabaseForAuditing -DatabaseConnectionString 'Server=.;Initial Catalog=Mce;Integrated Security=SSPI' -SqlScriptPath "PrimeDbForAuditing.sql" -UpdateType "Remove"

Generating audit events with Get-AuditEvents specifies the following parameters:

  • DatabaseConnectionString: Mandatory parameter used to determine the connection string to connect to the MCE database.

  • SqlScriptPath: Mandatory parameter used to specify the path to the SQL script. The script, "GenerateAuditEvents.sql", should have been included packaged with this PowerShell script.

  • Timeout: Optional parameter used to specify the wait time (in seconds) before terminating the attempt to execute the sql script and generating an error. If not specified then the parameter will default 0, indicating no timeout limit.

Steps:

  • Open PowerShell instance and navigate to script directory
  • Import-Module ./GenerateAuditEvents.ps1
  • Run Get-AuditEvents using the parameters specified above (use Get-Help for more information on the commandlet)
Get-AuditEvents -DatabaseConnectionString {MyDatabaseConnectionString} -SqlScriptPath {GenerateAuditEvents.sql}

Saving audit events with Save-AuditEvents specifies the following parameters:

  • AuditEvents: Mandatory parameter supplied by the pipeline, this is an ordered list of System.Object each representing an audit event entry.

  • OutFile: Optional parameter which specifies the output file which contains the generated audit events. If not specified, will default to "AuditResults{CurrentTimestamp}.json" saving a file within the running directory, where the timestamp is derived from the time that the script runs.

Steps:

  • Open PowerShell instance and navigate to script directory
  • Import-Module ./GenerateAuditEvents.ps1
  • Run Save-AuditEvents using the output from Get-AuditEvents
Get-AuditEvents -DatabaseConnectionString {MyDatabaseConnectionString} -SqlScriptPath {GenerateAuditEvents.sql} | Save-AuditEvents -OutFile {MyCustomFileName}

Examples#

  • Running against a localhost for the first time, specifying the full path of the SQL script and saving the json file to the working directory
PS> Update-DatabaseForAuditing -DatabaseConnectionString 'Server=.;Initial Catalog=Mce;Integrated Security=SSPI' -SqlScriptPath "PrimeDbForAuditing.sql" -UpdateType "Add"
PS> Get-AuditEvents -DatabaseConnectionString 'Server=localhost;Initial Catalog=MCE;Integrated Security=SSPI' -SqlScriptPath 'GenerateAuditEvents.sql' | Save-AuditEvents
  • Running against a DB with credentials, specifying the name of the SQL script within the same working directory and saving the json file to a separate directory
PS> .\GenerateAuditEvents.ps1 -DatabaseConnectionString 'Server=;Initial Catalog=mce;User Id=myUsername;Password=myPassword;' -SqlScriptPath 'C:\eng\GenerateAuditEvents.sql' | Save-AuditEvents -OutFile 'C:\files\MyCustomFileName'

How to periodically scrape the database for audit events#

Requirements:

  • Windows Task Scheduler
  • Windows PowerShell
  • A MCE Database that has been primed for auditing. (See How to capture MCE group events - How To Run)

While the examples above demonstrate how to run these PowerShell functions manually it is possible to automate this process via Windows Task Scheduler.

Creating a scheduled Task via Task Scheduler#

  1. Open Task Scheduler by searching for "Task Scheduler" via the windows search bar or pressing "Windows key + R" and entering "taskschd.msc".

  2. Click "Create a task" in the Actions section of Task Scheduler.

  3. Enter a memorable name for the name of the task.

  4. Switch to the "triggers" tab and click the "new" button.

  5. You can configure how often the task will be run here.

  6. After creating the trigger navigate to the Actions tab and click "new".

  7. Enter "powershell" as the program.

  8. To provide the task with the script enter the following -File "InvokeGenerateAuditEvents.ps1" -DatabaseConnectionString "Your database connection string" -SqlScriptPath "your directory for the SQL path, or the name of the file if in the same folder" -OutFile "Your audit directory" as the arguments.

    • 8a. Make sure that you do not use single quotation marks.
  9. Enter the path to the directory where you keep the audit script in the 'Start in' field.

The "Conditions" and "Settings" tabs provide additional configuration. Please use them according to your needs.

Creating a scheduled task via power shell#

Alternatively, you can create a scheduled task with the following:

$Trigger= New-ScheduledTaskTrigger -At 10:00am –Daily # Specify the trigger settings
$Action= New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument '-File "InvokeGenerateAuditEvents.ps1" -DatabaseConnectionString "Your database connection string" -SqlScriptPath "your path for the SQL script, or the name of the file if it is in the same folder" -OutFile "Your audit directory"' –WorkingDirectory "The path to the directory where the powershell script is located" #Specify what program to run with its parameters
Register-ScheduledTask -TaskName "Audit MCE group events" -Trigger $Trigger -Action $Action -RunLevel Highest –Force # Specify the name and register the task