cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Wanstor
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
eac
Engaged Sweeper III
...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...

View solution in original post

6 REPLIES 6
Wanstor
Engaged Sweeper II
Hi,

Many thanks this is perfect!! Thanks alot!
eac
Engaged Sweeper III
...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
Engaged Sweeper III
...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
Honored Sweeper II
Excellent! More detail is always good.
RCorbeil
Honored Sweeper II
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
Lansweeper Alumni
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.