Custom script to list the number of physical disks a machine has and its size

[Wanstor]

Hi,

I was wondering if i could have some help with a custom script that lists the number of Physical disk a system has (not partition), but physical, the size, and serial.

I would greatly appreciate some assitance with this, i currently use the latest version of lansweeper enterprise.

Thanks

[Wanstor]

Hi,

Many thanks this is perfect!! Thanks alot!

[eac]

...or like this:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblFloppy.InterfaceType,
  tblFloppy.Model,
  tblFloppy.Name,
  tblFloppy.Partitions,
  Case
    When tblFloppy.Size > 1099511627776 Then Ceiling(tblFloppy.Size /
    1099511627776) Else Case
      When tblFloppy.Size > 1073741824 Then Ceiling(tblFloppy.Size / 1073741824)
      Else Case
        When tblFloppy.Size > 1048576 Then Ceiling(tblFloppy.Size / 1048576)
        Else Case When tblFloppy.Size > 1024 Then Ceiling(tblFloppy.Size / 1024)
          Else Ceiling(tblFloppy.Size) End End End End As Capacity,
  Case When tblFloppy.Size > 1099511627776 Then 'TB' Else Case
      When tblFloppy.Size > 1073741824 Then 'GB' Else Case
        When tblFloppy.Size > 1048576 Then 'MB' Else Case
          When tblFloppy.Size > 1024 Then 'KB' Else Case
            When IsNull(tblFloppy.Size, 0) <> 0 Then 'B' Else Null End End End
    End End As Unit,
  tblFloppy.Lastchanged
From tblAssets
  Inner Join tblFloppy On tblFloppy.AssetID = tblAssets.AssetID
Order By tblAssets.AssetUnique,
  tblFloppy.Name

...to be able to filter the unit and to sort for real the disks size.

[eac · Accepted solution]

...or you can put the query like this:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblFloppy.InterfaceType,
  tblFloppy.Model,
  tblFloppy.Name,
  tblFloppy.Partitions,
  Case
    When tblFloppy.Size > 1099511627776 Then Cast(Ceiling(tblFloppy.Size /
    1099511627776) As nVarChar(50)) + ' TB' Else Case
      When tblFloppy.Size > 1073741824 Then Cast(Ceiling(tblFloppy.Size /
      1073741824) As nVarChar(50)) + ' GB' Else Case
        When tblFloppy.Size > 1048576 Then Cast(Ceiling(tblFloppy.Size /
        1048576) As nVarChar(50)) + ' MB' Else Case
          When tblFloppy.Size > 1024 Then Cast(Ceiling(tblFloppy.Size /
          1024) As nVarChar(50)) + ' KB'
          Else Cast(Ceiling(tblFloppy.Size) As nVarChar(50)) + ' B' End End End
  End As Capacity,
  tblFloppy.Lastchanged
From tblAssets
  Inner Join tblFloppy On tblFloppy.AssetID = tblAssets.AssetID
Order By tblAssets.AssetUnique,
  tblFloppy.Name

to have the size formated as MB, GB, TB...

[RCorbeil]

Excellent! More detail is always good.

[RCorbeil]

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblFloppy.InterfaceType,
  tblFloppy.Model,
  tblFloppy.Name,
  tblFloppy.Partitions,
  Ceiling(tblFloppy.Size / 1000 / 1000 / 1000) As [Capacity (GB)],
  tblFloppy.Lastchanged
From
  tblAssets
  Inner Join tblFloppy On tblFloppy.AssetID = tblAssets.AssetID
Order By
  tblAssets.AssetUnique,
  tblFloppy.Name

A drive's serial number is not stored in the drive details table (tblFloppy). You can find other details there, though, like reported cylinders, heads, sectors, tracks, sector size and such.

[Hemoco]

RC62N wrote:
A drive's serial number is not stored in the drive details table (tblFloppy).

This field will be added in Lansweeper 5.1 by the way.