Watch-DbaDbLogin - Unclear about output columns DatabaseId and Database #8199
DougStevensGCPS
started this conversation in
Contributors
Replies: 1 comment
-
FYI - No longer a request. Looking into writing a small script using Get-DbaProcess and filtering out where IsSytem -eq $true. Will test against our servers (about 155 servers), but only concerned about the 47 (give or take) running SQL Server 2014. |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Not sure if this is an undocumented feature; or (more likely) I do not understand the output or purpose of the function.
My understanding of this function:
It captures login information across one or more SQL instances (source can be pipeline, CMS, or input file) and stores the output into specified SQL instance, database and table. This can be scheduled periodically (e.g. every 5 minutes) for later analysis. Primarily to identify the logins connecting to database(s). (Which is a good thing when planning migration.)
To become familiar with the command, ran the following:
Watch-DbaDbLogin -Database DBAdmin -SqlInstance DBAServer01 -ServersFromFile C:\Users<username>\Desktop\ServerList.txt
The output looks pretty good and it was compared to Activity Monitor.
However, for several records in the output the DatabaseId and Database have values zero (0) and NULL (respectively). Wherease, Activity Monitory shows the database name.
Looking at the code in the function (around lines 163 through 176):
$sql = " SELECT s.login_time AS [LoginTime] , s.login_name AS [Login] , ISNULL(s.host_name,N'') AS [Host] , ISNULL(s.program_name,N'') AS [Program] , ISNULL(r.database_id,N'') AS [DatabaseId] , ISNULL(DB_NAME(r.database_id),N'') AS [Database] , CAST(~s.is_user_process AS bit) AS [IsSystem] , CaptureTime = (SELECT GETDATE()) FROM sys.dm_exec_sessions AS s LEFT OUTER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id" Write-Message -Level Debug -Message $sql
Looks like the database information is coming from the sys.dm_exec_requests DMV.
Wonder if should be coming from the sys.dm_exec_sessions DMV?
(Yep, I'm a noob. Not sure if this should have been submitted as an "issue." Or, submitted as a feature request. (Example; requesting a parameter for DbDmvSource with valid set of "Sessions" or "Requests." Which could translate a variable, $DbSrc to be the alias names of the DMV tables, "r" or "s"))
Thank you kindly,
Doug
Beta Was this translation helpful? Give feedback.
All reactions