Solved

Confirm files exist on UNC path

Posted on 2013-11-15
4
767 Views
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
0
Comment
Question by:PantoffelSlippers
  • 2
  • 2
4 Comments
 
LVL 35

Assisted Solution

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

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

http://technet.microsoft.com/en-us/library/ee177015.aspx
0
 
LVL 35

Assisted Solution

by:Joseph Daly
Joseph Daly earned 300 total points
Comment Utility
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

0
 

Accepted Solution

by:
PantoffelSlippers earned 0 total points
Comment Utility
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
Else

      'Iterate verification paths
      Do While NOT Recordset.Eof  
      
            'Initiate
            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
                        Else
                            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
                        Next
                  
                  Else
                     tmpAccessResult = 0
                  End If
                  
                  
            Else
                  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
            Recordset.MoveNext    
      Loop
End If  'verification recordset has records

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

Author Closing Comment

by:PantoffelSlippers
Comment Utility
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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
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.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

763 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

6 Experts available now in Live!

Get 1:1 Help Now