Solved

How to get info in view from activity monitor?

Posted on 2014-11-24
25
66 Views
Last Modified: 2014-12-08
Hi Experts,
I am currently looking to create a view that can join the output of activity monitor with another table (by net address), any idea how I can get this?
0
Comment
Question by:bfuchs
  • 14
  • 10
25 Comments
 
LVL 84
ID: 40463326
Define what you mean by "activity monitor". Are you referring to a System Utility, or something like that?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40463516
hi,
its in SSMS under management tab.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40464034
You need to be more specific.
Activity Monitor provides information about CPU, Processes, Resource Waits, Datafile I/O and Expensive Queries.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40465323
Hi experts,

The story is as follows:

I am using an MS Access FE app to SQL BE, and would like to get a list of users who are logged in, and also who are doing certain tasks, for example holding a lock on the database etc..

In activity monitor there is a column HOST that used to give me accurate info, however in office 2003, the info is not more accurate as it gives me the name of pc that I designed the app with..

Therefore the only way left was to use the NetAddress field in activity monitor, were looks to give more reliable info about the pc's connected to.

Now in order to have users name displayed, I ran a process on each pc, that retrieves the net address, and stores that in a table, therefore the next step is to have the info avail in a view where I can join it with users table and retrieve full name.

Thanks,
Ben
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40466296
Ben, did you use sp_who2 before? If not, try to run it in SSMS and check if returns the information that you need.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40467720
@Vitor,
sp_who2 gives me only the host name, however as mentioned this is not working for users using Access 2003, as all come up with same host name.
Thanks,
Ben
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40468877
Ok. sp_who2 it's only a short view from
SELECT * FROM sys.sysprocesses
Now you should see more columns, net_address included.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40469632
Hi Vitor,
this latest seems to be in good direction, just wonder if I can also get the following 3 columns, Blocking, Blocked By and the Sql command?
Also is this safe to be running from another app linked to SQL (in my case Access ADP) ?
Thanks,
Ben
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40470118
Ok, here's a more elaborated query. Hope that has all the information you want.
SELECT p.spid, p.blocked, r.blocking_session_id, DB_NAME(p.dbid) dbname, p.loginame, p.login_time, p.last_batch, p.status, p.hostname, p.program_name, p.hostprocess, c.client_net_address, t.text
FROM sys.sysprocesses p
	INNER JOIN sys.dm_exec_connections c ON p.spid=c.session_id
	LEFT JOIN sys.dm_exec_requests r ON p.spid=r.session_id
	CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 40472727
Hi,

By executing the above I get the following from SSMS (2008).
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '.'.

please let me know how to correct

Thanks,
Ben
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40473214
Ben, I ran the same code in my SQL Server 2008R2 and didn't have any error.
Can you try to copy the code and run it again?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40474493
Hi Vitor,

I see the problem only occurs when I am running it in my database, but when I run in Master database its fine, how can I modify that to run in my DB?

Thanks,
Ben
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 3

Author Comment

by:bfuchs
ID: 40474671
FYI,
I also tried changing the code by adding  master before each table like the following:
SELECT p.spid, p.blocked, r.blocking_session_id, DB_NAME(p.dbid) dbname, p.loginame, p.login_time, p.last_batch, p.status, p.hostname, p.program_name, p.hostprocess, c.client_net_address, t.text
FROM master.sys.sysprocesses p
	INNER JOIN master.sys.dm_exec_connections c ON p.spid=c.session_id
	LEFT JOIN master.sys.dm_exec_requests r ON p.spid=r.session_id
	CROSS APPLY master.sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t

Open in new window

however the same error msg appears..
and the reason I need this to run in my DB is that when opening Access ADP, I only have access to objects of 1 database.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40474917
@Vitor,
I see this only happens in production database, in test database I don't have this issue, what can explain this strange behavior?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40475348
Check the user permissions. For sure in test database the user is system administrator and in production only has read and write permissions in the user database.

Those tables should be usually accessed by DBA's.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40477486
Hi Vitor,

It looks like you're right, however I cant find what property to look for, as I always log in as 'SA' in both DB,
what is strange to me is the fact that between the users is the SA not listed at all..
maybe you guide me..

Thanks,
Ben
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40477992
Can you run the following queries in the instance that you are having the issue and post here the results:
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')

SELECT loginname, sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin,bulkadmin, dbcreator, denylogin, hasaccess
FROM syslogins

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 40479241
Hi,
for the first one I get 0 in both, the test and production DB.
for the second I get the following in both.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'syslogins'.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40479259
Hi Vitor,

I think that at this point you've answered my original question, however this would be another issue, therefore I would accept your solution and will post this situation as a new thread, (So that I can assign double points-:)

Thanks,
Ben
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 40479261
Great, Thank you!
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40480227
Msg 208, Level 16, State 1, Line 1
 Invalid object name 'syslogins'.
Forgot to tell you that the query should run in master database.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40481511
Hi,

Ok in master I got 0 for the first and for the second I get 1 for sysadmin and for has access  and 0 for the rest, for the user I am talking about.
sa      1      0      0      0      0      0      0      0      0      1

Thanks,
Ben
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40482383
When hasaccess is 0 means that the user don't have access to the instance.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40485746
Hi,
but user has 1 for hasaccess (as shown above, all fields return 0 except for sysadmin and hasaccess), and besides i still cant see a reason why should test be different then production?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40486363
Sorry, I miss understood when I read it at first time. Went to read it again and now it's clear to me.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now