User ACTIVITY table - IM login 1

[baugie]

I am creating an automated integration process, and we are using Integration Manager which requires a login to the Dynamics interface. We are contemplating several ways to insure a seat is available for this process. I know that the currently logged in users are stored in the ACTIVITY table.

I used the Dynamics User Activity screen to remove a user and monitored the SQL Server activity with a profiler. I noticed that there were several checks to other tables before it removed the user from the ACTIVITY table.

Does anybody know what dependencies there are outside of the ACTIVITY table when a user is removed? If I want to 'lock' a seat, is this the only table that I need to work with? By lock, I mean put a record in the ACTIVITY table through a program between integrations to save the seat for the integration, and remove when the integration logs in.

My other option is to actually boot somebody out of the system automatically. The ACTIVITY table stores login time, but is there another table that stores most recent sytem activity? Helpful in determining who to target.

Any ideas on how this can be done, or other ideas that you may have used to address the Integration Manager auto-login would be helpful. Thanks in advance!

 

[Luvsql]

The key tables GP checks when a user is logged in is the ACTIVITY, SY00801 and SY00800. SY00800 is the batch activity table, so if a user is actually doing anything in GP with a transaction, that user ID will appear in this table. If the user is just logged in not doing any inserts/deletes/updates to a transaction, there will be no occurrence of that user in the SY00800. Also the SY00801 table is checked (resource activity).

 

[stef315]

I believe that when you delete the user out of the Activity table it will remove them from the other places also. Right Luvsql? If you have third party products and possibly some other add-ons there may be other activity files.

However, I'm wondering about this. Are you sure that you want to boot a user out automatically? What if they are in the middle of processing a check batch or something?

Why are you having to boot them out? Are they leaving it logged on overnight or something? Are they just constantly not having enough users? Should they consider purchasing another user or would that just be taken by another user (not IM) anyway?

What kind of integration are you doing? If you are doing an integration that can use the SQL Optimized adapter you can circumvent having to log in at all. That way you don't take up a valuable user and you don't have to blindly boot another user out.

 

[baugie]

Those are all good questions Steph315, and here are your answers.

We are not using any 3rd party add-ons so we only have to worry about GP activity files.

Users have a tendency to log on, and stay logged on all day regardless of their activity level.

luvsql gave me the insight on the SY00800 table, so if they are updating a batch or transaction of some kind then I will know it and won't kick them out. My goal was to also find a general activity table that would show any and all activity, but I havn't found it yet. About once a week all of the seats are used up and nobody can log in, so this is an issue. The business owner of the system is looking into more seats, but that is not something we can plan on at this point.

The integration will run at various times throughout the day (currently it could run every hour). We are integrating many different systems who process time sensitive production requests from customers. However, certain required production documentation cannot be created until the order is in GP so this has to be somewhat close to real-time (which is why hourly could be moved to closer intervals).

I tried to use SQL Optimizer (first choice) but when I did a test run it told me that it could not process because inventory bins had been turned on in our system (which is an undocumented hinderence), so that was ruled out.

So I am relegated to using IM, and running it automatically from my own home grown procceses which will do other things with the data before giving it to IM. The only hinderence is the possible lack of a log-in seat, so I am creating a work-around for the inevitable situation. I most likely will just hold onto a seat within the ACTIVITY Table, basically reducing our available seats by one at all times. I was just looking for ways to find inactive users in the system.

If there are any other ideas out there for a better work around then I would love to hear them! Thanks again in advance!!

baugie

 

[stef315]

Okay, I see now that you were really looking for INACTIVE users who were basically just sitting there!

Yes, that inventory bins is a big problem. Stinks because you would have actually been able to use SQL Optimized!

It's funny that companies can't just make a policy that users have to log off when they are not doing anything. I've seen this so many times. Problem is, they get scared they won't be able to get in when they need to.

So, you can just take a user for IM and keep that user all the time. Either leave Great Plains open or falsely put your IM user in the ACTIVITY table after exiting GP. Then the integration kicks that user out before running.

But your question really was is there a place to look for what the users are actually doing. Are you using a Process Server by chance? I've never used that but it's got to have what processes are being ran and maybe you can tell which user.

The SY00800 file that luvsql mentioned will show all users who are doing something. If they are posting or editing a transaction, they should be in there. I would say you're pretty safe to delete a user out of ACTIVITY if they are not also in SY00800.