Computer Audio Asylum Music servers and other computer based digital audio technologies.

---

[ Follow Ups ] [ Post Followup ] Thread: [ Display  All  Email ] [ Computer Audio Asylum ]

---

[ Alert Moderator ]

---

RE: the Powershell code for inventorying

Posted by LtMandella on October 5, 2020 at 10:10:30

In Reply to: Help, what a mess! posted by LtMandella on September 29, 2020 at 16:03:50:

Below is the powershell code I cobbled together and used to perform my music file inventory.

Any win 10 machine should have the powershell engine and powershell ISE already installed.

You will need to decide how to persist the data. I am calling a function to insert into Sql Server DB but you might need to do something else with the powershell object representing the data for each music file found.

The funcion I am calling to insert into Sql Server is not included in this listing. It is an open source function that is on the net.

So copy and paste into powershell ise and have a look!

Comments, insults, and questions encouraged...

<#
purpose: find music files on all local drives, collect data about the files, and insert into sql server db

inputs : no input parameters yet.

outputs: creates a pshell array of file data and inserts into sql server DB. Also writes some info to console at runtime.

dependancies: 1:powershell 3 or greater.
2:user executing code must have read rights to drive data.
3: Win 10
4: user must have rights to create target table and insert data into local target Sql Server DB

psuedocode: todo :)

todos : 1:run time logging
2:performance enhancements?
3:error handling
4:refactor to seperate paraeterized modules for each logical step

change history:

Date Author Description
____

10/4/2020 LT created

#>
$FoundVolumes=@() ##array for volume details
$physicalresult=@() ##array for physical disk details

## begin loop over all physical disks found on local machine and create and populate array of disk and logical volumes data
$disks=gwmi -computername . -query "SELECT * FROM Win32_DiskDrive"

ForEach($disk in $disks){
$physicalresult+=New-Object PSObject -Property @{
'System'=$disk.SystemName
'Serial'=$disk.SerialNumber.ToString().Trim()
'Name'=$disk.Caption
'Drive'=$disk.DeviceID;
'Partition'=$disk.Index
'Size'=[math]::Truncate($disk.Size / 1GB);
'FreeSpace'='';
} ## new object

##get details on all the partitions found on the physical disk
$partitions=gwmi -computername $server -query "ASSOCIATORS OF {Win32_DiskDrive.DeviceID='$($disk.DeviceID)'} WHERE AssocClass = Win32_DiskDriveToDiskPartition"

ForEach($partition in $partitions){
$logicaldisks=gwmi -computername $server -query "ASSOCIATORS OF {Win32_DiskPartition.DeviceID='$($partition.DeviceID)'} WHERE AssocClass = Win32_LogicalDiskToPartition"

ForEach($logicaldisk in $logicaldisks){
$FoundVolumes+=New-Object PSObject -Property @{
'Label'=$logicaldisk.VolumeName
'System'=$logicaldisk.SystemName
'Serial'=$disk.SerialNumber.ToString().Trim()
'Name'=$disk.Caption
'PartitionIndex'=$logicaldisk.VolumeSerialNumber.ToString()
'Drive'=$disk.DeviceID;
'Partition'=$logicaldisk.Name
'Size'=[math]::Truncate($logicaldisk.Size / 1GB);
'FreeSpace'=[math]::Truncate($logicaldisk.FreeSpace / 1Gb);
} ## new object
} ## for each logical disk
} ##for each partition
} ## end loop over each physical disk

$FoundVolumes | Select System,Serial,Drive,Name,Partition,Label,FreeSpace,Size,PartitionIndex ##| Sort-Object Partition ##| Out-GridView

##now loop over each logical volume finding all the directories and music files and insert location and file data into DB for each music file
foreach($volume in $FoundVolumes){
$filerow=@()
$driveroot = $volume.Partition+'\'
$drivedirs = dir $driveroot -Recurse -Directory -Name

foreach($dname in $drivedirs){
$mypath = $($driveroot+$dname)+ '\*'
$folderLastModifiedDate = Get-Item $($driveroot+$dname) | Foreach {$_.LastWriteTime}
Write-Output $mypath
$filelist = Get-ChildItem -path $mypath -File -Include ('*.dff','*.dsf','*.m4a','*.wav','*.flac','*.aac') ##-ErrorAction SilentlyContinue
foreach($entry in $filelist){
$filerow+=New-Object PSCustomObject -Property @{
'Serial'=$volume.Serial
'Drive' = $volume.Drive
'CurrentDriveLetter'=$volume.Partition
'PartitionIndex'=$volume.PartitionIndex
'VolumeName'=$volume.Name
'FileName'=$entry.Name
'FileLastAccess'=$entry.LastAccessTime
'FileExtension'=$entry.extension
'Directory' = $entry.Directory
'DirectoryName' = $entry.DirectoryName.Substring(2)
'DirectoryLastModifiedDate' = $folderLastModifiedDate
'FileLastModifiedDate' = $entry.LastWriteTime
##'Fullname' = $entry.FullName --redundant and takes up too much space in dB
'BaseName'=$entry.BaseName
'FileSize'=$entry.Length
} ##new object
## do not inventory tiny audio files that are just game, app, or windows sound files
if ($filerow.FileSize -gt 999999){
## if you don't have the Write-ObjectToSQ function, comment out below line
## but then you need to decide what you want to do with each $filerow data item
## maybe for testing just write to console, or write to a text file?
## the sql server data table used about 50mb of disk space for my data- about 80,000 audio files
$filerow | Write-ObjectToSQL -Server 'your sql server' -Database 'FileInventory' -SchemaName 'dbo' -TableName 'f4'
}
$filerow=$null
} ##for each entry
$filelist=$null
} ##for each directory
} ## for each volume

This post is made possible by the generous support of people like you and our sponsors:

VH Audio

---

[ Top of Page ] [ Contact Us ] [ Support/Wish List ] [ Copyright © 2024, Audio Asylum, all rights reserved ]

---

[ General ] [ Speakers ] [ Tubes ] [ Vinyl ] [ Digital ] [ Hi-Rez ] [ Video Asylum ] [ Cables ] [ Tweaks/DIY ] [ Music ] [ Films ]

---