Audit: Disk Partition Alignment = FALSE

[AdmJLovejoy]

List of hard-disk partitions which are not aligned

Note: This report runs only if the Lansweeper DB is installed on SQL Server Express or higher edition.

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  Cast(Round(tblDiskPartition.Size / 1024 / 1024 / 1024, 2, 0) As DECIMAL(20,1))
  As 'Size (GB)',
  'Disk #' + Cast(tblDiskPartition.DiskIndex As Varchar(5)) + ' : Partition #' +
  Cast(tblDiskPartition.[Index] As Varchar(5)) As DeviceID,
  tblDiskPartition.StartingOffset,
  Case
    When (tblDiskPartition.StartingOffset / 4096 !=
    Round(tblDiskPartition.StartingOffset / 4096, 0)) Then 'FALSE' Else 'TRUE'
  End As 'Is Aligned?',
  tsysOS.Image As icon
From tblDiskPartition
  Inner Join tblAssets On tblDiskPartition.AssetID = tblAssets.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblDiskPartition.StartingOffset / 4096 <>
  Round(tblDiskPartition.StartingOffset / 4096, 0) And
  tblDiskPartition.BootPartition = 0 And Round(tblDiskPartition.Size /
  1024 / 1024 / 1024, 2) > 1) Or
  (tblDiskPartition.StartingOffset / 4096 <>
  Round(tblDiskPartition.StartingOffset / 4096, 0) And
  tblDiskPartition.BootPartition = Null And Round(tblDiskPartition.Size / 1024 /
  1024 / 1024, 2) > 1 And tblAssetCustom.State = 1)
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  'Disk #' + Cast(tblDiskPartition.DiskIndex As Varchar(5)) + ' : Partition #' +
  Cast(tblDiskPartition.[Index] As Varchar(5)),
  tblDiskPartition.StartingOffset,
  tsysOS.Image,
  tblDiskPartition.StartingOffset / 4096,
  tblDiskPartition.Size
Order By tblAssets.AssetID,
  DeviceID

[lunja]

Great report.

Does not show all misaligned partitions as it should.

Following seems to work better:

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  Cast(Round(tblDiskPartition.Size / 1024 / 1024 / 1024, 2, 0) As DECIMAL(20,1))
  As 'Size (GB)',
  'Disk #' + Cast(tblDiskPartition.DiskIndex As Varchar(5)) + ' : Partition #' +
  Cast(tblDiskPartition.[Index] As Varchar(5)) As DeviceID,
  tblDiskPartition.StartingOffset,
  Case
    When (tblDiskPartition.StartingOffset / 4096 !=
    Round(tblDiskPartition.StartingOffset / 4096, 0)) Then 'FALSE' Else 'TRUE'
  End As 'Is Aligned?',
  tsysOS.Image As icon,
  tblAssetCustom.Model
From tblDiskPartition
  Right Join tblAssets On tblDiskPartition.AssetID = tblAssets.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblDiskPartition.StartingOffset / 4096 <>
  Round(tblDiskPartition.StartingOffset / 4096, 0) And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  'Disk #' + Cast(tblDiskPartition.DiskIndex As Varchar(5)) + ' : Partition #' +
  Cast(tblDiskPartition.[Index] As Varchar(5)),
  tblDiskPartition.StartingOffset,
  tsysOS.Image,
  tblAssetCustom.Model,
  tblDiskPartition.StartingOffset / 4096,
  tblDiskPartition.Size
Order By tblAssets.AssetName,
  DeviceID