Hey, Scripting Guy! Question

Hey, Scripting Guy! We have a whole bunch of folders named Archive, all on the same computer. How can I search for all these folders, go through each one, and remove any files that are older than XX number of days?

-- CH

SpacerHey, Scripting Guy! AnswerScript Center

Hey, CH. Well, it’s Day 2 of TechEd IT Forum here in Barcelona, and today’s the day the Scripting Guys really go back to work. Yesterday we only had to man our booth in the Ask the Experts section (booth 22; thanks for asking) for a couple hours in the evening. For the rest of the week, however, we’ll be here pretty much all day. (Well, when you’re a Scripting Guy you consider 10:00 AM to 4:00 PM to be pretty much all day.)

Yesterday the Scripting Guys took advantage of their free time to meander through Barcelona, taking in the sights like the Sagrada Familia (The Holy Family). The Sagrada Familia is a massive cathedral that at first looks like it was designed by Dr. Seuss and yet still inspires a sense of awe in anyone who visits it. Construction began on the cathedral way back in 1882 and is still on-going (and still a long ways from completion). As part of their visit yesterday the Scripting Guys made a bet with city authorities. The bet? Which project will be finished first: the Sagrada Familia or that Active Directory browser the Scripting Guys hinted at several years ago?

Insider’s tip. If we were you, we’d bet on the Sagrada Familia.

Of course, had we been smart we would have made this bet: Which project will be finished first: the Sagrada Familia or a script that can remove files older than a specified number of days from all the folders with the name Archive on a computer?

Believe it or not, that’s a bet the Scripting Guys would have won:

dtmDate = Date - 90

strDay = Day(dtmDate)

If Len(strDay) < 2 Then
    strDay = "0" & strDay
End If

strMonth = Month(dtmDate)

If Len(strMonth) < 2 Then
    strMonth = "0" & strMonth
End If

strYear = Year(dtmDate)

strTargetDate = strYear & strMonth & strDay

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colFolders = objWMIService.ExecQuery _
    ("Select * from Win32_Directory Where FileName = 'Archive'")

For Each objFolder in colFolders
    Set colFiles = objWMIService.ExecQuery _
        ("ASSOCIATORS OF {Win32_Directory.Name='" & objFolder.Name & "'} Where " _
            & "ResultClass = CIM_DataFile")

    For Each objFile In colFiles
        strDate = Left(objFile.CreationDate, 8)
        If strDate < strTargetDate Then
            objFile.Delete
        End If
    Next
Next

Let’s see if we can figure out how this script works. We’ve decided that we want to delete all the files that are older than 90 days. Of course, to do that we need to know the date 90 days ago. Our first line of code calculates that date and then stores it in a variable named dtmDate:

dtmDate = Date - 90

Because we’re going to use WMI we next need to convert dtmDate to a UTC-like date time value. We won’t discuss the UTC date-time format today; for more information see the Microsoft Windows 2000 Scripting Guide. For now all we need to know is that we must convert our date to something similar to this, with the first four digits representing the year, the next two digits representing the month, and the last two digits representing the day:

20070809

It’s pretty easy to extract the individual pieces of a date-time value; we can do that using the Day, Month, and Year functions. The one tricky part occurs when we have a date like 8/9/2007. In that case both the month (8) and the day (9) have only a single digit. Why is that a problem? Well, that’s a problem because, in the UTC format, the month and the day must be two digits longs. To deal with that problem we’ve included code like this for both the day and the month:

strDay = Day(dtmDate)

If Len(strDay) < 2 Then
    strDay = "0" & strDay
End If

What’s going on here? Well, here we’re using the Day function to grab the day portion of the target date and store it in a variable named strDay. We then use the Len function to check the number of characters in strDay. If strDay has less than 2 characters we use this line of code to tack a leading zero onto the value:

strDay = "0" & strDay

In other words, if strDay equals 9, the preceding block of code will add a leading zero, making strDay equal to this:

09

After assuring ourselves that both the day and month have two digits, we then use this line of code to combine the year, month, and day into a UTC-like value:

strTargetDate = strYear & strMonth & strDay

Now we’re ready for some fun. Our next step is to connect to the WMI service on the local computer. As usual, you could just as easily run this script against a remote computer; to do that simply assign the name of the remote computer to the variable strComputer:

strComputer = "atl-fs-01"

We then use the ExecQuery method and this line of code to retrieve a collection of all the folders that have the folder name Archive:

Set colFolders = objWMIService.ExecQuery _
    ("Select * from Win32_Directory Where FileName = 'Archive'")

What, exactly, will this query bring back? Well, like we said, it will bring back a collection of all the folders that have the name Archive. That will include folders like this:

C:\Archive
D:\Archive
C:\Documents and Settings\Kenmyer\Archive
C:\Documents and Settings\Pilara\My Documents\Archive

Etc., etc.

So what’s next? Well, next we set up a For Each loop to loop through each of the folders in our collection. Inside that loop, we use one of those oddball Associators Of queries to return a collection of all the files found in the first folder in that collection:

For Each objFolder in colFolders
    Set colFiles = objWMIService.ExecQuery _
        ("ASSOCIATORS OF {Win32_Directory.Name='" & objFolder.Name & "'} Where " _
            & "ResultClass = CIM_DataFile")

After that we set up a second For Each loop, this one designed to loop through the collection of files found in folder number 1. Inside that loop we execute this block of code:

strDate = Left(objFile.CreationDate, 8)

If strDate < strTargetDate Then
     objFile.Delete
End If

Interesting piece of work, isn’t it? What we’re doing here is using the Left function to grab the first 8 characters from the CreationDate property. Because CreationDate stores values in UTC format, that means strDate will equal something like this:

20070618

We now have two integer values we can compare: strDate (the date the file in question was created) and strTargetDate (the date 90 days prior to today). Because we cleverly-configured these dates to act like integers that means we can compare these dates using this line of code:

If strDate < strTargetDate Then

Using our example dates, that line of code, in effect, is saying this:

If 20070618 < 20070809 Then

As it turns out, 20070618 is less than 20070809. That means this file is old, and also means that we need to delete it, something we do using the Delete method:

objFile.Delete

We then loop around and repeat the process with the next file in the collection. After we’ve looped through all the files in the first folder, we then return to the top of our first loop and repeat the process with the next folder in the collection.

By the time we’re done, we’ll have gone through all the folders named Archive and deleted any files older than 90 days.

It’s as easy as that.

For those of you who are wondering, the best guess is that the Sagrada Familia will be finished around the year 2026 (the same year the Scripting Guy who writes this column turns 40). When construction began in 1882, it was expected that the project would take several hundred years to complete. (Roughly the same amount of time the Scripting Guys expect to take with that Active Directory browser.) However, thanks to computers and to CAD/CAM technology it’s now expected that the project can be finished within the next 20 years.

Hmmm …. You know, maybe the Scripting Guys should try using computers to help them create that Active Directory browser ….