Can a BAQ access the data from the User Tracker?

I can’t seem to figure out how to get to the data that is displayed in the User Tracker from within a BAQ. I have a backup copy of my database (I’m hosted in DT, so I can’t just query the database directly) and I think maybe the tables I need access to aren’t available to be queried with a BAQ?

It looks like Ice.SysLock and Ice.SessionState are both candidates for where this data could be. I don’t see either of these tables as choices when I make a BAQ - everything on either side shows up in the list but not these tables.

Any ideas?

Those tables aren’t exposed to the BAQ Designer.

May I ask what the user tracker cannot do that you’re trying to recreate it?

I would like to capture my logged in users on an interval and log who is in the system / not in the system. I want this to run every (x) minutes of the day and I will be logging it to a SQL server for reporting. If I could write a BAQ that returned the CurComp, CurUserID, and expired - that would be enough for me to supply the timestamps on the SQL side so I could trend this over time.

1 Like

A few different approaches, in no particular order:

  • If you enable system activity tracking, you can determine who is logged in and when as their login events are logged into ice.systemactivitylog which has an existing dashboard that can be used to retrieve that data. There are checkboxes on the company record to enable it. Support can provide information on the particulars and there is information on it in application help.

  • You can get the session data via REST services in E10.1.600.x Perhaps someone else in the community could assist you with the basics of extracting that as it is a little outside of my expertise. REST has to be enabled on the DT side, so if you haven’t requested that it be enabled up to this point, please contact Epicor Support and we can get that enabled for you. An overview of REST can be reviewed here: REST Overview Novel

  • I have a third approach but I’d have to discuss it further with our DT Operations Director first before I over-promise. (spoiler: external data sourced BAQs)

2 Likes

Thanks for the ideas. I’ve tried to enable login events - the only activity I get in the system activity log is failed login events. I followed directions in this thread User Access Tracking and posted that in DT that area of configuration is blank - maybe I need to reach out to support to have it enabled for me?

I was looking into REST. It looks like that is in Ice.BO.AdminSessionSvc? Right now 504 has an issue in PROD, but Pilot does have that service so I could try there.

Item 3 looks very interesting to me :slight_smile:

I have a fourth approach (assuming that the DT/MT licensing doesn’t prevent access to those tables–I’m not sure)

  • Technically, one could query this data via a custom code BPM and create new records in say a UD10x table. Then, you could create a standard BAQ against that UD10x table with the conditional logic you wish to use. If your C# fu is pretty good, you could attempt to do that now. To have it run every X minutes, you’d need to trigger it on something that runs all the time. You could latch it onto GetRowsKeepIdle on the ReportMonitor which executes every 3-10 seconds (depending on how your system agent is configured), BUT, that would negatively impact all client-side printing performance to some extent.

I think this is my target. I’ve got some c# fu but not as much as option 4 requires :slight_smile: I’m poking around with this in Pilot right now.

I ended up using SQL SSIS package using an OData connection to grab the data from Ice.BO.AdminSessionSVC. If anyone is interested, I could put together a quick overview of how to do this. It turns out it’s pretty easy once you know what you are doing :slight_smile:

Looks like this is a somewhat old thread, but I’d be interested in seeing your overview of how this is accomplished if possible!

Sure, where do you want me to start from :slight_smile: I will see how much I can screenshot without giving away too many details. This will assume that you are MT, DT, or possibly Single Tenant, I only know for sure that MT and DT have the web services enabled.

OK here is a bit of a summary. I haven’t gone into complete detail, just showing you the bits that are common to any attempt at this. You’ll want to work with your development staff and IT team to figure out security, etc. etc. your mileage may vary :slight_smile: Here is one way to do this.


  1. In Epicor, create a username with the permissions required to access this information.

  2. Create an OData Connection Manger in an SSIS project to connect. Use the account from step 1 here. I’ve redacted the URL and user here. Test your connection
    image

  3. You should see
    image

  4. Create a connection to your local SQL server – I’ve redacted my server name here. This job runs sceheduled in SQL agent and uses the credentials of the agent account, you may need to set permissions here depending on your security model.
    image
    Test your connection. I’m running SQL server 2016, you may need a different native client.

  5. In the SSIS package, I have a simple control flow in my UserLog.dtsx package. The first step truncates a holding table, the last steps checks for data in the holding table and moves it to the historical table. The interesting bit is the data flow task…
    image

  6. In your data flow task, you need a source and destination.

image
Here is how I have the OData source set up


If you’ve done it correctly and have everything enabled, you should see data when you hit preview
image
Map your columns in the Columns heading on the left – I just grab everything. Set up your error output per your development standards…

Set up your local SQL server with an OLE DB Destination and configure it. Here’s mine.

And set your mappings up. Here I’m keeping the same names as this is a staging table.