Sage 100

I have a client that would like to run a report hourly (possibly more often) that reports back the number of users in Sage100. They are on Sage100 Premium and have users all over the world (various time zones) and they are trying to figure out if they have peak and lag periods. I have not found a table that stores that information. anyone have thoughts on how we could accomplish this either in SQL or within Sage?

I would think that even if a table does track that, people tend to stay logged on when they're not actually using the system.

True, but they want to know that too! if they are seeing high volume of users they can look for efficiencies and make users log out.

Activity Log?

Sy_Console (used by Master Console). You could take the Distinct Count of WorkstationName field. You could also link the UserKey to Sy_User table and get everything else you see in Master Console.

Thanks Alnoor, that sounds like a winner!

Doesn't that file get cleared when people log out? Seems like you'd have to poll the file every so often (every 15 minutes) in order to get your count data per time period.......

That is what they want to do - they want to run a crystal report every hour (or perhaps more often) that shows the number of users logged in at regular intervals 24 hours a day to see when the most usage is happening.

Sounds like a scripting project then!

I would suggest creating a table to store the date/time and number of users, and writing a stored procedure that queries SY_Console and writes the results to the custom table. Then a Crystal report can read that table, maybe with a date range parameter. The stored procedure can be scheduled as a SQL job.

I had pondered same thing of mirroring Sy_Console to another table for current and historical querying purposes.

I need to revisit this issue; the client is on Sage100 Premium. and SY_Console is not in the SQL instance. I think it is one of the system files that remain as PVX in the MAS_System Directory. Is there any way to look at the data in this table through crystal or SQL?

Would something like this work - http://www.dsdinc.com/enhancement/lm-1031-user-login-activity-log/ -- I had a banking customer once whose auditors requested a security log. We used this and as I recall it writes out a record to the activity log for each login and each logout (I do not think stock Sage records every access). Then create a report on the activity log and seems like you'd be done. A couple cautions - currently at 5.30, it appears this only logs for total access and not by a company and the price is steep at $1,830.

I think the price tag on that is more than this client would be willing to accept. good to know that is out there though. We have a couple of clients in the medical industry that might be interested in that.

David, although there is no SOTAMAS90 DSN with Premium see if the ProvideX ODBC driver for it is installed (goto 32-bit ODBC Administrator / Drivers tab look for ""MAS 90 4.0 ODBC Driver""). If found, try to add a System DSN for it and see if you can view Sy_Console in Crystal. If that's all you need you're set. But if you need to also join it to Sy_User and Sy_Company to get user and company names you could create a SQL Linked Server to make it accessible to SQL. Then in Crystal you would join the Linked Server version of Sy_Console to the MAS_System db version of Sy_User and Sy_Company. Alternatively you can have a process that mirrors Sy_Console to a SQL custom table via like Phil was suggesting. There is an Evernote KB article on how to setup the Linked Server but it relies on the ""MAS 90 4.0 ODBC Driver"" being present. But if ProvideX ODBC isn't available (then you could create your custom pvx program that mirrors Sy_Console to a SQL custom table then schedule that program to run every X minutes to refresh the table.

I did see the MAS 90 4.0 ODBC Driver and tried to create a 32 bit odbc system DSN but when I did test database it kept telling me login failed (without ever prompting me for a login. and I tried adding username and PW to the DSN and that didn't change the result. At that point, I thought maybe ProvideX ODBC just was not an option with Premium, but if you think it should be working, I will go back and try that again this afternoon. I double checked all my settings when creating the DSN and I've created Sage DSN's a hundred times before, but I might have missed something obvious. I try to avoid custom programming when possible, but I might need to do that in this case.

David I'll send you a chat