We would like to closely monitor our DefaultUser & DataCollection sessions so that we can better understand how our licensing needs are changing. We want to take a snapshop of the # of each License type each hour.
If I could do a BAQ for it, I was thinking I would use a Windows Scheduled DMT Powershell script, to call the BAQ & export the results to a csv file, and then import that data into UD100. The DMT Powershell bit I’ve done before… provided I can get a BAQ to dump the data I can then schedule the Powershell script every hour and effectively end up with an hourly session log in the UD100 table.
In the referenced discussion, it says that in E9 this data was in LicUser table and in 10.1 this was moved to the Ice.SessionState table, however I am on 10.0, what table is the data in 10.0.?
If anyone has other ideas on how I can get an hourly snapshot of the license count of these two types of licenses…?
Thanks!
Let me know if you are interested in how this can be done using SQL Server tools (SSIS for the package, SQL Agent for scheduling)
I dump the data into a table every 15 to coincide with my timeout length. I then do my analysis in Excel with some pivot charts that I built against views of that data (hourly average by license type, last 24 hours, etc.).
The problem you will have with a db query is that the license data is cached in the app servers themselves, not the database. There is a background task to flush session to db or do UDP peer to peer broadcasting, etc for how you want to manage state on your servers. Luckily, the SessionTypes are easy to query against the REST API and kick with curl.
You can look at the Session List like you would in Admin Console via Ice.BO.AdminSessionSvc/List
I am filtering to just active sessions and their ‘SessionType’ (more in a second)
This will give you the collection of Sessions and their attributes. Next, you need to translate those Guids to their license equivalents. You can decompile Epicor.ServiceModel for those constants or I can build a list if you need.
Next, call this via curl as the example describes. Place that curl command in Windows Task Scheduler and have a dump done every hour or whatever.
Just to be clear, I’m using the REST services - just accessing them from SSIS. No Epicor table access here…
Create an OData Connection manager (in this case to my Epicor dedicated Tenancy REST Service. We want the AdminSessionSvc). Use your credentials you’ve previously set up for running REST queries
For the destination, make a connection to a local database on your SQL server.
Create a simple control flow. In this case step 1 truncates the holding table, step 3 inserts from the holding table. Step 2 is where i get the data, in the data flow task…
Map your source data to a table in your destination in the data flow task
Schedule using SQL agent. It’s a simple 1 step SQL Agent job. In this case I’m loading it from the file system, in your case you will use the integration services package store most likely.
I know this is old but it is still valid. I have never used SSIS so had some learning to do. Once I installed SSIS on my development SQL server and learned how to install MSDT I was off to the races.
I did find that the filter spec on the REST service did not work. The syntax was valid but when I tried to limit my query to only get entries that had InUse set to true it returned all the records. I inserted a Conditional Split transformation in the stream to filter out the records I wanted to ignore.
My plan is to use this to monitor license usage throughout the day to make sure we are not near our limit.
