Solved

Confirm files exist on UNC path

Posted on 2013-11-15
4
889 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
[X]
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
4 Comments
 
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

http://technet.microsoft.com/en-us/library/ee177015.aspx
0
 
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

0
 

Accepted Solution

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

Featured Post

How our DevOps Teams Maximize Uptime

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

Question has a verified solution.

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

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…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

734 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