Open Forum

Expand all | Collapse all

GP user seats capacity prompt

  • 1.  GP user seats capacity prompt

    Posted 15 days ago
    Trying to determine how frequently our users are impacted by a lack of open seats.

    Does SQL write to a log when someone tries to sign in but all seats are taken? Hoping for a timestamp every time the 'no seats available' dialog is displayed.


    ------------------------------
    Brad Smith
    SE Retail
    Fountain Inn SC
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: GP user seats capacity prompt

    GOLD CONTRIBUTOR
    Posted 14 days ago
    We had a similar need and set up a sql job to check the count from the Activity table and send an email notification when it got close to our limit.  That didn't tell us when users were unable to log in because we were out of licenses, but it did give us a way of knowing how often that was likely to happen.

    ------------------------------
    Jim Harris
    Director of Information Systems
    ExamWorks, Inc.
    Atlanta GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: GP user seats capacity prompt

    TOP CONTRIBUTOR
    Posted 14 days ago
    If you have GP Power Tools by @David Musgrave at Winthrop Development, it has a feature to "Manage user licenses with user activity tracking, automatic logout and login limits.". If not, why not!

    You can roll your own alerts of you are good with SQL. Something as basic as
    select * from ACTIVITY Order by USERID

    Medium Fancy as this SQL View ( I don't remember who gave this to me a long time ago, sorry!)

    USE [DYNAMICS]
    GO

    /****** Object: View [dbo].[view_User_Activity] Script Date: 01/10/2012 15:28:34 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    Create VIEW [dbo].[view_User_Activity]
    AS
    SELECT
    a.USERID GP_User_ID,
    um.USERNAME [User_Name],
    RTrim(a.CMPNYNAM) Company_Name,
    a.LOGINDAT+a.LOGINTIM Login_Date_and_Time,
    dbo.datediffToWords(a.LOGINDAT+a.LOGINTIM, GETDATE ( )) Duration,
    coalesce(b.batch_count,0) Batch_Activity_Records,
    coalesce(r.resource_count,0) Resource_Activity_Records,
    coalesce(t.table_locks,0) Table_Lock_Records

    FROM DYNAMICS..ACTIVITY a

    LEFT OUTER JOIN
    (SELECT USERID, count(*) batch_count
    FROM DYNAMICS..SY00800
    GROUP BY USERID) b -- batch activity
    ON a.USERID = b.USERID

    LEFT OUTER JOIN
    (SELECT USERID, count(*) resource_count
    FROM DYNAMICS..SY00801
    GROUP BY USERID) r -- resource activity
    ON a.USERID = r.USERID

    LEFT OUTER JOIN
    (SELECT Session_ID, COUNT(*) table_locks
    FROM tempdb..DEX_LOCK
    GROUP BY Session_ID) t -- table locks
    ON a.SQLSESID = t.Session_ID

    LEFT OUTER JOIN
    DYNAMICS..SY01400 um -- user master
    ON a.USERID = um.USERID
    GO

    Or
    Look up one of the many SQL code samples posted here in GPUG, like this one from @Steve Erbach
    https://www.gpug.com/viewdocument/user-activity-dashb?CommunityKey=5d86a8ae-6b79-4b9f-b1c7-ed92de67d600&tab=librarydocuments


    ​​​​​

    ------------------------------
    David Morinello
    Senior Dynamics GP Systems Architect
    Ascend Learning, LLC
    Leawood KS
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: GP user seats capacity prompt

    GPUG ALL STAR
    Posted 14 days ago
    Hi Brad,
    it depends.. if you have some $$$ to spend on a 3rd-party tools or not.. GPPT would be one that has a user timeout feature and there is also Willoware GP PowerPack that offers a similar feature. Not to speak about Rockton Toolbox. If you had some budget, I'd definitely go with GPPT, as it offers more bang for the bucks.

    Now, as @David Morinello suggested, there is always the free way. Like his link mentionned, @Steve Erbach & I presented a session in the past 2 Summits about the GP ADmin Dashboard that helps you monitor GP activities from Excel.. no GP client needed.. And the extended version II of that Dashboard also has activity tracking that you can enable with the proper scripts to create a collection table to get a nice graph about your GP license usage.
    Years ago I wrote also a blog post about the topic: https://dyngpgeek.wordpress.com/2013/09/30/tracking-user-activity-in-dynamics-gp
    Good luck.
    ​​

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: GP user seats capacity prompt

    Posted 10 days ago
    Thanks for all that info @Jim Harris, @David Morinello, and @Beat Bucher. We don't mind having a full pool, it's only an issue when a seat is unavailable.

    I've most likely used some/all of these scripts when I created an SSRS Active Users report. The goal is to show who's logged in so they know who to ask for seats. From there then ask those who might be heading into a meeting or lunch. Also, everyone knows who's our resident campers.

    Typically we have just enough seats. Seems to only be an issue when there's the occasional user who remains logged in that causes a seat hunt for others. That's why I was seeking the flag when user capacity is exceeded.

    I'll take your recommendation and setup a SQL notification when user log in time exceeds X hours to flag overnighters. That seems the best approach for our current situation.

    SSRS user activity report screenshot


    ------------------------------
    Brad Smith
    SE Retail
    Fountain Inn SC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: GP user seats capacity prompt

    GPUG ALL STAR
    Posted 10 days ago

    Hi @Brad Smith

    This cannot solve your SQL Server Seat issue, but can help track and keep your GP license usage to a minimum.

    ​https://winthropdc.wordpress.com/2019/02/13/gppt-managing-user-licenses-with-gp-power-tools/

    https://winthropdc.wordpress.com/2019/02/13/gppt-automatic-logout-for-microsoft-dynamics-gp/

    Regards

    David



    ------------------------------
    David Musgrave MVP, GPUG All-Star

    Managing Director
    Winthrop Development Consultants

    Perth, Western Australia

    http://www.winthropdc.com
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: GP user seats capacity prompt

    TOP CONTRIBUTOR
    Posted 8 days ago
    Hello @Brad Smith:

    It sounds like you are looking for something that shows the users who is logged into GP so they can request for those users to exit GP?​ We created a group email (GP Users) that our staff can use to simply send an email asking anyone if they can exit GP. If I want to see how often it happens I use GP Power Tools as @David Musgrave and @Beat Bucher mentioned. It's very helpful when you want to evaluate the need to add another user license.

    Thanks
    ​​

    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: GP user seats capacity prompt

    Posted 8 days ago
    Our seat request workflow is a GP & SalesPad Slack channel ​where users make requests when we're full. This is similar to your email distribution list method.

    Pinned in this channel is a link to a report showing who's logged in and for how long (screenshot below). Some users are known to have quick tasks so they're easy to identify as potential candidates. Others might be those who left the office but remained logged in.

    Some users prefer to discreetly shoulder tap and others openly broadcast a need request. One flaw with the Slack postings is sometimes they don't specify their need being GP or SalesPad but that's a fairly minor issue with a chat tool.


    Our seats are often full and that's okay. It's only an issue when there's that one extra seat need that pops up. I'm not a regular user. When I'm working on a project and need to be logged in I try to do it outside of peak hours.

    We can look at the Slack channel postings to get an rough idea of the frequency when capacity is exceeded. I've since realized this data might be useless. Before going to the trouble of pulling data I try to first address what would move the decision needle. For example, if there were one vs three seat requests per week, does that create a seat purchase need?


    To help shape behavior I send an amusingly naggy 'tsk tsk' Slack msg to the user gently alerting them. It's a silly joke in our office where users 'desperately' want to avoid my nuisance tskery. So much so, when they arrive in the morning and notice they were errantly logged in overnight, they'll quickly log out and back in.

    Part of this tskery is to make users continually aware of seat limitations. Help them keep it top of mind when they're done with a task set. Often its hard to fully understand how people use a system unless you know their job intimately. We think we've got a set of hard-core full-time users and another set of occasional users. There's also user anxiety which drives camper behavior. My gut guess is shaping user behavior can fend off seat purchases.


    SSRS report screenshot @Joni Finnell​. The time bar graph gradually turns more red as it grows.


    ------------------------------
    Brad Smith
    SE Retail
    Fountain Inn SC
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role