InTrust 11.6 - Auditing Custom Logs with InTrust

Example of Database Events Data Source

This example describes how you can transfer data from an InTrust audit database to an InTrust repository. InTrust does not provide a job type that does this. However, you may want to move data from a database to a repository in some situations.

The following information is not gathered in this example:

• Information about computers that the data originally came from
  This data will be replaced with information about the SQL Server computer and the database.
• The RecordNumber field
  This is an auxiliary field that has meaning only for Quest development and support.
• The GatheringComputer field
  InTrust always fills in this field automatically with the name of the computer that is running the current gathering session. Any original values would be lost anyway, so they are not gathered.
• The EventLog field
  Information for this field comes from the name of the log in the database events data source. In the example, only security events are gathered, so specify Security as the log name.

To transfer data from an InTrust audit database to an InTrust repository

1. Start creating a new database events data source.
2. On the SQL Query step, supply the following query:

select

e.ID,e.SessionID,e.VersionMajor,e.VersionMinor,

e.Computer,e.UserName,e.UserDomain,e.EventType,e.Source,e.EventID,e.Category,e.GMT,e.LocalTime,

s.S1,s.S2,s.S3,s.S4,s.S5,s.S6,s.S7,s.S8,s.S9,s.S10,s.S11,s.S12,s.S13,s.S14,s.S15,s.S16,s.S17,s.S18,s.S19,s.S20,s.S21,s.S22,s.S23,s.S24,s.S25,s.S26,s.S27,s.S28,s.S29,s.S30,s.S31,s.S32,s.S33,s.S34,s.S35,s.S36,s.S37,s.S38,s.S39,s.S40,s.S41,s.S42,s.S43,s.S44,s.S45,s.S46,s.S47,s.S48,s.S49,s.S50,

isnull(d.Description,'') Description

from

Events e

inner join

(

select

e.ID,

e.SessionID,

max(case s.StringIndex when 1 then s.StringValue else null end) S1,

max(case s.StringIndex when 2 then s.StringValue else null end) S2,

max(case s.StringIndex when 3 then s.StringValue else null end) S3,

max(case s.StringIndex when 4 then s.StringValue else null end) S4,

max(case s.StringIndex when 5 then s.StringValue else null end) S5,

max(case s.StringIndex when 6 then s.StringValue else null end) S6,

max(case s.StringIndex when 7 then s.StringValue else null end) S7,

max(case s.StringIndex when 8 then s.StringValue else null end) S8,

max(case s.StringIndex when 9 then s.StringValue else null end) S9,

max(case s.StringIndex when 10 then s.StringValue else null end) S10,

max(case s.StringIndex when 11 then s.StringValue else null end) S11,

max(case s.StringIndex when 12 then s.StringValue else null end) S12,

max(case s.StringIndex when 13 then s.StringValue else null end) S13,

max(case s.StringIndex when 14 then s.StringValue else null end) S14,

max(case s.StringIndex when 15 then s.StringValue else null end) S15,

max(case s.StringIndex when 16 then s.StringValue else null end) S16,

max(case s.StringIndex when 17 then s.StringValue else null end) S17,

max(case s.StringIndex when 18 then s.StringValue else null end) S18,

max(case s.StringIndex when 19 then s.StringValue else null end) S19,

max(case s.StringIndex when 20 then s.StringValue else null end) S20,

max(case s.StringIndex when 21 then s.StringValue else null end) S21,

max(case s.StringIndex when 22 then s.StringValue else null end) S22,

max(case s.StringIndex when 23 then s.StringValue else null end) S23,

max(case s.StringIndex when 24 then s.StringValue else null end) S24,

max(case s.StringIndex when 25 then s.StringValue else null end) S25,

max(case s.StringIndex when 26 then s.StringValue else null end) S26,

max(case s.StringIndex when 27 then s.StringValue else null end) S27,

max(case s.StringIndex when 28 then s.StringValue else null end) S28,

max(case s.StringIndex when 29 then s.StringValue else null end) S29,

max(case s.StringIndex when 30 then s.StringValue else null end) S30,

max(case s.StringIndex when 31 then s.StringValue else null end) S31,

max(case s.StringIndex when 32 then s.StringValue else null end) S32,

max(case s.StringIndex when 33 then s.StringValue else null end) S33,

max(case s.StringIndex when 34 then s.StringValue else null end) S34,

max(case s.StringIndex when 35 then s.StringValue else null end) S35,

max(case s.StringIndex when 36 then s.StringValue else null end) S36,

max(case s.StringIndex when 37 then s.StringValue else null end) S37,

max(case s.StringIndex when 38 then s.StringValue else null end) S38,

max(case s.StringIndex when 39 then s.StringValue else null end) S39,

max(case s.StringIndex when 40 then s.StringValue else null end) S40,

max(case s.StringIndex when 41 then s.StringValue else null end) S41,

max(case s.StringIndex when 42 then s.StringValue else null end) S42,

max(case s.StringIndex when 43 then s.StringValue else null end) S43,

max(case s.StringIndex when 44 then s.StringValue else null end) S44,

max(case s.StringIndex when 45 then s.StringValue else null end) S45,

max(case s.StringIndex when 46 then s.StringValue else null end) S46,

max(case s.StringIndex when 47 then s.StringValue else null end) S47,

max(case s.StringIndex when 48 then s.StringValue else null end) S48,

max(case s.StringIndex when 49 then s.StringValue else null end) S49,

max(case s.StringIndex when 50 then s.StringValue else null end) S50

from

Events e

left join EventsStrings s on s.SessionID=e.SessionID and s.EventID=e.ID and s.StringIndex<=50

group by

e.ID,

e.SessionID

) s

on s.SessionID=e.SessionID and s.ID=e.ID

left join EventsDescriptions d on d.SessionID=e.SessionID and d.EventID=e.ID

WHERE EVENTLOG = 'Security' and GMT >= %LAST_GATHERED_EVENT% ORDER BY GMT

3. On the same step, specify a date as the value for the %LAST_GATHERED_EVENT% variable. It should be a date that you know precedes the earliest event’s date. Use the following format: 2000-01-01 00:00:00.
4. Specify “Security” as the name of the log.
5. When configuring field mapping, map fields to their counterparts. For example, map Computer in the left column to %Computer% in the right. Map LAST_GATHERED_EVENT and GMT to %GMT%. If you want to map insertion strings, use syntax such as %S1% in the right column.
6. Leave the cleanup query blank. Only a subset of data available in the database is gathered, and there is a lot of other useful information in it.
7. Give a descriptive name to the new data source.
8. Specify a valid license.
9. Complete the wizard and commit the changes you have made.

External Events Data Sources

The External Events data source type is not represented by any predefined data sources. It is different from other data source types in that it generates event records with fields that you define and hands them over to the InTrust agent to process.

Data sources of this type are represented by a command-line utility on the agent side and an InTrust data source object on the InTrust server side.

This command-line utility forces special events on the InTrust agent running on the same computer. The agent stores the events in its backup cache. From there, the events can be captured by the gathering or real-time monitoring engine.

To create an External Events data source

1. Right-click the Configuration | Data Sources node and select New Data Source.
2. In the New Data Source Wizard, select the External Events data source type.
3. Complete the remaining steps.

For details about External Events data source settings, see the Configuring Data Sources topic the InTrust Auditing Guide.

Script Event Provider Data Sources

InTrust provides an additional option to create a custom data source using the Script Event Provider.

This functionality allows you to create a script that starts with pre-set frequency. Under some conditions that are specified in this script events are generated and then are passed to the InTrust agent. Events are stored in the agent's backup cache. From there, the events can be captured by the gathering or real-time monitoring engine.

You can specify in the certain script: what information is stored and how it is ordered in the certain events, what conditions are required for event generation.

To create a custom data source with Script Event Provider

1. Right-click the Configuration | Data Sources node and select New Data Source.
2. In the New Data Source Wizard, select the Script Event Provider data source type.
3. On the Script step select the script language and enter your script text using XML editor.
4. On the same step specify a frequency of the script running.
5. Complete the remaining steps.

Pattern Letters for Date and Time Designation