Confirm files exist on UNC path

Posted on 2013-11-15
Last Modified: 2013-12-24
Good day exports,

I have a list of about 4000 files distributed across one network (servers and workstations).

For example:   \\workstation01\c$\file.xls

I have sufficient rights on relevant servers and workstations.  I'm looking for an easy script (.bat file or something) that just tests whether each file exists and returns true/false or yes/no or 1/0 whatever.

Workstations are on/off the network at various times of day so whatever script I write will need to be run via windows task scheduler or something similar several times a day and results written to a database or appended to text file.

Please make recommendations or samples.

Many thanks
Question by:PantoffelSlippers
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
  • 2
  • 2
LVL 35

Assisted Solution

by:Joseph Daly
Joseph Daly earned 300 total points
ID: 39651864
This is very simple using powershell. Test-Path command does exactly what you want.

Test-path \\server\path\file.txt
LVL 35

Assisted Solution

by:Joseph Daly
Joseph Daly earned 300 total points
ID: 39651884
Try this code to help you out. You will need to change the  $_.pathcolumnnamefromcsv to be your column header in the csv file. Make sure to keep the $_. though.

Import-CSV C:\file.csv | foreach { $path = $_.pathcolumnnamefromcsv
$result = test-path $path
"$Path - $result">> c:\file.txt

Open in new window


Accepted Solution

PantoffelSlippers earned 0 total points
ID: 39652974
OK so I found it difficult to record results with powershell - think it's just because i never used it before.

I played a bit with VBScript and so far I have the following script that basically does the following:
Connect to SQL server using a ODBC DSN
Retrieve a list of paths to verify from a SQL view
Loop through and perform a test for each path
The test involves ping the machine, test access to c$ and then the full path
Write result back to database

Not sure why I did this as a script and not a .Net application.  I use to code VB.Net and C# previously (years ago).  It's just that I may need to get someone with more network privileges to run this for me.  I thought for some reason a Vbs script file would be easier to have someone else execute....  Not sure why I thought that.....

The VBS script seems to work though.   I have so far:

option explicit


Dim Connection         'adodb connection to sql server database
Dim Recordset            'adodb recordset object to retrieve paths for verification
Dim SQL                        'string that specifies sql query required to retrieve verification paths or update query back to ICU database
Dim objFSO                  'file system object used to verify the existance of file on file system
Dim tmpPSTResult      'bit indicator of whether a specific file exists
Dim strPingResults      'result from the ping test executed against a workstation before verifying a PST file path on the workstation
Dim tmpPingResult      'bit indicator of whether we can ping a machine
Dim tmpLoggedOnUser      'holds the user currently logged on to a machine
Dim tmpAccessResult      'bit indicator of whether we can access c$ on a machine
Dim WshShell            'shell script object to perform a ping test against a workstation before verifying a PST file path on the workstation
Dim WshExec                  'same as above
Dim objWMIService      'WMI service to query current logged in user
Dim colComputer            'used to help query logged in user
Dim objComputer            'same as above

'Initiate SQL Server connection to ICU database server
Set Connection = CreateObject("ADODB.Connection")
Connection.Open "DSN=ICU_PSTPathVerification;Database=PSTCleanup"

'Retrieve paths that require verification
Set Recordset = CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM vw_PSTPathsToVerify"
Recordset.Open SQL,Connection

'Process recordset that we got back from SQL Server
If Recordset.EOF Then
      'Write error to eventlog here

      'Iterate verification paths
      Do While NOT Recordset.Eof  
            Set objFSO = CreateObject("Scripting.FileSystemObject")
            tmpPSTResult = 0
            tmpPingResult = 0
            tmpAccessResult = 0
            tmpLoggedOnUser = ""
            SQL = ""
            'Test if machine responds to ping
            Set WshShell = WScript.CreateObject("WScript.Shell")
            Set WshExec = WshShell.Exec("ping -n 1 -w 500 " & Recordset("MachineName"))
            strPingResults = LCase(WshExec.StdOut.ReadAll)
            If InStr(strPingResults, "reply from") Then
                  tmpPingResult = 1
                  'Test if we can access the dollar share otherwise the PST existance test gives skew result
                  If (objFSO.FOlderExists("\\" & Recordset("MachineName") & "\C$\")) Then
                     tmpAccessResult = 1
                         'Test if the current path being evaluated from the recordset does exist or not and record a result value
                        If objFSO.FileExists(Recordset("VerificationPath")) Then
                            tmpPSTResult = 1
                            tmpPSTResult = 0
                        End If
                        'Get current logged on user
                        Set objWMIService = GetObject("winmgmts:" _
                            & "{impersonationLevel=impersonate}!\\" & Recordset("MachineName") & "\root\cimv2")
                        Set colComputer = objWMIService.ExecQuery _
                            ("Select * from Win32_ComputerSystem")
                         For Each objComputer in colComputer
                          tmpLoggedOnUser = objComputer.UserName
                     tmpAccessResult = 0
                  End If
                  tmpPingResult = 0
            End If
            'Write result back to ICU database
            SQL = "EXEC sp_UpdatePathVerification '" & Trim(Recordset("VerificationPath")) & "', " & tmpPSTResult & ", " & tmpAccessResult
            SQL = SQL & ", " & tmpPingResult & ", '" & tmpLoggedOnUser & "'"
            Connection.Execute SQL
            'Move to next path for verification
End If  'verification recordset has records

'Close the connection and recordset objects to free up resources
Set Recordset=nothing
Set Connection=nothing

Author Closing Comment

ID: 39737632
Thank you experts.

The VBScript I provided is still in use - best solution I found so far - my database is populated with results several times per day and the script just runs.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

AutoHotkey is an excellent, free, open source programming/scripting language for Windows. It started out as a keyboard/mouse macros product, but has expanded into a robust language. This article provides an introduction to it, with links to addition…
Introduction: Recently, I got a requirement to zip all files individually with batch file script in Windows OS. I don't know much about scripting, but I searched Google and found a lot of examples and websites to complete my task. Finally, I was ab…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…

735 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