Custom Audit::Audit

  • Article

Adds SQL statements to a transaction log for execution against the Microsoft SQL ServerT database for the audit log. Used to modify the audit log for custom reporting purposes. Used by Microsoft Provisioning Framework (MPF).

The Audit procedure executes two kinds of statements:

  • INSERTs that add rows of data into custom database tables

  • EXECUTEs that run custom stored procedures

Arguments

The following table describes the XML elements and attributes. Unless otherwise indicated, the data type is string.

Element Description, relationships, and attributes

column

Description:
Column in the SQL table updated by the SQL INSERT statement. The following code sample shows the usage of the column element.

<table name="titles"> 
  <column name="title_id">123456</column> 
  <column name="title">Job Title</column> 
</table>

Parent:
table

Attribute:

nameName of the column.

exec

Description:
EXECUTE statement for a stored procedure in the audit log database. The following code sample shows the usage of the exec element.

<exec name="proc_calculate_taxes"> 
  <param>WA</param> 
  <param>Sales Tax</param> 
  <param>13</param> 
</exec>

Parent:
executeData

Child:

param (minOccurs="0" maxOccurs="*")

Attribute:

nameName of the table.

executeData

Description:
Encapsulates the procedure's input data.

Children:
exec (minOccurs="0" maxOccurs="*")
table (minOccurs="0" maxOccurs="*")

param

Description:
Parameter for the SQL EXECUTE statement. The following code sample shows the usage of the param element.

<exec name="proc_calculate_taxes"> 
  <param>WA</param> 
  <param>Sales Tax</param> 
  <param>13</param> 
</exec>

Parent:
exec

table

Description:
SQL table to update with the INSERT statement. The following code sample shows the usage of the table element.

<table name="titles"> 
  <column name="title_id">123456</column> 
  <column name="title">Job Title</column> 
</table>

Parent:
executeData

Child:
column (minOccurs="1" maxOccurs="*")

Attribute:

nameName of the table.

Remarks

Audit does not create a database table at the same time it executes an INSERT command. The table must already exist in the audit log.

SQL Provider contains a procedure, SQL Provider:: ExecSQL, that can also be used to execute commands against the audit log. However, be aware that the functionality is not identical. First, ExecSQL executes commands immediately, whereas Audit executes later. ExecSQL will also fail immediately if the audit log database is not available, but Audit will not, since it stores commands in a transaction log database until it is able to execute them.

Note

Audit is slightly simpler to use than ExecSQL because you do not have to specify database or server connection information in the request. MPF is already configured to locate the audit log database.

To successfully execute Audit, the Security Contexts [HMC SDK] user group requires access permissions for any tables and stored procedures referenced by the SQL commands. To grant these permissions, use SQL Enterprise Manager to assign MPFServiceAccts to the role MPFAuditRole for the database MPFAudit. To add tables and stored procedures to an existing audit log database, you must assign appropriate permissions to the MPFAuditRole in that database. Alternately, you can create a new audit log database.

Audit acts only on data previously marked for auditing (for example, all successful transactions). It is entirely possible for the procedure to return successfully but not yield the desired results if the audit log does not contain the expected data. Audit levels for transactions and procedures are defined in Provisioning Manager [HMC SDK1]; for more information, see Provisioning Engines [HMC SDK]. If Transactions marked for audit is selected, transactions that include calls to Audit are eligible.

Schema Definition

Input

<executeData>1..1
  <table name="..">0..unbounded
    <column name="..">1..unbounded</column>
  </table>

  <exec name="..">0..unbounded
    <param>0..unbounded</param>
  </exec>
</executeData>

Sample Code

Example XML Request

The following code fragment shows the format for sending data to this procedure. For more information on individual elements and attributes, see the Elements and Attributes table.

<request xmlns="https://maps/specs/prov.xml"> 
  <procedure> 
    <execute namespace="Custom Audit" procedure="Audit"> 
      <executeData> 
      <!-- INSERT titles (title_id1, title_id2) VALUES ('Col 1', 'Col 2') --> 
        <table name="titles"> 
          <column name="title_id1">Col 1</column> 
          <column name="title_id2">Col 2</column> 
        </table> 

        <!-- EXECUTE proc_calculate_taxes 69, 'C', 'House' --> 
        <exec name="proc_calculate_taxes"> 
          <param>69</param> 
          <param>C</param> 
          <param>House</param> 
        </exec> 
      </executeData> 
    </execute> 
  </procedure> 
</request>

Example XML Response

Audit does not return data.

Applies To

Custom Audit for:

  • Hosted Messaging and Collaboration version 4.5

  • Hosted Messaging and Collaboration version 4.0

  • Hosted Messaging and Collaboration version 3.5

  • Hosted Messaging and Collaboration version 3.0

  • Windows-based Hosting version 4.5

  • Windows-based Hosting version 4.0

  • Windows-based Hosting version 3.5

  • Windows-based Hosting for Applications version 1.0