This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

How to find machine name lookup? DPA 9 , SQL Server

FormerMember
FormerMember

We have a sql server instance that DPA is reporting several old machines connecting to it. When I look at the hosts in SQL Server's Activity Monitor, I don't see them. Where is DPA getting the connection information?

Thanks for any help!

  • We run a quickpoll once every second to extract information from SQL Server (see query below).  We view this data from the lens of SQL Server, so my question is why would SQL Server be telling us this (s.hostname)?  We had another case where a query was being executed from a spreadsheet where the hostname was hardcoded in, so it appeared as if it was coming from a discontinued app server...  Could something similar be happening in your environment?

    Feel free to run this query outside of DPA to see the raw data SQL Server is returning for us.

    SELECT s.loginame, db_name(s.dbid) name, s.hostname,  

           s.program_name, s.sql_handle,  

           s.stmt_start, s.stmt_end, s.spid,  

           CONVERT(smallint, s.waittype) waittype, s.lastwaittype, 

           s.ecid, s.waittime, CONVERT(varchar(64), s.context_info) context_info, 

           RTRIM(r.wait_resource) waitresource, s.blocked ,  

           r.plan_handle, r.statement_start_offset, r.statement_end_offset, r.start_time,  

           r.query_plan_hash

    FROM master..sysprocesses AS s WITH(NOLOCK)  

    LEFT OUTER JOIN sys.dm_exec_requests r WITH(NOLOCK) ON r.session_id = s.spid 

    WHERE (s.dbid<>0  

           AND s.cmd<>'AWAITING COMMAND'  

           AND s.cmd NOT LIKE '%BACKUP%'  

           AND s.cmd NOT LIKE '%RESTORE%'  

           AND s.cmd NOT LIKE 'FG MONITOR%'  

           AND s.hostprocess > ''  

           AND s.spid>50 AND s.spid<>@@SPID)  

           AND lastwaittype NOT IN ('BROKER_EVENTHANDLER', 'CLR_MANUAL_EVENT', 'SLEEP_BPOOL_FLUSH', 'SLEEP_TASK', 'WAITFOR')

  • FormerMember
    0 FormerMember in reply to mandevil

    Thank you for the information! It gives me a place to start - we had thought it was a spreadsheet because of the query, but I hadn't thought of information hard coded in the there.I'll use the query you provided to see if we can locate them!