Little Gremlins in the Computer

A couple of weeks ago, something weird happened on my machine so I decided to do some investigations. I basically wanted to see what files have changed on my machine on that day. After much Googling and trying out things, I ended up learning that Microsoft and other vendors are doing work on my machine after I have shut the computer down.

I can generate change information by using “datemodified:today” in the search bar of the File Explorer; however, that can produce a long list of files and folders to search through. Image 1 was enlarged with larger text for legibility.

Image 1: Search on “datemodified:today”

I wanted to download that search information and found that it was not that easy to do! I finally found “Select all” under the “Home” tab, and then “Copy path”. Again, images are being enlarged.

Image 2: “Select all” highlighted in yellow under “Home” tab

Image 3: “Copy path” highlighted in yellow

Now after doing “Copy path”, I can paste the information into Excel and do any kind of filtering I pleased. Then I ran into the next problem: while I got a list of files and folders as well as directory paths, I was missing the time that the files were modified. Sometimes you want the date and time.

Image 4: List of files generated after “Copy path”

This missing information necessitated another search for how to pull this information. I finally found something I could work with and it entailed some command instructions in the command prompt. The sites that gave some working information are:

https://superuser.com/questions/355771/windows-listing-of-files-from-command-line

https://stackoverflow.com/questions/17616045/find-files-on-windows-modified-after-a-given-date-using-the-command-line/17616134

After much experimenting, I found the following DOS command structure to work:

dir “C:\Program Files (x86)” /o:ns /t:caw /s >”C:\Users\…\files7.txt”

As best as I understand, the pieces of the DOS command can be broken out as 1) dir “C:\…” as the directory where you want to pull the information; 2) /o:ns gives the name and filesize; 3) /t:caw provides time created, accessed and modified; 4) /s >”C:\…” means where you want to save the text file and the name of the file.

Using dir “C:\” probably won’t work because for me, the results came back as all files and folders in “C:\Users” and it missed a few files and folders that were sitting in “C:\ProgramData” and other machine related files.

One thing I should point out: these DOS commands will list all of the files in the directory listed, not just those files that changed on the date I wanted. I was never successful at just pulling in files and folders with a specific modified date so I ended up pulling in everything in the requested directory.

I had five searches to use in the DOS command:

dir “C:\Program Files (x86)”
dir “C:\ProgramData”
dir “C:\Program Files”
dir “C:\Users”
dir “C:\Windows”

These directories were discovered by sorting the list of files generated by my File Explorer and color blocking the ones with similar directories.

Image 5: Color blocking similar directories found in the File Explorer search

The end results of running the DOS commands were five text files that I copied into a folder called “Combine” for Power Query to access. Accessing by folder was new; instead of accessing each text file individually, I directed Power Query to access the folder and then chose which files to include in the query.

Image 6: The five text files and the folder called “Combine”

When you first pick a folder to access and you choose the files that you want to pull in, there will be a couple of other steps you will have to do before the results from the text files are pulled into the query. Image 7 shows what the Power Query looks like before you do those additional steps.

Image 7: After selecting a folder and the required text files

On the left hand side of the table, you will see a column titled “Content”. Clicking on the double down arrows and then clicking on “Transform” will pull in the data and you should get something similar to the following enlarged image.

Image 8: After clicking on the double down arrows in the Content column and clicking on “Transform” (rather than “Load”)

Now, I had to do a lot more work to pull out the directory path, the date, the time and the file name. Here are the instructions that can be found in the “Advanced Editor”:

let

Source = Folder.Files(“C:\Users\veron\Documents\Veronique\Veronique New\Excel simulations\Searches\Combine”),

#”Filtered Hidden Files1″ = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

#”Invoke Custom Function1″ = Table.AddColumn(#”Filtered Hidden Files1″, “Transform File”, each #”Transform File”([Content])),

#”Renamed Columns1″ = Table.RenameColumns(#”Invoke Custom Function1″, {“Name”, “Source.Name”}),

#”Removed Other Columns1″ = Table.SelectColumns(#”Renamed Columns1″, {“Source.Name”, “Transform File”}),

#”Expanded Table Column1″ = Table.ExpandTableColumn(#”Removed Other Columns1″, “Transform File”, Table.ColumnNames(#”Transform File”(#”Sample File”))),

#”Changed Type” = Table.TransformColumnTypes(#”Expanded Table Column1″,{{“Source.Name”, type text}, {“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}}),

#”Removed Top Rows” = Table.Skip(#”Changed Type”,3),

#”Removed Blank Rows” = Table.SelectRows(#”Removed Top Rows”, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {“”, null}))),

#”Filtered Rows” = Table.SelectRows(#”Removed Blank Rows”, each ([Column1] <> “”)),

#”Added Custom” = Table.AddColumn(#”Filtered Rows”, “Directory”, each if Text.Start([Column1],13) =” Directory of” then Text.End([Column1],Text.Length([Column1])-13) else “”),

#”Inserted Text Before Delimiter” = Table.AddColumn(#”Added Custom”, “Text Before Delimiter”, each Text.BeforeDelimiter([Column1], ” “), type text),

#”Renamed Columns” = Table.RenameColumns(#”Inserted Text Before Delimiter”,{{“Text Before Delimiter”, “Date”}}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“Date”, type date}}),

#”Added Custom1″ = Table.AddColumn(#”Changed Type1″, “Time”, each if [Date]<>null then Text.Start(Text.End([Column1],Text.Length([Column1])-11),9) else “”),

#”Changed Type2″ = Table.TransformColumnTypes(#”Added Custom1″,{{“Time”, type time}}),

#”Added Custom2″ = Table.AddColumn(#”Changed Type2″, “Folder-File”, each if Text.Middle([Column1],24,5)=”<DIR>” then “Folder” else if Text.Contains([Column1],”File(s)”) then “ignore” else if [Directory]<> “” then “Directory” else “File”),

#”Filtered Rows1″ = Table.SelectRows(#”Added Custom2″, each ([#”Folder-File”] <> “ignore”)),

#”Added Custom3″ = Table.AddColumn(#”Filtered Rows1″, “Name”, each if [Column2]<>”” then [Column2] else if [Directory]<>”” then [Directory] else Text.End([Column1],Text.Length([Column1])-39)),

#”Replaced Value” = Table.ReplaceValue(#”Added Custom3″,””,null,Replacer.ReplaceValue,{“Directory”}),

#”Filled Down” = Table.FillDown(#”Replaced Value”,{“Directory”}),

#”Filtered Rows2″ = Table.SelectRows(#”Filled Down”, each ([Date] = #date(2021, 5, 8)))

#”Filtered Rows2″

The #”Expanded Table Column1″ is just after clicking on the double down arrows and transforming into Power Query. All instructions after that point are geared towards pulling out the directory path, the date, the time, and the file name. The last instruction filters the data for the date in question that I want, in this instance 5/8/2021, thus weeding out all of the other files that were changed on another date.

Image 9: Transformed Power Query

If you look at the last column in Image 9, you will find a column titled “Class”. This column contains a formula to “classify” the folder information, whether the folder or file relates to Microsoft, HP, Norton, etc. With this end result, I now have a table that I can use for Pivot tables.

Let’s take a look at the Pivot Tables I created.

The following image shows the results for 4/23/20201. Now, I know I was not up on my machine at 2 or 5 am in the morning! I can open up those times to see what has been changing at that time.