System Administration

GP Power Tools build 22 hotfix available for download (May 02, 2017)

Good day GPUGgers !

As many of you may know (or not), I’m an avid user of GP Power Tools (former Support Debugging Tool, aka SDT) since the tools was launched in 2006, and recently worked very closely with David to bring fixes & enhancements to the product, as I’ve been running Windows 10 since a few months in production on my laptop, where I’ve GP 2013 R2 & GP 2016 R2 (in Test) clients installed.

Windows 10 having changed quite a few things in regards of screen management and multi-monitor setup, plus a few other small hindrance (like the invisible window frames), it has been quite an interesting journey in the past months.

Here are the changes that were brought into this update for the build 22:

Version 16.00 build 22 (Released 02-May-2017) Installer: 16.00.0022.4

  •          Updated Trigger Setup window to stop asking to reset script when changing Menu Trigger text.
  •          Updated Company Color Themes to use User Display Preference settings for Link Fields and Required Fields.
  •          Updated Company Color Themes to override User Display Preference color settings when needed for inverted themes.
  •          Added fix for Dynamics GP Bug where Apply button on User Display Preferences only works one time.
  •          Added additional checks to ensure correct window handle is used when repositioning and retitling windows.
  •          Update default values for Email, Logging and Administrator settings to enable recommended features for new installs.
  •          Adjusted window repositioning code when capturing window handle for 3rd party products to use foreground window method.

One of the important updates for me is about the handling of the windows, as I’m running a setup with 3 displays (Laptop LCD + 2 external 22″ monitors), and I think that nowadays this is a pretty common setup. Since the Windows position memory feature was introduced in build 22 by David, I had a few issues where my other application windows (Outlook, Office, Explorer) would randomly be snapped to the the lower right or left corner of the display it was showing, even it was not related to GP, and this specially annoying when you had a larger posting batch going on your GP client, as you couldn’t work with other windows without having them snapping away under your mouse :-).

Based on the feedback I provided to David, he was able to correct his code and now checks for the proper identification of the window’s title, and if that title does not match the expected value, the code will stop the Automatic Window Position check (this can also happen with 3rd-party ISV products, though part of GP, they are not recognized as such).

Thanks to a feature added a while ago, but not fully used yet,  it provides  an additional DEX.ini settings that allows you to trace in detail what’s going on with your GP forms while your client is active. That helped us to identify the issue caused by non-GP windows.

MBS_Debug_LogWinData=TRUE

Be careful to not leave that option on longer than it would be necessary for you to debug a problem with some forms in GP, because it will fill up the GPPT log quite quickly.

Each GP form/window entry would look something like this (extract from the GPPT log):

2017-05-05 09:44:07 : ** Start of Log **
2017-05-05 09:44:07 : Version: 12.00.0022, Last Modified: 02-May-2017.
2017-05-05 09:44:07 : Automatic Trigger Mode Trigger CPY_DIR_DEPOSIT Unregistered
2017-05-05 09:44:07 : Automatic Trigger Mode Trigger VENDOR_EFT Unregistered
2017-05-05 09:44:07 : ** End of Log  **

2017-05-11 08:02:24 : Automatic Window Position    : window UPR_Employee_MNT of form UPR_Employee_MNT.
2017-05-11 08:02:24 : Dynamics GP Application Pos  : (114,57), Content Offset: (202,167).
2017-05-11 08:02:24 : Dynamics GP Zero Position    : (316,224).
2017-05-11 08:02:24 : Primary Desktop Actual Size  : (1680,1050), not used if per monitor setting available.
2017-05-11 08:02:24 : Primary Desktop Work   Size  : (1680,1010), not used if per monitor setting available.
2017-05-11 08:02:24 : Monitor Desktop Actual Pos   : (0,0) - (1680,1050), Size: (1680,1050), Device: \\.\DISPLAY1.
2017-05-11 08:02:24 : Monitor Desktop Work   Pos   : (0,0) - (1680,1010), Size: (1680,1010).
2017-05-11 08:02:25 : Monitor Desktop Physical Size: (1680,1050), DPI Scaling: 100%.
2017-05-11 08:02:25 : Dexterity reports Window Pos : (9,33) - (656,489), Size: (647,456).
2017-05-11 08:02:25 : Windows reports Window Pos   : (332,257) - (981,771), Size: (649,514).
2017-05-11 08:02:25 : Windows Window Scaled Pos    : (332,257) - (981,771), Size: (649,514), use instead of Dexterity settings.
2017-05-11 08:02:25 : Windows Window Border Offset : (7,3).
2017-05-11 08:02:25 : Adjusted Dynamics GP Zero Pos: (323,224), adjusted horizontal position for border offset.
2017-05-11 08:02:25 : Updated Dexterity window Pos : (9,33) - (658,547), Size: (649,514).
2017-05-11 08:02:25 : Dexterity Window + Offset Pos: (332,257) - (981,771), Size: (649,514).

Quite interesting and helpful when you’re an administrator and trying to figure out what’s going on with your GP client, especially if you run that on a shared environment like Citrix or Terminal Server.

If you’re already a GPPT subscriber, go and download the latest build 22 from the Mekorma site and install it. If you’re not a GPPT user yet, I encourage you strongly to grab a copy and ask for the free 30 Days trial key. You’re going to love it.

Until next time, have a great Day!

@GP_Beat

Advertisements
Categories: Citrix - TS, Dynamics GP, GPPT - GP Power Tools, System Administration | Tags: , , | Leave a comment

Are your currency rates Up-to-date?

Once in a while I get a call or an e-mail from a user that is stuck in GP in the Sales Order entry form or the PO receiving process, because they received an error message on screen telling them there is currently no valid Exchange Rate available for this transaction.

Pop-up from GP when no valid rate is available for a transaction

Pop-up from GP when no valid rate is available for a transaction

As in many companies when you enable Multi-Currency in GP, you’ll end up with a ton of Exchange Rate ID’s, considering you might need one for the daily transactions, the Average for a month, and the month-end rate for some reports. Even worse, if you use Management reporter, you may want to use different rate ID’s then the ones for daily use.

Anyhow, in our system with a dozen GP companies, we deal with as much as 46 Exchange Rate ID’s (and this doesn’t include the Fabrikam Z-xxx rates), ranging from BATH all the way to ZAR. Some of them are more often used pairs than others, so you get the picture: our users from the Finance department that are entering them every day (mostly) are sometimes behind or forget about some of the pairs (especially in month-end when they’re busy).

I had written an Excel report a few years back that would give them a quick overview of the various MC tables involved, notably MC00100 (Exchange Rate table), MC40300 (Rate ID’s) and MC60200 (MC company access). The problem with that report was that the data returned for MC00100 would just keep getting longer and longer as you don’t remove outdated rates.. Sorting the data by the expiration rate date wasn’t a good idea either, because due to some typo’s during entry, some of the expiration dates would be totally off (like in 3016 or 2106), thus not very reliable..

I tweaked the SQL query that I had built to extract the MC00100 data to return the most recent entered Exchange Rate only for each Rate ID… which limited the entries to the actual amount of Rate ID’s (or pairs) we have in the sytem. Much better and easier to quickly spot where there is an expired currency rate that needed a new entry.

Here is the SQL script that you can easily put into a refreshable Excel report (or if you own SmartList builder, create a view and use it with SmartList in GP).

/* This query allows to retrieve all the most current Exchange Rates from the Rate Table MC00100
in the DYNAMICS DB. Given the nature of error prone entries, it can't be based on the Expiration date,
as sometimes totally unreallistic dates show up there (like 3016-12-31, due to a typo).
The query was written in a way that lists in descending order by the latest EXCHDATE
(c) 2017-02-03 B. Bucher
*/
SELECT EXGTBLID
,CURNCYID ,EXCHDATE ,XCHGRATE ,EXPNDATE
FROM DYNAMICS..MC00100 
Inner join (Select EXGTBLID as ExcTabID, max(EXCHDATE) as ExcDate
from MC00100
Group by EXGTBLID) GroupDT
on MC00100.EXGTBLID = GroupDT.ExcTabID
and MC00100.EXCHDATE = GroupDT.ExcDate
ORDER BY GroupDT.ExcDate DESC


PS: I know that some may arg we could use a data feed to update our currency rates, but I’ve looked around and as we draw them from the Bank Of Canada web site, I wasn’t able to find a feed that would work with their data (at least not for free).

Until next post..
@GP_Beat

Categories: Dynamics GP, System Administration | Tags: , | Leave a comment

How to recover your SQL DB from a suspect mode ?

Today we had a very strange situation as I came into to office usually around 7:30am and as there are not much people are around, I have around one hour quite time to work on things that I don’t want too much disturbance..

Around 8:00, a fellow worker from Finance came to my office and asked if I knew why he couldn’t access the ‘Accounting’ folder anymore ?  You have to know that our business is running it’s Intranet over a SharePoint 2013 instance that is configured to run across three servers. As it is very cumbersome for the users, especially accounting people that deal a lot with Excel files, to drag files around on SP, they do map their ‘main’ SP folders thru Windows Explorer with drive letters (i.e. F:\ ).

I started with the usual questions about “have you rebooted your computer ? did you change your password recently ?, and so on”..  As nothing seemed to be wrong, I fired up my IE browser which normally starts straight on our Intranet home page.. and allas, all I got what a typical SharePoint error message (the famous one with the yellow background).

As not much people from IT are in the office around this time, I started to look at the SP front-end server first by opening a remote session into the system. The event log was red of alerts of all sorts, but one that caught my eye was an ‘invalid login’ towards one of the service accounts pointing to the SP database server. In particular was the “SharePoint_Config” database mentioned in the errors, which led me to believe there was  a serious issue going on the SQL server where SP hosts all the DB’s (and there are a ton, believe me… I think at least 20 or so).

As a SQL DB admin who manages quite a few SQL servers in the company, my first thought was to restart the SQL services, as nothing seemed to be unusual (disk space was OK, first thing to check, services were all running… ). At this time I tried to reach out our SP administrator that was en-route, but not aware at all about the whole mess.. In the mean time at least a half-dozen employees had already opened Help Desk tickets about the non-working intranet. He said that he wasn’t far from the office and said that I should just try to reboot all the servers, that would usually help to bring things back to normal 🙂

I didn’t had a chance to reboot all the servers, but one thing that caught my attention was the SharePoint_Config database that had a strange icon that was not looking normal.  As my SP Admin had in the meantime reached his desk and called me, we started to look into the SQL DB status. We quickly found a blog post related to the exact same issue and started working on it.

http://blog.cloudshare.com/blog/2013/01/29/how-to-recover-sharepoint-2013-databases-from-suspect-mode

The SharePoint_Config database is a vital component of a SharePoint server farm, as it contains all the configuration for the various sites composing the farm. In our case the database ran into an issue over the week-end and was put in ‘suspect‘ mode by the SQL server, cutting simply all access to the DB from any application.. thus the SP website could not run anymore.

With the help of the blog post we were able to quickly fix the corruption (fortunately for us) and within an hour or so had the server back on operation. At last resort, we would have had to reach out to the server backups to restore the faulty database, which would have taken probably much more time, as the whole backup is very huge.

Lesson to Learn from this event :

  1. the same could have happened to any Dynamics GP or CRM databases, not something specific to SharePoint
  2. if you manage your own backup jobs on the server for SQL DB’s, make sure you get an alert if anything unusual happens during the backup. In this case, the faulty DB could not be backed up, but the CommVault application that we use wasn’t triggering any alert about that …
  3. I’ve programmed my own sets of backup reports for all the SQL servers I’m responsible for (which was not the case for SharePoint), so I get in my inbox every day an overview of what was fully backed up
  4. if anything happens during my SQL maintenance jobs, I immediately get a notification on my cell/inbox.
  5. if you don’t manage yourself your DB backups (i.e. IT takes care of it), make sure you have some tools (like Idera, Apex or Red-Gate) to monitor the status of your SQL servers and DB’s.. Such situation should not have left a server stranded for several hours and could have avoided a stressful Monday morning in the office.

I hope this provides some food for thought about how you manage your daily SQL servers.
Until next post…

@GP_Beat

Disclaimer : I have no relations or interests in any ways of the above mentioned tools, but I’ve used some of them that are free for quite a few years.

 

 

Categories: Dynamics GP, SharePoint, System Administration | Tags: , , | Leave a comment

The All-New-GPPT finally released… in BETA.

It was worth the waiting … on July 7th, David Musgrave finally announced the long waited successor of the Support Debugging Tool (aka #SDT), under the new name GP PowerTools (#GPPT).

Soon as I got the link to the download area, I went to pick up the firt built of the new package and the next day installed it.. and there is when I ran into troubles.. I had previously applied on my GP2015 test bed system upgrades to go from GP2010R2 to GP2013R2, and 2015 RTM, so I was coming from a previous release of SDT that was running fine under GP2013R2, and still had all the references in 2015.. Since I couldn’t survive without the SDT very long, Dave was kind enough to provide me a pre-build for 2015 of the actual SDT, so I could test properly the new GP.

That being said, the new setup of the GPPT comes with a few tricks if you already have an old SDT installed… and you need to be careful the way you apply the update.  I don’t wanna repeat what David already wrote, so for more details about what to care for to install the new #GPPT, go and have a look at today’s blog post about the public BETA setup.

The current BETA release has a time-bomb built-in that will expire the product on August 15th, 2015. By that time David will either have released another BETA with an extended expiration date, or feel confident enough that #GPPT is ready for prime-time release on the commercial level. Yes.. the new #GPPT will no longer be available for free since David doesn’t work anymore for Microsoft and has to work hard to get the bread on the table too :-).. But  the inital launch price should be very affordable at 365$/yr and everyone should get a license to support that super tool that took years of development to reach this level of quality (that’s less than a coffee cup a day !). Just the current BETA release required several hundred hours of work to add in new features and functionality that will make you fall in love again with GP’s administration (in case you just divorced from it 🙂 )..

Here are a few ones just to show you.

  • The Company selection screen. David included some of it’s VBA code that he wrote in the past to allow to enlarge the company selection list from the login screen, because sometimes it was very hard to figure out which company to select when they all started with the same or similar names.. To enable this feature you have to navigate into the GPPT Administrator Settings > Company tab, and in the middle of the screen, check the box called ‘Add extra width to company name drop down list…’
GP widen Login screen

GP widen Login screen

  • Another one I love is the all new Security Log enabler.. in the old #SDT when a user had security issues, you would walk up (or connect) to the end-user and start the security profiler to track what’s causing the issue, then export the log to XML by file or e-mail, re-import it with your POWERUSER role user and load the profiler trace to figure out what’s not working.  The new Security Log allows you to enable all this directly from your workstation on the product level itself. To enable this, you navigate again to the Administrator Settings > General tab, and at the bottom check the box “Enable Security Activity Tracking”.

    Centralized Security tracking log

    Centralized Security tracking log

  • Finally another nugget is the fact that you can now setup a different password for the GPPT administrator and for the System protected area password. This way you can delegate or share some of the powerful functions of GPPT without revealing the system password.

    Image 2015-07-08_134943

    Dedicated GPPT Admin password

Lots of new functions to still discover, so stay tuned as I’m going to report more about my findings on the new #GPPT.

Until next post, have a great time.

@GP_Beat

Categories: Dynamics GP, GPPT - GP Power Tools, SDT - Support Debugging Tool, System Administration | Leave a comment

Using Dynamics GP in a Citrix / TS environment

I often come across discussions in forums and community posts about the problems related to the end-user parameters stored in the DEX.ini file from GP.

Now the problem with shared environments is the reality that all the users are using the program folder to launch the application, thus also the same same settings from the DYNAMICS.SET and the DEX.ini to save the parameters. This can lead to some very confusion situations…. I had users calling in and saying they started a new GP session but only get the icon in the task bar, but actually never get to the login screen.

It took me some investigation back in 2010 or 2011 when this happened the first time, and it was coincidentally at the time when some users started to request a second monitor to increase their productivity :-)…

As it turned out at that time, Dynamics GP was used on the Citrix XenApp server by the vast majority of our users (around 120) and there was a single DEX.ini file for all the users, which means that every time a user launched the application remotely, it was showing the last user ID that logged in previously and also put the main GP windows at the screen coordinates where it was closed the last time. This was not too bad as long as everyone had pretty much the same configuration for the hardware (screen and printer), but soon as people started using 2 or more monitors, and moved the main GP window off the monitor #1, then the next user would run into troubles if he/she didn’t had the same setup.

That’s when I came across a blog post from my good friend David Musgrave  (at that time) from Microsoft Australia, who wrote something about the side-effects of shared DEX.ini files and how to avoid collisions in shared environments.

His article inspired me to find a smart way to make sure when a user would start GP the first time from the Citrix server that he would get a clean pristine DEX.ini file that become he/her own and contains only the settings from their sessions. No more hidden windows and lengthy DEX.ini files that would grow out by endless printer queue entries.

So I created a small batch file that would check the presence of an existing DEX.ini file in the owner’s home profile under the system hidden AppData folder. The content of the batch file looks like this :

 @echo off
rem This file is used to launch Dynamics GP
rem from within CITRIX MF without parameters
rem echo Press any key to start the GP apps
rem pause
rem Modified version to TEST SP3 1:07 PM 7/13/2012
c:
cd “C:\Program Files (x86)\Microsoft Dynamics\GP11SP3”
if exist %HOMEDRIVE%%HOMEPATH%\AppData\Local\Dex.ini Goto StartGP
copy C:\Users\Default\AppData\Local\Dex.ini %HOMEDRIVE%%HOMEPATH%\AppData\Local\Dex.ini
:StartGP
start Dynamics.exe Dynamics.set %HOMEDRIVE%%HOMEPATH%\AppData\Local\Dex.ini
cd \

Save the file as a .cmd in the Program Folder location of your GP application on the Citrix server. Make sure you have fresh and pristing DEX.ini file copy in the Default user home path (see location above).  You can take the current DEX.ini file from your GP Data folder and remove any references to the ‘remembered’ printers and clear out the last user in the file :

SQLLastUser=
RememberUser=FALSE

It’s important to set the 2nd value to FALSE, otherwise GP will try to auto-login the user and this would fail since there is no user-id.

Lets assume you named your file GP_Roaming.cmd, then your new launch file command line would look like this :

“C:\Program Files (x86)\Microsoft Dynamics\GP\GP_roaming.cmd”

There is no need anymore for a parameter provided in the published application for Citrix, since everything is already passed in the .cmd file itself.

One last tip when you apply a new service pack or a year-end update to your GP. When updating your client on the Citrix or TS server, only the DEX.ini that’s sitting in the GP Data folder gets updated… none of the distributed config files from the users home profile…Remember to delete them all before given the updated GP apps free again for the users (the nice thing with published apps is that you can quickly enable/disable them).

If you don’t want to delete the files, use a VBS script or Powershell to loop through all the DEX.ini’s and make the changes.. I wouldn’t necessarily recommend this way of doing it, since during a service pack upgrade, there are quite a few changes applied to the file.

Test the new launch file with a few users and do adjustments if required. This procedure has worked pretty well for our company since about 3-4 years now and has successfully been ported to a newly built Windows 2012 Remote Desktop Application Server.

Until next post…
@GP_Beat

Update on 2015-08-21:

There is relief to all the users that have setup GP in a Citrix or TS environment. Since GP2013 was released, you don’t need anymore to have a separate DEX.ini for each user. Read on the Blog post from my friend Mariano Gomez, Dynamics GP MVP : http://dynamicsgpblogster.blogspot.ca/2014/05/working-with-dexini-settings-in.html

To me when upgrading to GP 2015 it was a real relief to not have to deal anymore with outdated DEX.ini settings every time you do apply an upgrade / service pack to your share GP client (as only the main DEX.ini got updated by the GP Utilities process). Running the GP client with wrong release information from the DEX.ini file can be very stressful as you get all kinds of strange messages and don’t necessarily think of the user’s DEX.ini (though I haven’t tested yet the user’s DEX.ini scenario after an update).

Until next post…
@GP_Beat

Update on 2016-01-25:

After we upgraded last year the GP2010R2 to GP2013R2 (not going to 2015 was a decision based on the fact that some important features from BP were missing on the Web Client PTE), I realized that the user shared DEX.ini feature was finally not so good and in some cases not useful at all. For that reason I went back to the individual DEX.ini file for each users on the TS. This way I have a much better control over the settings. I’ll post in a future blog entry, some tips on how to quickly get rid of all the DEX.ini files after an upgrade, rather then having to painfully delete them one-by-one.
Until next post…
@GP_Beat

Categories: Citrix - TS, Dynamics GP, SDT - Support Debugging Tool, System Administration | Tags: , , | 4 Comments

Use the Support Debugging Tool to tackle GP security issues

Last week our HR department people had training about the HR module in GP… it was a very formal training of two hours from an external consultant, and as such it wasn’t going very deeply into the subject. But enough to say that he showed our users about some of the hidden gems in the HR module that they weren’t even aware of: the Letter Writing assistant function.

Since I had given them access to the Fabrikam Company in GP for evident privacy purposes, I had also created some training users for the sake of this exercise, not wanting them use their regular GP users.

Having assigned those users regular GP Security role for the HR module (this would likely be HR_MANAGER & HR_GENERALIST), they were supposed to get covered for almost everything… Think so? Not really… In their attempt to create default Security Roles in GP to cover most of the functionality in GP, Microsoft sometimes didn’t checked if all the roles where properly assigned to corresponding tasks, and that those security tasks were also related to actual existing resources in the system…

We all know that by default GP has numerous Security Roles (> 50), Security Tasks (> 450) and Resources (> 9000), resulting in thousands of security combinations in the system, that it is very difficult to test all combination and make sure nothing was missed.

Now what to do when you hit the infamous error message in GP about denied access to resources?

Dynamics GP Security Denial Message

Dynamics GP Security Denial Message

Call your system Administrator of course :-)… but what if you are the system admin? The answer will be to use the Support Debugging Tool (SDT in short). This tool is free for every Dynamics GP customer and you’ll find more details about it on the web (http://aka.ms/sdt). If you don’t have it installed now, you’ll have to contact your Microsoft partner and ask for the latest release for your GP version.

The tool was written and developed by my good friend David Musgrave that works for Microsoft Australia. I’m not going to enter into the history behind the SDT; there is plenty of links on the site.

Where to Start with the SDT?

Once you have the SDT installed into your Dynamics GP client, you’ll get and additional menu entry under your main GP menu >> Tools >> Support Debugging Tools (CTRL-D). From the SDT main menu you can access the user manual by hitting F1 and open the PDF document (assuming the installation was done properly and the PDF was copied into the program folder).

I encourage you to read thru the guide, it is very well written and explains all the aspects of the tool. Don’t be afraid by the length of the document (about 200 pages), the most important information is covered in Chap 2 & 3 in about 40-50 pages. We’re going to focus today on the Security Profiler (Chap. 2, pp 52-59), and see how this tool can help you to identify in about 5 min. what is causing your denial of access in GP.

Warning: the content and usage of the SDT can be dangerous if used inappropriately in Dynamics GP. I always suggest to test this out in a TEST environment or TEST company before your actually apply this to a production system. Over the course of the time, when you become more confident with the tool, you’re going to love it and install it across all your GP clients. This will make your administrator life much easier. Also, most sensitive functions of the SDT are protected by the system password, and assuming that you have set one, it will ask you every time for it before letting you access a critical function.

10 simple steps

That said, let’s start with the Security Profiler and see how we can identify in 10 steps the security issue.

  1. Start the Security profiler from the SDT ‘Debugger’ menu:  Debugger >> Security Profiler
    This will open a new form that you can minimize to the bottom of your screen. I suggest creating a shortcut to the security profiler; it’s a tool you’re going to use often.
  2. Go to the resource that you want to access with the user that is denied of it. In our example we want to access the Applicant form in HR and want to launch the “Write Letters” assistant, in order to use the ‘Prepare an Applicant Letter’ wizard. Click on the menu until you hit the security error message.
  3. Restore the Security Profiler window and locate the last line…

    Security Profiler window (regular user)

    Security Profiler window (regular user)

  4. Use the Export menu option to save the result of the tracing to an XML file (best place is the shared Data folder on the server, thus you can open that XML file from another GP client with a Power User.
  5. Either login in the same session with a different user that has access to GP Security and preferably a system admin, or use another client from a different system and has access to the saved XML file.
  6. Import the XML file previously saved into the Security Profiler and select the line that has the red denial sign in front of the line. With higher privileges, you now get another option to check the security settings.

    Security Profiler window (Power User or SA)

    Security Profiler window (Power User or SA)

  7. When you click on the Security button, the SDT will open a window and show you a tree-like view of the resource that was denied.

    SDT Security Tree View

    SDT Security Tree View

  8. Expand the left side tree labeled “Access Granted” and drill down to the lowest level to get a list of the Security Tasks and Roles assigned to the required resource.
  9. On the right side you see a list of the users with a small box checked (which means access is granted to all companies, grey would mean only partial company access and blank no access at all).
  10. Double click on the user name to open the User Security Setup form of GP. Scroll down until you get to the Security Role that was displayed in the Security Information pane on the left side and check the box on that line. Save the changes for that user and ask him/her to log back into GP and try to access the denied resource.

Et Voilà! You just solved your first security mystery in Dynamics GP.

This is only one facet of the multiple functions that the Support Debugging tool can offer. Go thru the user guide and familiarize yourself with the other functions.

Good luck with your daily Dynamics GP administration.

Categories: Dynamics GP, SDT - Support Debugging Tool, System Administration | Tags: , , | Leave a comment

Tracking User activity in Dynamics GP

What is it all about?

There were lots of inquiries recently following a forum question about how to deal with the user’s ACTIVITY table in Dynamics GP. Some company are adding user licenses when they feel being short or having too much users complaining about the inability to login into GP, because all the licenses are used up in the system.

Now there is always a question that comes up in this situation: how many of my GP licenses are really in use and how much are currently stranded users that have been disconnected by the system or closed on the ‘hard’ way (i.e. killed their GP session) the client because it was not responding or they were stuck in a loop not being able to logoff properly.

The Way Dynamics GP tracks the user licenses is through the table DYNAMICS..ACTIVITY in the system database of the SQL server. From the registration key, Dynamics know how much user licenses are granted to this server and thus tracks the count in that table. Now when user proceed with normal login / logout, the table gets updated every time and puts a time stamp in the fields  LOGINDAT & LOGINTIM in the form of a long Date string. This is a way to know since how long a user has been logged in GP, but doesn’t tell you if the user is actually working or not in GP actively. This can only be achieved by monitoring closely the processes running inside of GP itself. Some 3rd party tools like the Rockton GP Toolbox and Willoware GP Powerpack offer that kind of functions.

Dynamics GP offers a way to track the login/logout activity in the system itself, but that doesn’t help more if looking for ‘inactive’ users or stranded logins. This would only serve as a kind of statistics log. To enable the user login/logout tracking, go to “Microsoft Dynamics GP menu >> Tools >> Utilities >> System >> Activity Detail” and select the appropriate range of users or company to enable / disable the login/logout activity.  The table that is tracking the activity is SY05000 from the DYNAMICS system DB.

Now what to do? How to get rid of stranded users?

A simple way to get rid of stranded user’s login in the ACTIVITY table is to delete the entries from that table, after you make sure the user is really no longer working in GP.

In a usual  9-to-5 office environment, this is pretty easy to achieve, because most of the GP activity will take place during those office hours, thus making it easy to determine who is working or not and I doubt that you’ll have a lots of users working off-hours let’s say around 11PM or even 1AM in the morning.

But you don’t want to setup your clock and get up in the midst of the night to flush your ACTIVITY table, so your SQL Agent is your best friend in this sense.  All you have to do is create a scheduled job that would run a script and clear out the table.

What I do is filter the DELETE action and leave out user logins that I know might be used (i.e. the ‘sa’ user account or my own one, because I might doing some nightly maintenance on GP).  The script for the job would look like this:

Delete from ACTIVITY
       where
LOGINDAT < GETDATE()-1 and  –> optional, cleans out only logins older then 24hrs
       (USERID <> sa or USERID <> xxxxx)

Your Step 1 for the job would look like this below. Schedule it to be run every night before you do your SQL backups routine (or later if this happens to be earlier in the evening). Mine runs at midnight. Make sure you make it run under the DYNAMICS Database.

Setup a CleanUp Job in SQL Management Studio

Setup a CleanUp Job in SQL Management Studio

To complete this, I use also a stored procedure that I run on schedule one or two hours before the cleanup task and that sends me the list of the stranded users in the ACTIVITY table. The code for this makes usage of a send-mail feature in SQL that needs to be enabled in order to work (see comment from next chapter).

The code goes like follow:

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[usp_GPLoggedUsers_overtimed]    Script Date: 2013-04-04 08:01:49 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

–=============================================
— Author:           < Beat BUCHER >
— Create date: 2009-09-08 / 10:22 /
— Description:      The script validates which user account is logged in Dynamics GP
—                   since more then 750 min (12,5 hrs) and sent the report by e-mail
–=============================================

CREATE PROCEDURE [dbo].[usp_GPLoggedUsers_overtimed]

AS
BEGIN

       — SET NOCOUNT ON added to prevent extra result sets from

       — interfering with SELECT statements.

       SET NOCOUNT ON;
IF EXISTS
(
select datediff(mi,logindat+logintim, getdate()) as DURATION, –convert(datetime, convert(varchar(15), GetDate(), 114), 114) – LOGINTIM as DURATION,
USERID,
              CMPNYNAM,
LOGINDAT,
LOGINTIM

              from DYNAMICS.dbo.ACTIVITY
where datediff(mi,logindat+logintim, getdate()) > 750
)

              BEGIN
DECLARE @SQL varchar(8000)

              SET @SQL = ‘select datediff(mi,logindat+logintim, getdate()) as DURATION, USERID,
CMPNYNAM,
LOGINDAT,
LOGINTIM
from DYNAMICS.dbo.ACTIVITY where datediff(mi,logindat+logintim,getdate()) > 750′

               –print @SQL

               EXEC msdb.dbo.sp_send_dbmail@recipients = ‘xxxx@domain.com’,
@subject = ‘Users Logged in beyond limit’,
@body = ‘Attached is a list of users that have been logged in beyond the limit’,                 @query = @SQL,
@attach_query_result_as_file = ‘TRUE’,
@query_result_width = 250

      END

END
GO

Change the @recipients variable content to fit your needs accordingly.

I’m constantly hitting the max user license limit. Is there a way to monitor this?

Yes there is. This actually implies some setup on the SQL server side, since by default the function to send out e-mails is disabled from SQL 2005 and up (SQL 2000 still had it enabled by default, but not much systems are running on this old SQL version today). This goes behind the topic of this article, but you have to make sure with your DBA or SQL admin that the sp_sendmail stored procedure is enabled and proper notification and SQL-mail is configured in the server. There are plenty of SQL forums and sites that discuss this in detail.

Since this blog environment doesn’t allow uploading attachments, I have to explode the entire code here in the text.

declare @limit int, @current int
declare
@msg varchar(80)
declare
@threshold decimal(5,2)

set @limit = 16  /* current Great Plains limit – unable to extract from reg keys of table */

set @threshold = 0.85

select @current  = count(*) from ACTIVITY

print ‘Current = ‘ + str( @current)

if @current
>= @limit begin
set @msg = ‘Great Plains user limit of ‘ + str( @limit) + ‘ has been hit.’

       exec master.dbo.sp_sendmail
@recipients
= ‘xxxxx@domain.com;’
, @subject = ‘Great Plains User limit hit’

 — @message = @msg

end
else

if @current >=( @limit * @threshold  ) begin

       set @msg = ‘Great Plains user connections (‘ + str( @current ) + ‘) exceeds ‘ + ltrim(str( @threshold * 100)) + ‘ % limit of the available ‘ + str( @limit) + ‘.’

        exec master.dbo.sp_sendmail


@recipients = ‘xxxx@domain.com;’
, @subject = ‘Great Plains User count exceeds threshold’
, @body = @msg

end

 

The best way to make this work is to put this in a SQL Job that will be scheduled to run every 5 minutes or so (adjust to your needs) during the business hours. In my setup below, there is another step to collect stats information about the login activity in GP, which allows me to draw some nice charts in Excel. This too goes behind the topic of this article, but I can cover that in a later blog post if there is some interest for it. Just let me know by leaving a comment at the bottom.

GP_UserCountLimit

I hope this will help some users to get better in control with their Dynamics GP user licenses. As mentioned at the beginning, 3rd party tools have way more to offer and can be very handy to handle such situation, but they come with a price tag $$$$ attached, whereas those proposed solutions are free to implement.

I had worked for a company that didn’t wanted to understand the benefit of buying a product like Rockton’s Toolbox to kick out inactive users from GP and rather spend a lot of their precious dollars by adding new GP user licenses… When I left the company, the user license count was up to 48 users and we didn’t had much more active GP users then my current  company, and we run with only 16 concurrent licenses, barely hitting the maximum (it happened maybe 3-4 times since I joined the company).

My total user count in GP is 137 users from which about 40 are login into GP on a regular basis (more than a dozen times within the last 6 months), and about 20 heavy users, almost every day in GP. I never run out of licenses with the help of the Rockton GP Toolbox. I mention this product because it is the one our company bought several years ago when it was still called OmniTools, but other products in the market is doing a similar job.

From what I have discussed with Microsoft during my last Convergence participation, we may soon see a built-in function in GP to manage that kind of issues (i.e. inactivity timeout and maintenance lockout from the system in order to carry on upgrades), so cross your fingers.

Enjoy and start to work on a better control of your GP environment.

Categories: Dynamics GP, System Administration | Tags: , , , | 4 Comments

Powered by WordPress.com.