Filesystem reports

Doug asked me to let him know how many Lotus Approach DBF and APR files are present on our file servers at present. He was hoping that the report might be something that he could “save to file”.

After a lot of mucking around with gnuwin32 tools, I decided that the best “quick fix” was to do the following. The Windows Server Recource Kit is required:

  1. As someone with read access to the entire filesystem, run:
    dir /s /t:c \filesshared | qgrep /y -E “.dbf .apr” > dbreport.txt
    alternatively, one could use the gnu “ls” command to get similar output in a slightly ritcher format.
    the /t:c switch forces dir to display the last-changed timestamp rather than the default last-accessed. The /y performs a case insensitive grep. -E parses results on the end of the line only.
    note as a fun side project, I had to disable viewing of the ~snapshot directories on our NetApp filer while the query was running, as the dir command insisted on parsing through all of the snapshot directories. Aargh!
  2. next, I import the txt file into excel. I used the “Data->Filter->Advanced Filter” menu to mask the many duplicate records in the sheet, then past the filtered view into a new sheet.
  3. To get a better grip on the actual number of production Approach databases, I saved the filtered sheet to a new text file then ran the following command:
    type dbreport-filt.txt | qgrep -y -E “.apr” > dbreport-apronly.txt
    this generates a list of only the *.apr records in the file. I then re-import into a new Excel sheet. All three sheets have been shipped off to Doug for digestion.

Doug has asked for a new report. I thought I would try doing it with “POWERSHELL” this time:

gci \filesshared -recurse -include *.apr,*.mdb -exclude ~snap* | Select-Object LastWriteTime,Length,Name | export-csv dbhunt.csv

We are using the “get-childobject” (alias “dir” or “gci”) to get a recursive (-recurse) directory listing of \filesshared. We are excluding those pesky ~snapshot directories (-exclude ~snap*), and filtering for only APR and DBF files (-include *.apr,*.mdb – (NOTE: I got the expression via trial-and-error… I cannot find a good reference on PowerShell regular expressions and wild card matching. I would have thought “$.apr|$.dbf” would work, but it did not.)). We the pipe (|) to select-object and choose only the LastWriteTime, Length, and Name attributes of the listed files. Finally we pipe to “export-csv” to sent the search results to a csv file.

I am using the PowerShell 1.0 RC1 refresh for this report. Note that I am able to catch both APR and MDB file types in a single pass, and also I am able to filter the output to include only the attributes that I want. Finally, I can export directly to a CSV file for import into a spreadsheet. I suppose I could also have used the “sort” features in the pipeline to segregate the APR and MDB files to the top and bottom of the output file, but I will just do that in the spreadsheet. Another sort option would be to “remove duplicates”, but this could result in some data loss as many DBs may have the same name, but with different content. Somthing I could not figure out how to do in the time available how to auto-format the “Length” data in Megabytes… I know I saw this in a PowerShell demo, but I cannot find my notes, and I can’t glean this from the online documentation).

Ooh! Even better:
gci \filesshared -recurse -include *.apr,*.dbf,*.mdb -exclude ~snap* | Select-Object DirectoryName,Name,Extension,Length,CreationTime,LastWriteTime | sort DirectoryName | Export-Csv \filesflex1$qtree-home10ddsvwinhomedbhunt.csv

In this pass, I have added additional properties to the “select-object” command which I want exported to my CSV report. These properties are the “DirectoryPath” to the file, in addition to it’s creation time. Also, I have a separate field for “extension” to ease in reporting later on. I also have added a “sort” command to the pipeline so that the output file will already be sorted by DirectoryName.

I think this will take about 3 hours to run, so I am just going to dump the report straight to Doug’s home directory. Huzzah!