Solved

how to retreive value of computer management accecced by column?

Posted on 2014-03-03
26
77 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
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 3

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 3

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
 
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 3

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 3

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 3

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 3

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 3

Author Comment

by:bfuchs
ID: 41409702
OK Rob.

Will try that tom at work.

Good Night.

Thanks,
Ben
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 3

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 3

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 3

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 3

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 3

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 500 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 3

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

705 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

12 Experts available now in Live!

Get 1:1 Help Now