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.
collinkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris DentPowerShell 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.

http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx

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

http://www.quest.com/powershell/

Example below :)

Chris
# 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
$FSOU = "OU=FILEPRINT,OU=PROD,OU=SERVERS,OU=ROOT,DC=.."
# 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 }
}
$ShareList
 
# 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
$Results
 
# Export the lot to a CSV File
$Results | Export-CSV "SomeFile.csv"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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:?
0
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):

Share
\\Server1\Share
\\Server2\Share

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.

Chris
# 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
$Results
# 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
$Results
# Write them to another CSV file
$Results | Export-CSV "Output.csv"

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.