Comprehensive data protection for all workloads
Post Reply
ratkinsonuk
Expert
Posts: 111
Liked: 16 times
Joined: Dec 10, 2018 10:59 am
Full Name: Robert Atkinson
Contact:

Admin Audit Report using MSSQL

Post by ratkinsonuk »

As a full access audit report isn't natively available through Veeam B&R yet, I've used this SQL query to drive some of the information

Code: Select all

SELECT top 1000 *
  FROM [VeeamBackup].[dbo].[Audit.Records]
  JOIN [VeeamBackup].[dbo].[Audit.RecordParams] ON [VeeamBackup].[dbo].[Audit.RecordParams].record_id = [VeeamBackup].[dbo].[Audit.Records].id
Does anyone have a list of the translations behind the 'operation_type' and 'type' fields please?

Cheers.
Egor Yakovlev
Product Manager
Posts: 2581
Liked: 708 times
Joined: Jun 14, 2013 9:30 am
Full Name: Egor Yakovlev
Location: Prague, Czech Republic
Contact:

Re: Admin Audit Report using MSSQL

Post by Egor Yakovlev »

Hi Robert,

These tables data is used to generate Veeam Backup & Replication Audit Report using Enterprise Manager or via PowerShell.
Check it out!

/Thanks!
ratkinsonuk
Expert
Posts: 111
Liked: 16 times
Joined: Dec 10, 2018 10:59 am
Full Name: Robert Atkinson
Contact:

Re: Admin Audit Report using MSSQL

Post by ratkinsonuk »

Thanks Egor - a very useful cmdlet that I hadn't spotted. Unfortunately, it looks like the info B&R is storing is fairly limited, in that none of the operations I've executed via Powershell are showing in the report.

I guess VeeamOne captures the changes in a more verbose way?
Egor Yakovlev
Product Manager
Posts: 2581
Liked: 708 times
Joined: Jun 14, 2013 9:30 am
Full Name: Egor Yakovlev
Location: Prague, Czech Republic
Contact:

Re: Admin Audit Report using MSSQL

Post by Egor Yakovlev »

none of the operations I've executed via Powershell are showing
That's odd. Can you please share a little more details on operations performed? Are those operations shown under VBR GUI > History node?
ratkinsonuk
Expert
Posts: 111
Liked: 16 times
Joined: Dec 10, 2018 10:59 am
Full Name: Robert Atkinson
Contact:

Re: Admin Audit Report using MSSQL

Post by ratkinsonuk »

The script typically add new repositories and backups, e.g.

Code: Select all

Add-VBRViBackupCopyJob -Name $CopyJobName -Repository $TargetRepository -Description $paramBackupDescription -BackupJob $BackupJobName -DirectOperation  | Out-Null
From what I can see, none of this appears in the audit list or the GUI History pane.

Cheers, Rob.
Egor Yakovlev
Product Manager
Posts: 2581
Liked: 708 times
Joined: Jun 14, 2013 9:30 am
Full Name: Egor Yakovlev
Location: Prague, Czech Republic
Contact:

Re: Admin Audit Report using MSSQL

Post by Egor Yakovlev »

Yes, that's right - Veeam Audit Reports are mostly used to track restore operations.
For your case it will be best to look for Windows Event log event IDs that we push, those have much wider coverage and are easier to track and manipulate with.
wishr
Veteran
Posts: 3077
Liked: 455 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Admin Audit Report using MSSQL

Post by wishr »

ratkinsonuk wrote: Nov 11, 2021 1:04 pm I guess VeeamOne captures the changes in a more verbose way?
Hi Rob,

What particular information you are looking for?
ratkinsonuk
Expert
Posts: 111
Liked: 16 times
Joined: Dec 10, 2018 10:59 am
Full Name: Robert Atkinson
Contact:

Re: Admin Audit Report using MSSQL

Post by ratkinsonuk »

Egor Yakovlev wrote: Nov 11, 2021 2:08 pm Yes, that's right - Veeam Audit Reports are mostly used to track restore operations.
For your case it will be best to look for Windows Event log event IDs that we push, those have much wider coverage and are easier to track and manipulate with.
I've tested the Windows event logs, and the info seems to be in there, although somewhat hidden away in the data payload, but not impossible to access.
ratkinsonuk
Expert
Posts: 111
Liked: 16 times
Joined: Dec 10, 2018 10:59 am
Full Name: Robert Atkinson
Contact:

Re: Admin Audit Report using MSSQL

Post by ratkinsonuk »

wishr wrote: Nov 11, 2021 2:13 pm Hi Rob,

What particular information you are looking for?
Basically, all Administrator events. Such as, backup creation, modification and deletion. The same for repositories, and any other major function. Ideally down to the field level.

From the VeeamOne screenshots I've seen, there are audit reports that cover that level of detail but we don't have an Enterprise+ licence to be able to run VeeamOne.

The information in the B&R System History is pretty slim, but that would be the ideal place to get what we need.

Don't you just love IT Security requirements!
wishr
Veteran
Posts: 3077
Liked: 455 times
Joined: Aug 07, 2018 3:11 pm
Full Name: Fedor Maslov
Contact:

Re: Admin Audit Report using MSSQL

Post by wishr »

Robert,

Certainly, Veeam ONE provides a subset of audit reports which may fit your needs. For example:
Job configuration change tracking
Backup objects change tracking
Backup infrastructure audit

You may give these reports a try using Veeam ONE Community Edition which is free.

Speaking about VBR itself, we are aware of the needs and are planning enhancements for future product versions. Thanks for providing feedback!
ratkinsonuk
Expert
Posts: 111
Liked: 16 times
Joined: Dec 10, 2018 10:59 am
Full Name: Robert Atkinson
Contact:

Re: Admin Audit Report using MSSQL

Post by ratkinsonuk » 1 person likes this post

Thanks Wishr, I'll take a look at Veeam One. Many thanks for your help.
Post Reply

Who is online

Users browsing this forum: Bing [Bot], Semrush [Bot] and 49 guests