Is it an option for you in your situation to encourage users to manually synch their GP passwords to their AD/WIndows login passwords? I've only worked for companies with fewer than 10 GP licenses and that's what we've done. Whenever AD/Windows demands that a user change her/his password, the users know to change it in GP, too.
Original Message:
Sent: 01-24-2019 01:09 PM
From: Michael Barnes
Subject: IDLE Users - Users who appear active
Similar ratios here, just a whole lot more of them both in user head count and avail licenses.
I am leaning to the FastPath toolset for this because we are looking also at their pseudo "single signon" as an added bonus.
Off topic a bit here but, much of the issue of people remaining in is due to the tedium and time of our three step process to get to GP via Remote Desktop. Added to that, they have a hard time separating in their minds when to use AD and GP user ID and passwords leading to a high number of support cases each month regarding locked user accounts.
Thanks again for your and all the replies here.
------------------------------
MB Barnes
IT Manager, Enterprise Systems
Kenco Group, Inc
Chattanooga TN
Original Message:
Sent: 01-24-2019 12:50 PM
From: Beat Bucher
Subject: IDLE Users - Users who appear active
Hi @Michael Barnes,
I've been using reliably the Timeout feature from the Rockton Toolbox for many years and had only 16 concurrent licenses shared among over 100 GP users, granted that my average users are only logging into GP about 1 or 2 times a month, but my core GP users (about 20-30) were in GP every day and never had to fight for a license thanks to the RSTB.
Another company I had worked for several years ago had roughly the same total GP user count, but kept buying licenses to fill the need for a growing business, but never wanted to look at the RSTB option.. which would probably saved them a lot of cash.
------------------------------
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)
Original Message:
Sent: 01-23-2019 04:19 PM
From: Michael Barnes
Subject: IDLE Users - Users who appear active
Thanks for sharing it,
It has however created for me a bit of concern considering we are looking at implementing FastPath here.
It is producing nearly the same output as mine (+/- 60 sec) and same record count which suggests the "Check User Messaging" is corrupting the results even in their solution... I had yet again today a handful of people with locked workstations who have been at lunch for a period of time, showing up as only 1 min idle.
------------------------------
Michael Barnes
IT Manager, Enterprise Systems
Kenco Group, Inc
Chattanooga GA
Original Message:
Sent: 01-23-2019 12:24 PM
From: Frank Heslin
Subject: IDLE Users - Users who appear active
I've been using this script that I got from FastPath (I removed a couple of lines that are related to FatsPath's Config AD product that we use):
SELECT DISTINCT
a.USERID ,
a.CMPNYNAM Company_Name,
p.last_batch [Last Batch Time],
datediff(mi, p.last_batch, getdate()) as [MinutesInactive],
c.USERID [SY00800],
d.USERID [SY00801],
a.SQLSESID
FROM DYNAMICS..ACTIVITY a
INNER JOIN tempdb..DEX_SESSION s
on a.SQLSESID = s.session_id
INNER JOIN sys.sysprocesses p
on s.sqlsvr_spid = p.spid and ecid = 0
LEFT JOIN DYNAMICS..SY00800 c on c.USERID = a.USERID
LEFT JOIN DYNAMICS..SY00801 d on d.USERID = a.USERID
ORDER BY [MinutesInactive] DESC, USERID
It seems to be very accurate.
------------------------------
Frank Heslin
Financial Systems Administrator
ExamWorks, Inc.
Atlanta GA
GPUG Advisory Committee Member
Summit Track Leader
GPUG All Star 2016
GP2018
MR CU16
Original Message:
Sent: 01-23-2019 02:37 AM
From: Gavin Hundermark
Subject: IDLE Users - Users who appear active
I was doing the same exercise late last year, and got to pretty much the same point as @Steve Erbach.
Will be nice to see if anybody can get something more
------------------------------
Gavin 'Yoda' Hundermark
Technical Support Specialist
African Global Operations (Pty) Ltd
Krugersdorp South Africa
Original Message:
Sent: 01-22-2019 02:11 PM
From: David Musgrave
Subject: IDLE Users - Users who appear active
@Jo deRuiter
It turns out that the timed process for sending messages runs every minute and accesses the tables in SQL which resets the last_batch from sys.sysprocesses value meaning that at the SQL level, it will always look like there is activity even if the user has done nothing.
I am trying to work a way around this without just kiling the "Check for User Messages" Timed process in the Process Monitor.
@Michael Barnes This is your ghost process. Or at least one of them.
Any ideas @Steve Erbach or @Beat Bucher
David
------------------------------
David Musgrave MVP, GPUG All-Star
Managing Director
Winthrop Development Consultants
Perth, Western Australia
http://www.winthropdc.com
Original Message:
Sent: 01-16-2019 09:42 AM
From: Steve Erbach
Subject: IDLE Users - Users who appear active
@Jo deRuiter,
Dunno. None, I suppose. I kinda like this geeky sort of discussion about wringing more information out of GP than it, apparently, is able to give.
Regards,
------------------------------
"Sparkly" Steve Erbach - Green Bay, WI
Co-Chair, GPUG WI (Green Bay) Chapter
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: twitter.com/serbach
───────────────
Excel Webinar List
Original Message:
Sent: 01-16-2019 09:05 AM
From: Jo deRuiter
Subject: IDLE Users - Users who appear active
LOL..
@Steve Erbach then might I inquire as to WHAT reaction you were expecting??
------------------------------
Kindest Regards,
Jo deRuiter , MCP, DCP
"That GP Red Head"
AISLING DYNAMICS CONSULTING, LLC
WEBSITE: https://aislingdynamics.com/
BLOG: https://community.dynamics.com/gp/b/gplife
GPUG Academy Instructor
Dynamics GP Credentialing Council-Vice Chair
770-906-4504 (Cell)
Original Message:
Sent: 01-16-2019 08:57 AM
From: Steve Erbach
Subject: IDLE Users - Users who appear active
@Jo deRuiter,
I did not expect to get that kind of reaction!
Regards,
------------------------------
"Sparkly" Steve Erbach - Green Bay, WI
Co-Chair, GPUG WI (Green Bay) Chapter
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: twitter.com/serbach
───────────────
Excel Webinar List
Original Message:
Sent: 01-16-2019 08:11 AM
From: Jo deRuiter
Subject: IDLE Users - Users who appear active
@Steve Erbach
I'm definitely going to try this script - it's exciting!
Thanks for sharing!
------------------------------
Kindest Regards,
Jo deRuiter , MCP, DCP
"That GP Red Head"
AISLING DYNAMICS CONSULTING, LLC
WEBSITE: https://aislingdynamics.com/
BLOG: https://community.dynamics.com/gp/b/gplife
GPUG Academy Instructor
Dynamics GP Credentialing Council-Vice Chair
770-906-4504 (Cell)
Original Message:
Sent: 01-15-2019 05:15 PM
From: Steve Erbach
Subject: IDLE Users - Users who appear active
@Michael Barnes,
I had a similar concern when I developed my GP Admin Dashboard. That single inactivity number of minutes didn't seem to give me much.
So I figured out a way to get the number of minutes since the next-to-last activity. Here's the query I use:
SELECT RTRIM(um.USERNAME) AS [User Name]
, CASE um.UserType
WHEN 1 THEN 'Full'
WHEN 2 THEN 'Limited'
WHEN 4 THEN 'Self-serve'
ELSE '---'
END AS [Type]
, RTRIM(da.CMPNYNAM) AS Company
, FORMAT(da.LOGINDAT, 'yyyy-MM-dd', 'en-us') + ' '
+ FORMAT(da.LOGINTIM, 'HH:mm', 'en-us') AS [Logged in at]
, CONVERT(varchar, ABS(DATEDIFF(minute, da.LOGINDAT + da.LOGINTIM, GETDATE())))
+ ' minutes' AS [For how long?]
, da.SQLSESID AS [Session]
, S.sqlsvr_spid AS GP_spid
, (
SELECT COUNT(*)
FROM [master].sys.sysprocesses AS ms
WHERE (loginame = da.USERID)
) AS Spid_count
, CASE ISNULL(om.USERID, '---')
WHEN '---' THEN 'OK'
ELSE 'No SQL session'
END AS [Status]
, ISNULL(RTRIM(P.[status]), 'No match') AS [Proc status]
, ISNULL(
( SELECT FORMAT(MIN(last_batch), 'h:mm tt', 'en-us') AS LastBatch
FROM (
SELECT TOP (1) last_batch
FROM [master].sys.sysprocesses
WHERE (loginame = da.USERID)
ORDER BY last_batch DESC
) AS m
), 'xxx' ) AS [Last]
, ISNULL(DATEDIFF(mi, (
SELECT MIN(m_3.last_batch) AS LastBatch
FROM (
SELECT TOP (1) last_batch
FROM [master].sys.sysprocesses
WHERE (loginame = da.USERID)
ORDER BY last_batch DESC
) AS m_3
), GETDATE() ), - 999 ) AS [Mins.]
, ISNULL(
( SELECT FORMAT(MIN(m_2.last_batch), 'h:mm tt', 'en-us') AS LastBatch
FROM (
SELECT TOP (2) last_batch
FROM [master].sys.sysprocesses
WHERE (loginame = da.USERID)
ORDER BY last_batch DESC
) AS m_2
), 'xxx' ) AS [2nd Last]
, ISNULL(DATEDIFF(mi, (
SELECT MIN(m_1.last_batch) AS LastBatch
FROM (
SELECT TOP (2) last_batch
FROM [master].sys.sysprocesses
WHERE (loginame = da.USERID)
ORDER BY last_batch DESC
) AS m_1
), GETDATE()), - 999 ) AS [Mins. 2nd]
, ISNULL(b.batch_count, 0) AS B
, ISNULL(r.resource_count, 0) AS R
, ISNULL(t.table_locks, 0) AS T
FROM DYNAMICS..ACTIVITY AS da
LEFT OUTER JOIN (
SELECT USERID
FROM DYNAMICS..ACTIVITY
WHERE USERID NOT IN (
SELECT DISTINCT loginame
FROM [master].sys.sysprocesses
)
) AS om
ON da.USERID = om.USERID
LEFT OUTER JOIN DYNAMICS..SY01400 AS um
ON da.USERID = um.USERID
LEFT OUTER JOIN tempdb.dbo.DEX_SESSION AS S
ON da.SQLSESID = S.session_id
LEFT OUTER JOIN [master].sys.sysprocesses AS P
ON S.sqlsvr_spid = P.spid
AND da.USERID = P.loginame
LEFT OUTER JOIN (
SELECT USERID
, COUNT(*) AS batch_count
FROM DYNAMICS..SY00800
GROUP BY USERID
) AS b
ON da.USERID = b.USERID
LEFT OUTER JOIN (
SELECT USERID
, COUNT(*) AS resource_count
FROM DYNAMICS..SY00801
GROUP BY USERID
) AS r
ON da.USERID = r.USERID
LEFT OUTER JOIN (
SELECT session_id
, COUNT(*) AS table_locks
FROM tempdb.dbo.DEX_LOCK
GROUP BY session_id
) AS t
ON da.SQLSESID = t.session_id
ORDER BY [Mins.]
, [Mins. 2nd]
, [User Name];
Regards,
------------------------------
"Sparkly" Steve Erbach - Green Bay, WI
Co-Chair, GPUG WI (Green Bay) Chapter
Blog: https://www.gpug.com/blogs/steve-erbach
Twitter: twitter.com/serbach
───────────────
Excel Webinar List
Original Message:
Sent: 01-15-2019 02:22 PM
From: Michael Barnes
Subject: IDLE Users - Users who appear active
I have found several ways to guesstimate user activity as an expression of idle time.
We are managing to a limited license count and have attached it to a server job that runs every 15 minutes and if at a particular % of capacity, it emails me the results of this script. To date it has worked well to find some obvious offenders who go to lunch and not logout or at the end of the day. Education and puritan shaming is how we manage it now without invoking another add on.
We just discovered that however our results are inaccurate in that we have many uses who APPEAR active.
I have used one script I cobbled together and compared it to others, including the only one I was able to find here on GPUG only to find each provide the SAME results sets.
So the question is -
Are there add ons or ISV solutions that create "ghost activity" or behavior as a side effect that would emulate a user actively working in GP ?
RDC vs Desktop Client Installation ?
Here are two slightly different scripts that yield the same results for me, both show several users active when clearly off the property and their workstations locked..
Here is mine:
SELECT SY01500.INTERID AS SQL_DB, sysProc.spid AS SQL_SPID, SY01500.CMPNYNAM AS Company, Activity.USERID AS Login_ID, Usr.USERNAME AS User_Name,
sysproc.login_time as Login_Timestamp,
DATEDIFF(hour, sysproc.login_time, GETDATE()) as Approx_Hours_LoggedIn,
DATEDIFF(minute, sysproc.last_batch, GETDATE()) as Inactive_Minutes
FROM DYNAMICS..ACTIVITY Activity (NOLOCK)
INNER JOIN DYNAMICS..SY01400 Usr (NOLOCK) ON Activity.USERID = Usr.USERID
INNER JOIN DYNAMICS..SY01500 SY01500 ON Activity.CMPNYNAM = SY01500.CMPNYNAM
LEFT OUTER JOIN tempdb..DEX_SESSION DexSession ON Activity.SQLSESID = DexSession.session_id
LEFT OUTER JOIN master..sysprocesses sysproc ON DexSession.sqlsvr_spid = sysproc.spid AND Activity.USERID = sysproc.loginame
ORDER BY CASE WHEN sysproc.last_batch IS NULL THEN 9999999 ELSE DATEDIFF(minute, sysproc.last_batch, GETDATE()) END , Activity.USERID
Here is another (not mine and avail here on the GPUG site) :
declare @IdleMinutes int
set @IdleMinutes = 0 /*Set the number of minutes. If the idle minutes are greater than this, the users will appear in the results. */
SELECT
A.USERID as USERID,
rtrim(u.USERNAME) as USERNAME,
convert(varchar(10), logindat, 101) as LOGIN_DATE,
ltrim(substring(convert(varchar(20), logintim, 100), 13, len(convert(varchar(20), logintim, 100)))) as LOGIN_TIME,
CASE
WHEN DATEDIFF(mi, P.last_batch, GETDATE()) >= 60 then
cast(( CASE
WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1
THEN DATEDIFF(mi, P.last_batch, GETDATE())
ELSE 0
END ) / 60 as varchar(5)) + ' hours(2), '
+ RIGHT('0' + cast(( CASE
WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1
THEN DATEDIFF(mi, P.last_batch, GETDATE())
ELSE 0
END )%60 as varchar(2)), 2) + ' minute(s)'
WHEN DATEDIFF(mi, P.last_batch, GETDATE()) < 60 then
RIGHT('0' + cast(( CASE
WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1
THEN DATEDIFF(mi, P.last_batch, GETDATE())
ELSE 0
END )%60 as varchar(2)), 2) + ' minute(s)'
else ''
end as IdleTime,
CASE
WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1
THEN DATEDIFF(mi, P.last_batch, GETDATE())
ELSE 0
END AS IDLE_TIME,
rtrim(A.CMPNYNAM) as COMPANY_NAME,
GETDATE() as Run_Date_Time
FROM DYNAMICS..ACTIVITY A
LEFT JOIN DYNAMICS..SY01400 U
ON A.USERID = U.USERID
LEFT JOIN DYNAMICS..SY01500 C
ON A.CMPNYNAM = C.CMPNYNAM
LEFT JOIN tempdb..DEX_SESSION S
ON A.SQLSESID = S.session_id
LEFT JOIN master..sysprocesses P
ON S.sqlsvr_spid = P.spid
AND ecid = 0
LEFT JOIN master..sysdatabases D
ON P.dbid = D.dbid
where
( CASE
WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1
THEN DATEDIFF(mi, P.last_batch, GETDATE())
ELSE 0
END ) >= @IdleMinutes
order by IDLE_TIME desc
------------------------------
Michael Barnes
IT Manager, Enterprise Systems
Kenco Group, Inc
Chattanooga GA
------------------------------