ADSI script that searches multiple servers / shares for file types

I need to query all server files and sub folders in our AD enterprise (OU=FILEPRINT,OU=PROD,OU=SERVERS,OU=ROOT, ,DC=.)..for database files.
I need to find all databases in all shared directories and get the info back to a csv on the date created, modified, size, path and name.  

I have a list of shares in our environment, which is (very) 1325 lines long, but it is really only about 30 servers  So, I was thinking an ADSI script could query all those servers and bring out the data nicely.

A batch or cmd script that could process the share list in the csv, line by line, would be fine, or one that could go to our forest and search all the file servers for database files.

I've got a vb script going, but I'm really struggling with it.  I think I don't know how to call, declare or name file properties in a script or parse out the output either!

Thanks for your help.
Who is Participating?
Chris DentConnect With a Mentor PowerShell DeveloperCommented:

If you're not all that familiar with VbScript I recommend you switch to PowerShell. It's probably easier to learn as it has so many built in components to get what you want quickly.

Then grab the Quest CmdLets as well to fill in the holes for Active Directory management:

Example below :)

# A list of Extensions to look for. Comma delimited will do.
# Must include the preceeding . for each extension
$Extensions = ".mdb"
# Where we find the servers
# Get the servers from AD (Quest CmdLet)
$Servers = Get-QADComputer -SearchRoot $FSOU
# Something to store the list of shares in
$ShareList = @()
# This is the WMI filter used to return shares. It currently excludes Hidden shares
# % is a Wildcard character for WQL / SQL queries.
$Filter = "Type='0' AND NOT Name LIKE '%`$'"
# Note that Administrative Shares will not be returned by this search (different Type)
# Take the list of servers and get the shares
ForEach ($Server in $Servers) {
  # Get the shares for this server
  $Shares = Get-WMIObject Win32_Share -ComputerName $Server.Name -Filter $Filter | %{
    $_ | Select-Object @{n='ServerName';e={ $Server.Name }}, Name, Path }
  # If it got the shares, add them into the list
  If ($Shares) { $ShareList += $Shares }
# Something to store the results in
$Results = @()
# Take the list of shares and look for the file type under each.
# This is a slow operation for large structures.
# % is shorthand for ForEach-Object
$ShareList | %{
  # Get-ChildItem is like the DOS command DIR in this context
  # ? is Where-Object, where the item we found has an extension, and 
  # where it matches one of those in our list.
  $Results += Get-ChildItem "\\$($_.ServerName)\$($_.Name)" -Recurse | `
    ?{ ($_.Extension) -And $Extensions -Match $_.Extension } | %{
      $_ | Select-Object Name, FullName, CreationTime, LastWriteTime, Length }
# Display it here
# Export the lot to a CSV File
$Results | Export-CSV "SomeFile.csv"

Open in new window

collinkAuthor Commented:
Thanks.This is fantastic.   I've got Powershell installed, Quest PowerGUI and PowerGUI ScriptEditor, and the blogs and tutorials to make progress. I can see that this scripting envirionment is a tad more straightforward from the get go and very powerful.

(PowerGUI ScriptEditor is pulling out the servers from AD and demonstrating that it could be successufl. However, I'm getting an access denied message when it gets to processing the shares.  PErhaps  the shares aren't being found because my account does not have permissions on the shares. I may have to ask to be added to the the domain admin group  to test?                        
I am wondering again about a simple  cmd excel formula to search that excel \\server\drectory list instead I should have permissions there if not on the shares -- I think. I just want to get this done wihtout having to plead for rights. Cna you think of a cmd or batch file that will search the column A list of the \\server\directory and and send the *.mdbs found out to a file,  dir $a1 /s /b *.mdb >> C:\FILESTATS.csv. even if i have to do every server data directory, that could be possilbe, yes:?
Chris DentPowerShell DeveloperCommented:

From Excel is no fun, it's a pain in the neck to try and account for Office objects (in my opinion). However... exporting to CSV from Excel is real easy, and reading CSVs in PowerShell is also real easy.

So... imagine we had a CSV file with the share like this (the header line would need to be included):


Then we could do the below. Notice that we use the Column Name, "Share", to reference the field we want in the CSV. That's why it's important that the CSV file has header names.

If you'd rather just have the results echo immediately then it would just need "$Results = " removing from the beginning.

And as a little note... If this were a simple text list instead, Get-Content can be used with a small modiifcation.

# The CSV File version:
$Extensions = ".mdb"
$Results = Import-CSV "TheCSVFile.csv" | %{
  Get-ChildItem $_.Share -Recurse | `
      ?{ ($_.Extension) -And $Extensions -Match $_.Extension } | %{
      $_ | Select-Object Name, FullName, CreationTime, LastWriteTime, Length }
# Display the results on the screen
# Write them to another CSV file
$Results | Export-CSV "Output.csv"
# The Get-Content version. For simple text files.
$Extensions = ".mdb"
$Results = Get-Content "TheFile.txt" | %{
  Get-ChildItem $_ -Recurse | `
      ?{ ($_.Extension) -And $Extensions -Match $_.Extension } | %{
      $_ | Select-Object Name, FullName, CreationTime, LastWriteTime, Length }
# Display the results on the screen
# Write them to another CSV file
$Results | Export-CSV "Output.csv"

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.