[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1035
  • Last Modified:

Confirm files exist on UNC path

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
  • 2
  • 2
3 Solutions
Joseph DalyCommented:
This is very simple using powershell. Test-Path command does exactly what you want.

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

Joseph DalyCommented:
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

PantoffelSlippersAuthor Commented:
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
PantoffelSlippersAuthor Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now