Disclaimer: While writing this post I finally scrolled down a little further and read the ‘Testing your results’ portion of Chrissy LeMaire’s post, which uses that same ClientName parameter, making most of this post redundant. That would have saved me some time if I hadn’t skimmed past it for over 2.5 years! Discovering functionality within the dbatools module from the console is very rewarding, though.
I'm trying to decommission a SQL Server so I'm monitoring logins to that SQL instance, to ensure it isn’t being used. However, the customized version of the script to monitor logins using Chrissy’s backwards-compatible T-SQL and the dbatools module excludes connections from certain apps (our performance monitoring software and dbatools connections, and certain domain credentials) from being logged, which made it problematic to test the monitoring to see if the connections were being written to the watchlogins table.
Using the Connect-DbaInstance cmdlet I can impersonate another account _and_ impersonate an application name, verifying that the monitoring script is inserting the desired connections into the watchlogins table. The SqlCredential parameter allows Windows logons or SQL logins to be used. The example below uses a domain logon stored in the $cred variable as a PSCredential object. The ClientName parameter is where the “fake” client application name is specified. Using a WAITFOR DELAY statement ensured the connection was active long enough for the polling from the collection script to catch it.
In the screenshot below the client name is displayed in the program_name column of Adam Machanic’s sp_WhoIsActive results in SQL Server Management Studio.
See Get-Help Connect-DbaInstance -Full or https://dbatools.io/Connect-DbaInstance for more info/examples.
I’ve modified Chrissy’s script slightly to avoid running the query against instances that may be down and time out, as well as adding some output that can be seen when looking at job history when scheduling the PowerShell job using SQL Agent.
