September 15, 2016 at 5:49 am
We have an audit running but it does not capture the workstation from which
the connection was made to the database. How do we capture this information?
https://technet.microsoft.com/en-us/library/cc280765(v=sql.105).aspx
September 15, 2016 at 6:53 am
Give this query a whirl and see if it is what you are looking for - -- Get a count of SQL connections by IP address
SELECT dec.client_net_address ,
des.program_name ,
des.host_name ,
--des.login_name ,
COUNT(dec.session_id) AS connection_count
FROM sys.dm_exec_sessions AS des
INNER JOIN sys.dm_exec_connections AS dec
ON des.session_id = dec.session_id
-- WHERE LEFT(des.host_name, 2) = 'WK'
GROUP BY dec.client_net_address ,
des.program_name ,
des.host_name
-- des.login_name
-- HAVING COUNT(dec.session_id) > 1
ORDER BY des.program_name,
dec.client_net_address ;
September 20, 2016 at 5:49 am
Nice query but not exactly what I was hoping to find. I need to add the workstation name to our audit table.
Funny, this works perfect in SQL trace but not in the built-in auditing.
September 21, 2016 at 4:16 pm
I don't believe you can get this (or app name) in an Audit, which is sad. I think this is a hole.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply