topic Re: Audit Log Query in Product Forum

Audit Log Query

Jrwork — Tue, 10 Sep 2024 20:02:48 GMT

Hey guys! how are you? I hope everything is ok!

I need guidance on how to consult user information by consulting the audit log, ex:

In this case, I need to consult the user "ual.jbj" on a specific day and thus analyze the performance logs on the server.

Can anyone advise how to query this table?

thank you very much!

Re: Audit Log Query

david.l.morris — Wed, 11 Sep 2024 03:06:49 GMT

Can you explain in more detail what you're looking for? You showed a screenshot of searching in the audit log. That is the way to search it. Are you talking about querying the database table directly? And, if so, explain what you've tried that didn't work or what specifically you're unsure how to do. For example, are you unsure how to connect to the database? Are you unsure how to form SQL queries? Are you unsure what the table name is?

Re: Audit Log Query

Jrwork — Wed, 11 Sep 2024 11:01:46 GMT

@david.l.morris

Hello! I just didn't express myself with the details, I'll pay attention next time!

Not sure what the name of the table is? So, I'm not sure how to form the SQl query for this table (I don't know what it is yet).

Re: Audit Log Query

david.l.morris — Wed, 11 Sep 2024 16:45:12 GMT

I see. No worries. I'll describe some things below that may help you in the right direction. But just recognize that typically it is not recommended to do your own queries against the Blue Prism database. So, you ideally should limit how often you do this kind of thing. That said, I have found many times that I just don't have any other option for certain functionality without directly querying the database. I should also mention that it's been suggested you should replicate/copy your database and query the copy. But SQL Server can handle a lot, so it's probably fine.

So, there are some pieces of information you need to find out that the database administrator (DBA) could help you find out:

Database Server Name
Database Name
How to authenticate (maybe a special username/password with Read-only access or your own ID may be given read access)

I recommend you test this out in an environment that is not important first. For example, on your home computer, you could set up the Learning Edition since you'll have full access to the database there.

Download/install SQL Server Management Studio (SSMS): https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

When you open SSMS, it will ask you to enter login details. This is where you'll input the Database Server Name and authentication details. This part I can't really help you with directly for real databases because I won't know any of those details. But for the Learning Edition, most likely the details will be this:
Server Name: (LocalDB)\BluePrismLocalDB
Authentication: Windows Authentication

Once you've connected, expand the nodes out like this: (LocalDB)\BluePrismLocalDB > Databases > BluePrism > Tables > dbo.BPAAuditEvents

You can then right click on dbo.BPAAuditEvents and choose "Select Top 1000 Rows" which will open a query window and will do a quick query that will look like this:

From here, you can play around with determining exactly which fields you want and such. I don't know what purpose you're doing this for so I don't know how to suggest further steps. You could call this query from inside a Blue Prism process, a PowerShell script, or just manually go do these queries in SSMS periodically. But if you're just going to look at a particular user's activity, you can and should prefer doing that from in the Blue Prism UI.

Edit: One final thing I'll suggest. I assume you're new to SQL queries. A solid way to search for how to do queries is to just always include "SQL Server Query" or something like that in your search term. I believe it's called Transact SQL or T-SQL, but I've always found I can ignore that concept and just search for queries that work in SQL Server and then type whatever I need like "SQL Server Query two tables" or "SQL Server Query filter by date range", and there are many articles and posts online about this kind of thing.

Re: Audit Log Query

Jrwork — Wed, 11 Sep 2024 18:40:30 GMT

@david.l.morris

Master, thank you very much!

You explained it in a very detailed way and it was possible to carry out a consultation and capture the information I needed, thank you very much!

I selected it as the best answer and once again, THANK YOU!!