Finding a File Type across several machines

I did this as one of my very first Powershell scripts and I noticed that it did no work as well as I liked so here is another attempt.
This one serches AD for a list of Servers and then looks for MDF ( SQL DB files ) on the D:, E: and F: drives.


#
function Get-Servers {
	$strCategory = "computer"

$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(objectCategory=$strCategory)")

$colProplist = "name"
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

$colResults = $objSearcher.FindAll()

foreach ($objResult in $colResults)
    {$objComputer = $objResult.Properties; $objComputer.name}
}

function Get-MDF{ 
	Param(    
	[string] $srv   
	)  
	
	Get-ChildItem -path ("\\" + $srv) -Recurse -Filter "*.mdf" -ea continue |
		foreach { 
	       $ws.Cells.Item($row, 3) = $_.FullName 
                $ws.Cells.Item($row, 4) = $_.Length       
                $ws.Cells.Item($row, 5) = $_.LastAccessTime    
                $ws.Cells.Item($row, 6) = $_.CreationTime
                $ws.Cells.EntireColumn.AutoFit()
                $row++ }
				}
	
$xlCellTypeLastCell = 11	
$a  = Get-Date -format g
$row = 2 
$xl = New-Object -c excel.application 
$xl.visible = $True
$xl.DisplayAlerts = False
 
$wb = $xl.workbooks.add() 
$ws = $wb.sheets.item(1) 
$ws.Range("A1:T1").Font.Bold = $true 
$ws.Range("A:A").Font.Bold = $True 
$ws.Cells.Item(1, 1) = "Computer Name"
$ws.Cells.Item(1, 2) = "Date Run: "  + $a
$ws.Cells.Item(1, 3) = "FullName"
$ws.Cells.Item(1, 4) = "Length"    
$ws.Cells.Item(1, 5) = "LastAccessTime"    
$ws.Cells.Item(1, 6) = "CreationTime"

# You could say $computerlist = gc "c:\temp\servers.txt"
# you will need a list of computers, one computer name per line
# 
$computerlist = Get-Servers
 foreach ($srv in $computerlist) 
 { 
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row + 1
$ws.Cells.Item($row, 1) = $srv
$response = Get-WmiObject -query "Select * From Win32_PingStatus Where Address = '$srv'" 
  
  if( ($response -eq $null) -or ($response.StatusCode -ne 0)) { 
    $ws.Cells.Item($row, 2).Font.ColorIndex = 3 
    $ws.Cells.Item($row, 2) = "Does Not Ping"
    $row++ 
    } else { if ($response.TimeToLive -le 64) 
       { 
      $ws.Cells.Item($row, 2).Font.ColorIndex = 5
      $ws.Cells.Item($row, 2) = "probably is a Unix host"
      $row++
       } Else { 
      # Change the path and the extension. If you want all files, remove the -include *.???
      # This might be faster get-childitem  -path \$srvc$ -filter "*.mdb" -r -ea continue |
    	$string = ""
		if(Test-Path \\$srv\D$){
		$srvD = ($srv + "\D$")
				Get-MDF $srvD }
		Else { $string =  "No D: Drive"
		$ws.Cells.Item($row, 2) = $string
			$ws.Cells.Item($row, 2).Font.ColorIndex = 5
		}		
		If (Test-Path \\$srv\E$) { 
			$srvE = ($srv + "\E$")	
		Get-MDF $srvE }
		Else { $string = $string + " No E: Drive"
		$ws.Cells.Item($row, 2) = $string
		$ws.Cells.Item($row, 2).Font.ColorIndex = 5
		}
				
		If (Test-Path \\$srv\F$) { 
		$srvF = ($srv + "\F$")
				Get-MDF $srvF }
		Else { $string = $string + " No F: Drive" 
		$ws.Cells.Item($row, 2) = $string
		$ws.Cells.Item($row, 2).Font.ColorIndex = 5
		}
		}
			$ws.Cells.EntireColumn.AutoFit()
			$row++
  }
 }
 $xlWorkbookNormal = -4143 
$wb.SaveAs("C:\Scripts\find_MDFfiles.xlsx", $xlWorkbookNormal )
# close and release resources 
$wb.close($false) 
$xl.quit() 
spps -n excel # this will shut down all instances of Excel, so be carefull.
 
#—- End Script ————–>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s