Compare values within an array for a unique object

Question

I have a code below which gives the list of SQL server DB's and also lists the location of their mdf and ldf files, i wanted to figure out for a DB if the logicalfilename locations (physical_name) are on the same drive path or not.

For example in the below output i have a dbname "DB1" which has its log and data file locations as DB1 and DB1_log, i wanted to check if the drive letter in which they exist match.

Invoke-Sqlcmd "select d.name dbname,f.name logicalfilename,f.physical_name , CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date] from sys.master_files f, sys.databases d where f.database_id = d.database_id" | Sort-Object "creation date" | ?{$_.physical_name -notmatch "C:"} | `
select @{l='ServerName';e={hostname}},dbname,logicalfilename,physical_name,'creation date', @{l='Drive_Letter';e={$_.physical_name.substring(0,1)}}

ServerName dbname logicalfilename physical_name                Creation date
---------- ------ --------------- -------------                -------------
SQLSNAP1   DB1    DB1_log         G:\Databases_LDF\DB1_log.ldf 14/03/2014 04:11:40
SQLSNAP1   DB1    DB1             F:\Databases_MDF\DB1.mdf     14/03/2014 04:11:40
SQLSNAP1   DB2    DB2             H:\Databases\DB2.mdf         14/03/2014 04:13:12
SQLSNAP1   DB2    DB2_log         I:\Databases\DB2_log.ldf     14/03/2014 04:13:12
SQLSNAP1   DB3    DB3             J:\Databases\DB3.mdf         14/03/2014 04:14:10
SQLSNAP1   DB3    DB3_log         K:\Databases\DB3_log.ldf     14/03/2014 04:14:10
SQLSNAP1   DB4    DB4             F:\Databases_MDF\DB4.mdf     14/03/2014 04:14:45
SQLSNAP1   DB4    DB4_log         F:\Databases_MDF\DB4_log.ldf 14/03/2014 04:14:45
SQLSNAP1   DB5    DB5             G:\Databases_LDF\DB5.mdf     14/03/2014 04:15:27
SQLSNAP1   DB5    DB5_log         G:\Databases_LDF\DB5_log.ldf 14/03/2014 04:15:27
SQLSNAP1   DB6    DB6             H:\Databases\DB6.mdf         14/03/2014 04:16:17
SQLSNAP1   DB6    DB6_log         H:\Databases\DB6_log.ldf     14/03/2014 04:16:17
SQLSNAP1   DB7    DB7             J:\Databases\DB7.mdf         14/03/2014 04:17:00
SQLSNAP1   DB7    DB7_log         J:\Databases\DB7_log.ldf     14/03/2014 04:17:00
SQLSNAP1   DB8    DB8             K:\Databases\DB8.mdf         14/03/2014 04:17:28
SQLSNAP1   DB8    DB8_log         K:\Databases\DB8_log.ldf     14/03/2014 04:17:28

I tried wrapping the output to group object and tries grouping by dbname but need further help or any other approach which can give me the desired results.

Invoke-Sqlcmd "select d.name dbname,f.name logicalfilename,f.physical_name , CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date] from sys.master_files f, sys.databases d where f.database_id = d.database_id" | Sort-Object "creation date" | ?{$_.physical_name -notmatch "C:"} | `
select @{l='ServerName';e={hostname}},dbname,logicalfilename,physical_name,'creation date', @{l='Drive_Letter';e={$_.physical_name.substring(0,1)}} | Group-Object dbname 


Count Name                      Group
----- ----                      -----
    2 DB1                       {@{ServerName=SQLSNAP1; dbname=DB1; logicalfilename=DB1_log; physical_name=G:\Databa...
    2 DB2                       {@{ServerName=SQLSNAP1; dbname=DB2; logicalfilename=DB2; physical_name=H:\Databases\...
    2 DB3                       {@{ServerName=SQLSNAP1; dbname=DB3; logicalfilename=DB3; physical_name=J:\Databases\...
    2 DB4                       {@{ServerName=SQLSNAP1; dbname=DB4; logicalfilename=DB4; physical_name=F:\Databases_...
    2 DB5                       {@{ServerName=SQLSNAP1; dbname=DB5; logicalfilename=DB5; physical_name=G:\Databases_...
    2 DB6                       {@{ServerName=SQLSNAP1; dbname=DB6; logicalfilename=DB6; physical_name=H:\Databases\...
    2 DB7                       {@{ServerName=SQLSNAP1; dbname=DB7; logicalfilename=DB7; physical_name=J:\Databases\...
    2 DB8                       {@{ServerName=SQLSNAP1; dbname=DB8; logicalfilename=DB8; physical_name=K:\Databases\...

Can someone please help.

mjolinor · Accepted Answer · 2014-03-14 17:32:24Z

1

Not tested, but I think this should work:

$DB_Status =
Invoke-Sqlcmd "select d.name dbname,f.name logicalfilename,f.physical_name , CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date] from sys.master_files f, sys.databases d where f.database_id = d.database_id" |
 Sort-Object "creation date" | 
 ?{$_.physical_name -notmatch "C:"} | 
 select @{l='ServerName';e={hostname}},dbname,logicalfilename,physical_name,'creation date', @{l='Drive_Letter';e={$_.physical_name.substring(0,1)}},DriveLetter_MatchResults 

 $ht = @{}

 $DB_Status |
 foreach {$ht[$_.dbname] += @($_.physical_name) }

 $DB_Status |
 foreach { 
  if ( (($ht[$_.dbname] | Split-Path -Qualifier) | Get-Unique).count -eq 1 )
    { $_.DriveLetter_MatchResults = $true }

   else { $_.DriveLetter_MatchResults = $false }
 }

edited Mar 14, 2014 at 17:32

answered Mar 14, 2014 at 17:11

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PowerShell Over a year ago

Hello @mjolinor, awesome it works, but i needed one more help, i need the match and donotmatch output added as one more column named as "DriveLetter_MatchResults" to my initital array output which has the field as ServerName, dbname, logicalfilename, physical_name, Creation date

Collectives™ on Stack Overflow

Compare values within an array for a unique object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related