We have users who don't properly exit or log off GP 2013 before they leave for the day.
Is there a script that can detect such users and send a report??
I would really prefer a PowerShell script but a SQL script would work too.
Thank you, Tom
*This post is locked for comments
Tom,
This is doable using a SQL Agent Job, some Powershell, and a Windows Scheduled Task.
Step one is configuring the SQL Agent to run the job on the schedule you have decided upon. Set the job to write the query output to file.
Step two is the Powershell. I borrowed this bit from StackOverflow and it looks like it should work to me. (Source stackoverflow.com/.../powershell-using-get-content-as-body-of-an-smtp-mail)
send-mailmessage -from "User01 <user01@example.com>" -to "User02 <user02@example.com>", "User03 <user03@example.com>" -subject "Sending the Attachment" -body (Get-Content C:\somefile.txt) -dno onSuccess, onFailure -smtpServer smtp.fabrikam.com -encoding (Get-FileEncoding C:\somefile.txt)
Step three is to use the Windows task scheduler to run the Powershell script after the SQL agent job completes.
Kirk
There are utilities out there that will provide you better service than to know if someone has logged off. You should consider looking for more user management functionality.
I believe you might want to start with PSTL tool - there is a user manger in there I believe that might start to assist.
If I am wrong, sorry, wrote this in a rush out the door.
Thank you, I will try to learn more about this etc.
Thank you, Tom
Well if you already which user has logged off through RDS then the activity table is all you'll need to know, while you're at it you might as well clear out the sy00800, and tempdb..dex_session and the tempdb..dex_lock to make sure there are no locked tables and objects from orphaned users.
Hi Tom,
Thank you for this idea, but I want to query dynamics.activity *after( all the terminal services/RDS users are logged out for the day and only see which GP users are still 'remaining.' I basically know all the users are logging off the RDSH servers but they are not necessarily properly exiting/logging off GP.
Thank you, Tom
Here's another food for thought, if you use a terminal server you can check to see which users are not logged into the server and which ones are in the dynamics activity log. If they show up in dynamics.dbo.activity but not in the server user log in task manager then that's a sure fire way to tell.
You could write a powershell script to export the system user activity log to a file and then import that into a temp table in GP where you can then compare that to the activity table. Probably want to write a stored procedure that executes the powershell export and import so you can get a real time comparison.
This might be overkill but it should do the trick. :)
How could/should the script be edited to NOT include the idle time query and column??
I would do it but I don't know GP/SQL to know when/if I would be making a big mistake(s).
We could do without that part to have the other parts work properly in GP 2013 SP2.
Perhaps post a revised script at your website without the idle time query and column??
Thank you, Tom
Hello Tom
That comment is just right, a new functionality was introduced with R2 which was thoroughly explained by my friend Tim Wappat on a previous post Check for user messages - GP Process Monitor
I am quoting Tim from a previous thread:
"Be aware that every min GP will be performing a query on an inbuilt timer, so the session that is on will never get truly idle.
Due to this event occurring I would expect a maximum idle time of 1 min from any examination of SQL session, on the newer versions of GP (2013 onward).
"
In this essence, I am afraid that such piece of information could hardly be provided by my script as thoroughly explained by Tim.
For the present time, I still have no other work around solution or an alternative way to detect the idle time, but there must be a way out of this. I will make sure to post any updates accordingly.
Thank you for offering this script.
Have you revised the script to work properly with GP 2013 SP2??
Please see the first comment, dated 1/2/15.
How would one email the data??
Thank you, Tom
I have developed a script for User Activity Report - Idle and Login Details SQL Script , it retrieves not only user records from Dynamics GP but associated SQL session details and idle time as well.
Your feedback is highly appreciated,
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
