See previous post about Jellyfin for details on Jellyfin database access.
1 2 3 4 5 | select artists,album,IndexNumber,name,path from TypedBaseItems where artists is NULL and IsFolder = 0 and path not like '%Metadata%' ORDER BY Path |
I had a slew of IIS FTP logs to dig through for a work project. I needed unique visitor details, and I needed it from a month of logs. Some of these daily logs were in excess of 40mb. I threw together a PowerShell script to do just so, kicked it off, and waited. And waited. I reconfigured the script to be more efficient in its processing, kicked it off again, and waited. And waited. And researched, because this was taking far too long. And – late to the game, I know – I found Microsoft’s Logparser. Logparser is a free tool, and it’s filled with magic. With one simple query it managed to pull the raw data I needed from the log files in *seconds*.
1 2 3 4 5 | #log files in c:\temp\iis, results to same path logparser "SELECT DISTINCT c-ip, cs-username INTO C:\temp\iis\results.txt FROM 'C:\temp\iis\*.log'" -i:W3C -o:W3C # |
In less than a minute I had a text file of unique visits gleaned from 550 MB worth of plaintext log files. From there I could use PowerShell to filter out any IP dupes (some connections do not have user details, thus producing some leftover duplication) and resolve hostnames from IP addresses to create a final masterlist. I’m not sure how Logparser does what it does so quickly.
Updated query for better detail!
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT a.artists,a.album,a.IndexNumber,a.name,a.path FROM TypedBaseItems a JOIN (SELECT name, album, IndexNumber FROM TypedBaseItems WHERE album IS NOT NULL AND IsFolder = 0 GROUP BY name, album, IndexNumber HAVING COUNT(*) > 1 ) b ON a.name = b.name AND a.album = b.album AND a.IndexNumber = b.IndexNumber ORDER BY a.artists, a.album, a.IndexNumber, a.name |
I run 2 Jellyfin servers – one for video, and one for music. In the world of streaming I’m old school in that I still curate a huge library of audio files. I don’t like being at the whim of streaming services, I don’t like how they treat artists, and I don’t like how I’m limited to artists on their platforms. I listen to a lot of obscure music across nearly all genres, and quite a bit of it can’t be found on *any* streaming service.
Jellyfin isn’t the perfect solution for a music server, but so far it’s the best I’ve found. Admittedly I may be biased due to my familiarity with the product, but every time I come across an alternative I give it a go only to wind up back at Jellyfin. Over the course of decades I’ve wound up with many duplicates in my library and, because often they aren’t identical in name or size, it’s not easy to identify them. The other day, as I saw yet another double listing for an album in Jellyfin, it dawned on me: Jellyfin knows these are dupes. Can I leverage it to my advantage?
Indeed I can.
First you’ll need DB Browser for SQLite. It’s free. Then you’ll want to locate your database file for Jellyfin. I’m running on Windows, and my path is
C:\Users\USERNAME\AppData\Local\Jellyfin\data\library.db
For the purpose of learning, I recommend making a copy of this file elsewhere to tinker with. If you accidentally change data in it, you could sink yourself. Everything happening below is strictly reading data, not manipulating, but still. You’ll have to shut Jellyfin down to access this file in DB Browser. Open it up, head to the Execute SQL tab, slap this query in, and execute it.
1 2 3 4 5 6 7 8 9 10 | SELECT a.artists,a.album,a.name,a.path FROM TypedBaseItems a JOIN (SELECT name, album FROM TypedBaseItems WHERE album IS NOT NULL GROUP BY name, album HAVING COUNT(*) > 1 ) b ON a.name = b.name AND a.album = b.album ORDER BY a.name |
This will return you a comma delimited list of dupes. It’s keying off of song title and album title. Mind you, it’s not perfect. It’s going to find dupes that aren’t here and there, especially on boxed sets and compilations where there might be multiple takes of the same tune. You could obviously add in track number (IndexNumber in the database) and leverage IsFolder to skip directory names (1 is yes, 0 is no), but for my purposes the above gave me accurate enough data to sort in a Google spreadsheet and start hacking away at my duplicates.
I shuttered this site in early 2020 for personal reasons. Having found myself in need of an outlet, and a place to keep random thoughts and information, I decided to revive it.
I recently realized that my left leg is still far weaker than its companion. That I’ve grown accustomed to allowing the right to carry more than its fair share of the burden. During my initial recovery from surgery I did physical therapy and then signed up for and started going to a gym. I continued the gym through the first winter. Trying to exercise in the cold was difficult, and navigating snow and ice seemed treacherous. But as soon as spring arrived I told myself that going out for walks and bike rides was sufficient, and I could skip the gym.
Truthfully, I hate the gym. I don’t hate exercise – I hate the gym. If I could go to the gym and be alone I’d be fine. And I recognize that this isn’t the fault of other gym attendees – this is my anxiety, my hangup. Everyone’s here for the same reason, I tell myself. No one’s watching you. No one cares.
I returned to the gym about a week ago. It was confirmation of how weak my leg is. On many of the machines, my left leg can only handle half – or even less – of the weight that my right can. And it’s not just muscle weakness, though that’s profound. It’s also flexibility. And it’s pain. Doing leg lifts with a mere 30 pounds hurts my knee. It’s not a severe pain, not a pain that’s sufficient to stop me from doing the exercise, but it’s more than enough to acknowledge.
Back in 2016 I wrote one post about my battle with SIBO – Small Intestine Bacterial Overgrowth – and nothing since. Little did I know then that I’d be diagnosed with a bone tumor and life would change in dramatic ways.
At the time of my post I was seeing a nutritionist who had me on a strict diet regimen, and it appeared that it was working. However, my weight continued to drop. I reached 130 pounds. One of our clients, a doc at a local hospital, expressed extreme concern over my condition, and even asked if I’d had an HIV test. My clothes hung on me loosely, my face was sunken, my pallor made people uncomfortable. I looked desperately ill.
My attempts to lead a semi-normal life proved to be dangerous. I had no reserves, my constitution was nil. I’d fall out exerting the slightest of efforts – literally nearly collapsing. I wound up in the hospital after an attempt at a casual mountain bike ride, bonking out and wrecking a mere mile and a half into a trail I used to ride without giving it a second thought.
Another 4 months another x-ray, and another bout of scanxiety as I wait for the disc of images to arrive and be analyzed by my doc on the east coast. The ortho here who diagnosed me took a glance and thinks it looks no different from the last scan, so that’s somewhat of a relief. I never feel full relief until I hear from the experts though.
Well, honestly, I never feel full relief. There’s a reason for recurring x-rays far out into the future.
Let’s talk for a minute about doctors.
I live in a town of approximately 100,000 people in central Oregon. When I was diagnosed with a bone tumor at one of the two orthopedists in the city, I was straight away handed my file and x-rays and referred out to specialists in Portland, Seattle and even Texas, because there’s literally no one in my hometown who is qualified to treat my GCT. I ultimately chose to have it handled at Shands in Florida, for a variety of reasons; I have family working in that organization who helped fast track my case, I have family in Florida who could put us up and assist with recovery and, most important of all, Shands is a learning hospital that has a surgeon on staff who specializes in rare tumors including GCT.
Continue reading
Microsoft’s Onenote is pretty groovy, but it’s missing some really obvious things, like sorting pages. Thankfully you can install the Onetastic addin and then the Sort Pages macro.
I’m a luddite – I love Paint Shop Pro 9. Yeah, I use Illustrator and Photoshop and Inkscape and Gimp and all that goodness but, in a pinch, PSP9’s been my go-to quick and dirty image manipulator forever.
Then I got a fancy new laptop with a fancy new video card… and PSP9 wouldn’t work. It wouldn’t produce a window on launch, even though I could see it there in the task manager, running. And when I uninstalled and tried to reinstall, it hung for infinity on “registering modules.”
Continue reading
