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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 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

17 Experts available now in Live!

Get 1:1 Help Now