Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to retreive value of computer management accecced by column?

Posted on 2014-03-03
26
Medium Priority
?
99 Views
Last Modified: 2016-01-13
Hi all,

I need a way to retreive (preferable in vba) the name that each pc has on the network, when they pose a lock on file its displayed under computer management/accessed by column, (see attached).

I would like to store this value in a table for reference as I have this very often users posing a lock on a file and I have no idea which user this is, currently I can see only pc names that are meaningless..

Thanks,
Ben
Doc2.doc
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
26 Comments
 
LVL 2

Expert Comment

by:sarvjita
ID: 39902383
hi
1. computer management > shared folders > sessions
2. you can use very tiny software (sharewatch)
http://stevemiller.net/sharewatch/
3. Run fsmgmt.msc and click sessions

hope these suggestions will help.

i think sharemonitor is software you are looking for, it has all you want i.e vb code, excel log etc.

http://www.mp3vcr.com/sharemonitor/
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39904591
Hi sarvjita, thanks for replying, I am looking into & will let you know.
Just wonder if there is no build in function to perform that, only a 3rd party software?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 39908137
Hi sarvjita, my manager don't like the idea of me installing software on the server,
As I mentioned, there should be a dos command or a windows API function to get it...
Thanks,
Ben
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 38

Expert Comment

by:younghv
ID: 41405799
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41405797
Hi,
That question is still relevant for me, if you can have a VB expert look into it, I will appreciate.
Thanks
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41405800
Hi,
That question is still relevant for me, if you can have a VB expert look into it, I will appreciate.
Thanks
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41408615
Hi, there's a script here from a few years ago that should give you the values you are after:
http://www.experts-exchange.com/questions/27111803/Viewing-Open-Files-on-Windows.html

Regards,

Rob.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41409306
@Rob,

I tried that, It asked me for a name of a file or query.
I put in a name of a file, that I know is in use
the file is on the server, and I included the full path.
All I got was a message "Files Matching.." & the name of my file.

I am missing something here?

Thanks,
Ben
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41409482
Try this revision to output all open files from that specified server.

Rob.

strServerName = "YourServerName"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
Set objNetwork = CreateObject("WScript.Network")
'strFileName = InputBox("Enter the file name to query:")
strTempFile = objNetwork.ComputerName & "_" & objNetwork.UserName & "_Results.csv"
'If Trim(strFileName) <> "" Then
	'objShell.Run "cmd /c openfiles /query /s " & strServerName & " /fo csv| find /i """ & strFileName & """ > " & strTempFile, 0, True
	objShell.Run "cmd /c openfiles /query /s " & strServerName & " /fo csv > " & strTempFile, 0, True
	Set objFile = objFSO.OpenTextFile(strTempFile, 1, False)
	'strOutput = "Files matching " & strFileName & ":"
	strOutput = "Files open on " & strServerName & ":"
	While Not objFile.AtEndOfStream
		strLine = objFile.ReadLine
		If Trim(strLine) <> "" Then
			If InStr(strLine, ",") > 0 Then
				strUser = Split(strLine, ",")(1)
				strUser = Left(strUser, Len(strUser) - 1)
				strUser = Mid(strUser, 2)
				strFileName = Split(strLine, ",")(3)
				strFileName = Mid(strFileName, InStrRev(strFileName, "\") + 1)
				strFileName = Left(strFileName, Len(strFileName) - 1)
				strOutput = strOutput & VbCrLf & Left(strUser & String(20, " "), 20) & strFileName
			End If
		End If
	Wend
	objFile.Close
	objFSO.DeleteFile strTempFile, True
	WScript.Echo strOutput
'End If

Open in new window

0
 
LVL 4

Author Comment

by:bfuchs
ID: 41409664
@Rob,

Try this revision to output all open files from that specified server.
Actually my intention is to figure out on each pc what is their network name, so I run this on each pc once, save that in a table, and when I see on server that "this file is accessed by user(s) so and so.." (as you can see on original attachment) I know where to look at.

Thanks,
Ben
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41409697
OK, so to run in the "current" PC, move this line
Set objNetwork = CreateObject("WScript.Network")

up to the very top, so that this becomes the second line
strServerName = "YourServerName"

Then change that strServerName line to
strServerName = objNetwork.ComputerName

and that should work.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41409702
OK Rob.

Will try that tom at work.

Good Night.

Thanks,
Ben
0
 
LVL 5

Expert Comment

by:nashim khan
ID: 41409924
Hi,

Rob had given the details to the below link. Please use the link on which Rob is provided the steps.

Thank's to Rob, and here is the link and steps given by rob.
http://www.experts-exchange.com/questions/27111803/Viewing-Open-Files-on-Windows.html
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41411328
@Rob,

Tried doing that will your original code as below, and same thing.

Public Function t()

Set objNetwork = CreateObject("WScript.Network")
strServerName = objNetwork.ComputerName
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")

strFileName = InputBox("Enter the file name to query:")
strTempFile = objNetwork.ComputerName & "_" & objNetwork.UserName & "_Results.csv"
If Trim(strFileName) <> "" Then
    objShell.Run "cmd /c openfiles /query /s " & strServerName & " /fo csv| find /i """ & strFileName & """ > " & strTempFile, 0, True
    Set objFile = objFSO.OpenTextFile(strTempFile, 1, False)
    strOutput = "Files matching " & strFileName & ":"
    While Not objFile.AtEndOfStream
        strLine = objFile.ReadLine
        If Trim(strLine) <> "" Then
            If InStr(strLine, ",") > 0 Then
                strUser = Split(strLine, ",")(1)
                strUser = Left(strUser, Len(strUser) - 1)
                strUser = Mid(strUser, 2)
                strFileName = Split(strLine, ",")(3)
                strFileName = Mid(strFileName, InStrRev(strFileName, "\") + 1)
                strFileName = Left(strFileName, Len(strFileName) - 1)
                strOutput = strOutput & vbCrLf & Left(strUser & String(20, " "), 20) & strFileName
            End If
        End If
    Wend
    objFile.Close
    objFSO.DeleteFile strTempFile, True
    'WScript.Echo strOutput
    Debug.Print strOutput
End If
End Function

Open in new window


I am running it in VBA instead of a script, but that usually works well in my other modules.

P.S. Not sure why is it asking me for a file name, however I did provide a valid file name.

Thanks,
Ben
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41411435
OK, so things work a bit differently in Excel.  I have added VBA specific code so it will output to the sheet called Sheet1

Public Function t()
    Set objNetwork = CreateObject("WScript.Network")
    strServerName = objNetwork.ComputerName
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objShell = CreateObject("WScript.Shell")
    
    strTempFile = ActiveWorkbook.Path & "\" & strServerName & "_" & objNetwork.UserName & "_Results.csv"
    objShell.Run "cmd /c openfiles /query /s " & strServerName & " /fo csv > " & strTempFile, 0, True
    Set objFile = objFSO.OpenTextFile(strTempFile, 1, False)
    strSheetName = "Sheet1"
    ActiveWorkbook.Sheets(strSheetName).Cells(1, 1).Value = "User"
    ActiveWorkbook.Sheets(strSheetName).Cells(1, 2).Value = "File name"
    ActiveWorkbook.Sheets(strSheetName).Rows("1:1").Font.Bold = True
    intRow = 2
    While Not objFile.AtEndOfStream
        strLine = objFile.ReadLine
        If Trim(strLine) <> "" Then
            If InStr(strLine, ",") > 0 Then
                If strLine <> """ID"",""Accessed By"",""Type"",""Open File (Path\executable)""" Then
                    strUser = Split(strLine, ",")(1)
                    strUser = Left(strUser, Len(strUser) - 1)
                    strUser = Mid(strUser, 2)
                    strFileName = Split(strLine, ",")(3)
                    strFileName = Mid(strFileName, 2)
                    strFileName = Left(strFileName, Len(strFileName) - 1)
                    ActiveWorkbook.Sheets(strSheetName).Cells(intRow, 1).Value = strUser
                    ActiveWorkbook.Sheets(strSheetName).Cells(intRow, 2).Value = strFileName
                    intRow = intRow + 1
                End If
            End If
        End If
    Wend
    objFile.Close
    objFSO.DeleteFile strTempFile, True
End Function

Open in new window


Regards,

Rob.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41411462
@Rob,

Excuse me for not being specific..

I use VBA in Access modules. (not so familiar with Excel coding)

Can you tell me what do I need to change in order to work from there?

Thanks,
Ben
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41411476
OK, do you want the data to go into a table?  Can you please first run it in Excel so that you can see what it is doing?  If you're happy with that information that goes into columns A and B, we can change it to add the data to an Access table.

Rob.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41411490
No, I know how to save the data into a table in Access.

My Question is how to execute that in Excel?

I am only familiar with MS Access.

Just need basic info, where to paste the code & how to run it in excel etc..

Thanks,
Ben
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41411526
Oh I see.  OK, so create a new Excel workbook, and save it.  You need to save it for the function to work.

Press ALT+F11 to open the VBA Editor.

On the left, under the VBA Project node for your newly saved workbook, right click "ThisWorkbook" then click Insert --> Module

Paste all of the code into the blank page on the right hand side, then press F5 to run the function.  Switch back to your worksheet and you should see the data (provided the computer you are running it on has open files from other computers).

Rob.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41411549
I just followed that, and excel file only contained two column headers.

provided the computer you are running it on has open files from other computers

Rob, I guess there is a misunderstanding here.

What I am looking for is a way to know how this computer (i'm running the code at) is named on the network server, and thus I can later lookup whenever the server lists open files/accessed by, who exactly this user is.

If you open the file Doc2.doc I had attached along with the original question, you will see what I mean.

Let me know if there is a problem opening that file, I will create a new in PGN format.

Thanks,
Ben
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41411633
Hi, the Open Files page does not show you the name of the computer that is accessing the file.  It shows the name of the user.  I think I may be missing what you are trying to see.  Let's say you are viewing the Open Files on a file server called FILESVR1.  With the script, if you make strServerName = "FILESVR1" you will have in the Excel sheet the same list of users and the file or folder they have open.

With that, you can close a file, but you cannot see directly exactly which computer that is being opened from, or which computer that user is logged on to.  If you *know* that a user is only logged on to one specific computer, then you can determine the computer name that has the file open, but otherwise, not easily.

If someone were to ring you can say they can't open a file because Word tells them it is open by "usera", you can close that file in the Open Files snap-in, if required.

In my environment, when I want to know which computer a specific user is logged on to, I run a VBS logon script for every user, that records the computer they have logged onto in a text file, so I can look up that user's file, and see their logon activity.

Rob.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41411710
the Open Files page does not show you the name of the computer that is accessing the file.  It shows the name of the user
Yes Rob,You got it, I guess this is what I need,

Let me know if you have a function that can provide me that info.

Again, I only need to run in a individual pc and function should return the user name.

The running on each pc & saving into table I will perform.

Thanks,
Ben
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 41411715
The Excel code that is posted will show you all of the files open on the current computer that are being accessed by other computers across the SMB shares.

Can we spell it out a bit so I can clarify what it is you are doing?

What I think you are asking is this:
1) Go to a PC (the CLIENT) and find out who the current user is - easy enough with objNetwork.Username
2) You want to know what files that user has open on network shares (on the SERVER) - the code in the Excel file needs to be run with strServerName being the name of the file server that advertises the network share the file is open on - if you only have one file server, specify that
3) With the info from the point 2 above, you can filter the results based on the current user from point 1

Maybe I've got it totally backwards....sorry.  Can you explain the steps you want to take in terms of client PCs and server PCs?
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 41411727
#1 is what I was looking for.

Thanks for clarifying what I need

And thanks again for providing it..

Ben
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 41411729
Great, no problem.  Looks like I was looking at the big picture, and missing the finer point.

Glad to help.

Regards,

Rob.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

722 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