Searching the Spotlight Database From The Command-Line

I’ve got a lot of MP3s, and I’m slightly obsessed with managing them. The problem is, I don’t know which ones are listed in my iTunes Library, and which ones aren’t.

Here’s how I used Spotlight to locate all the missing, “un-databased” MP3s.

First off, currently it’s not easy to create a Smart Folder that will match on a Boolean NOT. It’s possible, but not easy. The way to do it is to enclose the NOT term in parentheses, with a hyphen. For example, “bugs(-aphids)” would show all instances of “bugs”, and not “aphids”. As far as I know, there’s no good way to search for an empty set, or “”.

Since I wanted to find files NOT in my iTunes Library, I decided to attempt to tag all of the files that did exist in my library with a unique ID — for example, “OCD”. I selected all of the MP3s in my library, hit cmd-i, and added “OCD” to the comments field. Depending on the size of your library, this’ll take a while to process.

Once that was done, I had to drop to a Terminal shell, and use the built-in “mdls” and “mdfind” tools.

The overall idea is to use mdls to determine the names of the metadata fields you’re looking for, and then to use mdfind to actually find the files that match (or don’t match).

An example of mdls:

[server% mdls 02.\ VNV\ Nation\ -\ Standing\ (Motion).mp3
02. VNV Nation - Standing (Motion).mp3 ————-
kMDItemAlbum = “Standing (EP)"
kMDItemAttributeChangeDate = 2005-05-03 21:17:11 -0400
kMDItemAudioBitRate = 192
kMDItemAudioChannelCount = 2
kMDItemAudioSampleRate = 44100
kMDItemAuthors = (“VNV Nation”)
kMDItemComment = “OCD”
kMDItemComposer = “"
kMDItemContentCreationDate = 2002-10-12 11:15:14 -0400
kMDItemContentModificationDate = 2005-05-03 21:17:11 -0400
kMDItemContentType = “public.mp3”
kMDItemContentTypeTree = (
“public.mp3”,
“public.audio”,
“public.audiovisual-content”,
“public.data”,
“public.item”,
“public.content”
)
kMDItemDisplayName = “02. VNV Nation - Standing (Motion).mp3”
kMDItemDurationSeconds = 1468
kMDItemFSContentChangeDate = 2005-05-03 21:17:11 -0400
kMDItemFSCreationDate = 2002-10-12 11:15:14 -0400
kMDItemFSCreatorCode = 0
kMDItemFSFinderFlags = 0
kMDItemFSInvisible = 0
kMDItemFSLabel = 0
kMDItemFSName = “02. VNV Nation - Standing (Motion).mp3”
kMDItemFSNodeCount = 0
kMDItemFSOwnerGroupID = 20
kMDItemFSOwnerUserID = 501
kMDItemFSSize = 11752996
kMDItemFSTypeCode = 0
kMDItemID = 7695
kMDItemKind = “MP3 Audio File”
kMDItemLastUsedDate = 2003-04-29 16:46:21 -0400
kMDItemMediaTypes = (Sound)
kMDItemMusicalGenre = “Industrial”
kMDItemTitle = “02. VNV Nation - Standing (Motion)"
kMDItemTotalBitRate = 192
kMDItemUsedDates = (2003-04-29 16:46:21 -0400)

From this info I could tell that the “kMDItemComment” field is the one I needed.

The easiest way to do this is to hit cmd-F in the Finder, change ‘Kind’ to ‘Raw Query’, then enter: kMDItemComment != ???????OCD???????? && kMDItemContentType = ‘public.mp3’

This will display all the MP3s without “OCD” in the Comments field.

I saved this as a Smart Folder, which maked it much easier to drag-and-drop them into iTunes.

The CLI-way to do this is harder:

I ran mdfind to locate all files whose “kMDItemComment” field was NOT “OCD”, and only the files that were MP3s. I also sent this output to a text file for safekeeping. (For some reason, sometimes changing the Comment field appends the new comment, and doesn’t overwrite the old one. That’s why I had to use a wildcard on both sides of OCD.)

mdfind “kMDItemComment != ‘OCD’” | grep mp3 > missing.txt