Hello everyone, InTrust can collect event log information from many different sources (you can reed more in the datasheet), but what is mostly unknown is that it can collect information from custom logs: a text log, windows event log, WMI or log in a database table. InTrust has a datasource to read audit logs from databases. Let me show you an example of how amazingly this could be used in practice with audit information from Microsoft Dynamics CRM
Dynamics is of course a very powerfull CRM from Microsoft which hleps you manage reletionships with your customers: Sales, Support, Marketing and beyond. Because it could contain information about your customers it's extremely important to protect their data well. MS Dynamics itself has great security controls and features to limit what certain company representative can and cannot see and do. This does not mean however that dedicated audit log is not important, nobody can feel fully protected against insider threats as well as against targeted spear-fishing compaigns on accounts of your eployees. Besides that there are several quite powerfull administrator roles which should be audited as well.
MS Dynamics has internal Audit settings which allow you to create audit trail and manage retention for it, but depending on your audit settings this trail could grow very fast to a very inconvenient size in your Dynamics database which could cause performance issues and concerns about storage requirements to keep this trail in place. Also it's not very easy to search through contents of the audit tables. There is also no export capabilities, so managing and searching through this audit log is quite a challange.
Luckily Quest InTrust can collect DB-based log entries and I will show you how it can be done for Dynamics, although InTrust doesn't have a dedicated Knowledge Pack for Dynamics, you can assume that if something is in the DB table - it can be consumed/collected with InTrust.
First, let's create a query for Dynamics CRM tables with audit logs which suit our rquirements, the first problem that we faced is that Dynamics keeps log entries with Object and user IDs and GUIDs rather than real object/user names, so we have to do some SQL magic to create a user friendly log entries. This query below looks good to me, casting to 4K strings is required for InTrust to correctly collect the data, InTrust can collect strings into event fields which are larger than 4K, but only one and it should be the last column in the query.
You can get the full query code here https://github.com/QSFT/InTrustExtras/blob/master/DynamicsAudit.sql
This query will not work in SQL Management Studio correctly because it's been formatted to work with InTrust, the following line has been added:
Where CAST(tmp.CreatedOn as smalldatetime) >= %LAST_GATHERED_EVENT%
This is required for InTrust to collect only new events from the table.
Now let's configure this datasource on the InTrust side, we will need to configure the connection string to SQL (InTrust offer additional level of protection in terms of storing usernames and passwords in excrypted form inside the product) and a SQL query for the events gathering, %LAST_GATHERED_EVENT% initialization is required for the first time InTrust collects logs, but also could be used to limit history of audit event that InTrust need to collect during first run (Gif animation below), you can also get additional inforation from the manual
Now, right here on the fields mappig I need to stop and expplain a little bit more. You can use any named event string such as "source" or map SQL query columns to Insertion Strings (you have 99 of them). Here is how it looks in my test environment
If necessary you can specify clean up SQL query which will delete data from the table.
Now, we could specify localhost on the connection string and then instruct InTrust to collect data using agents (make sure agent account has permissions to read data from DB or that correct username is specified in the connection string), or you can gather centrally and create a spearate datasource for each Dynamics Deployment. Localhost approach is great because you can simply add a computer hosting Dynamics DB to the site and it will work almost automatically. Speaking of the site, it's a good idea to create one, dedicated for Dynamics servers (Gif animation below)
Now create a task that could be scheduled later and add a Gathering Job into it (you will need to specify gathering policy, but also can create it on the fly), you can choose to collect into a default repository or create a new one. Here is an example of steps (gif animation below). You can also read more about how it all works in InTrust in the user manual
Once data is collected, we can see results in the InTrust Repository VIewer. You can also setup a separate rule to export data into SQL server for future SRS reporting when you can cross-reference some of the GUIDs we collected. Previously I did a post about also straming this data into the PowerBI cloud
The collecting query could be improved to resolve all of the GUIDs into corresponding object names, but there should be a balance of user-friendly logs and acceptable performance of the query, this is important for planning the schedule with which you are collecting the data.
Stay tuned for the followup post on how to make it available in the IT Security Search with more user friendly field mappings.
Thanks.