Need to keep track of RDS users

I need a way top keep track of Remote Desktop login/logout and disconnected sessions by userid.  I'm not familiar with PowerShell yet but I've seen some scripts that do that by exporting the  TerminalServices-LocalSessionManager events.  I'm struck though.  I would like to put into a SQL DB so we can analyze who is using the RDS.  We have 20+ RDS users.
LVL 14
Bill RossProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
0
Bill RossProgrammerAuthor Commented:
Hi Icohan,

I saw that but not being familiar with PS I could not get it to work.  Please help by briefly explaining the script.

.\RDPConnectionParser.ps1 -ServersToQuery Server1, Server2 -StartTime "November 1"

When I run it I get the message below so I must not understand PS enough.

.\RDPConnectionParser.ps1 : The term '.\RDPConnectionParser.ps1' is not recognized as the name of a cmdlet, function,
script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is
correct and try again.
At line:1 char:1

Thanks for your help.

Bill
0
Bill RossProgrammerAuthor Commented:
Also - Is server 1 the gateway or the RDP VM?
0
Webinar: Miercom Evaluates Wi-Fi Security

It's not just about Wi-Fi connectivity anymore. A wireless security breach can cost your business large amounts of time, trouble, and expense. Plus, hear first-hand from Miercom how WatchGuard's Wi-Fi security stacks up against the competition in our upcoming webinar!

lcohanDatabase AnalystCommented:
So you download and copy RDPConnectionParser.ps1 ON the Server where you want to get the report from;
Start Powershell on that server and run the command below where of course you will need to updated the folder name where you put it, the  server name and adjust the start date as needed:
      C:\WhateverFolder\RDPConnectionParser.ps1 -ServersToQuery YourServerNameHere -StartTime "August 1, 2018"
This will create a CSV output on the desktop you run it from.

You could aggregate all you want to do in a SSIS package to perform these steps using a parameter for the date and pull the CSV file into a SQL table.
0
Bill RossProgrammerAuthor Commented:
Great help!  I got it to run.  Please explain the SSIS package concept as this would be perfect.  I can open another question if you want.

Thanks,

Bill
0
lcohanDatabase AnalystCommented:
Here's the official Microsoft document https://docs.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-2014  however there are many links that can show you how to do it like https://www.youtube.com/watch?v=lkuvvaOntb4 
It may look complicated but you will have only few steps in the package like read input variable - start date - exec that powershell script that will generate the CSV file then import it into a SQL table like described here: http://www.kodyaz.com/articles/import-csv-flat-file-into-sql-server-using-ssis-integration-services.aspx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill RossProgrammerAuthor Commented:
Hi Icohan,

Perfect!  Do you know how to execute a PS script from TSQL?

All is working great!

Thanks,

Bill
0
lcohanDatabase AnalystCommented:
Here's a link to show you how to include powershell script in SSIS and how to grant the access needed for the task to not fail: https://blogs.msdn.microsoft.com/ssis/2017/01/26/run-powershell-scripts-in-ssis/
0
Bill RossProgrammerAuthor Commented:
All set.  Thanks for the help!!!
0
Bill RossProgrammerAuthor Commented:
Fast help!  Thanks - saved my butt.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.