Linux disk space report

[laurentiune]

Is there any way to have a report like: "Disk: Servers less than 1 GB" for Linux servers?

[Susan_A]

Question received and answered via email. For everyone else's benefit, I'm pasting the reply we sent via email below.

The problem is most likely not the report itself, but the data in your Lansweeper database. As mentioned in the forum topic as well, the fields storing Linux disk information are text fields, not numeric fields. This means you cannot apply "less than X amount" filters to the fields. The report we posted on the forum attempts to convert the text field that stores the percentage of free space into a numeric field, e.g. it attempts to convert "10%" to "10". Most likely, there is some data in this field in your particular database that is not uniformly formatted, causing the conversion to fail.

Unfortunately, there is not much we can do about this. At best, you can exclude the faulty data from the report or modify the conversion procedure to suit the data that isn't uniform, but this might not be easy. For a basic report that lists all Linux machines and the percentage of free space on each disk, use the query below. The query below will likely work, but it won't convert the percentages into numeric values so you can apply "less than X amount" filters to them.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblLinuxSystem.OSRelease,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblLinuxHardDisks.Filesystem,
  tblLinuxHardDisks.Size,
  tblLinuxHardDisks.Used,
  tblLinuxHardDisks.Available,
  tblLinuxHardDisks.Percentage As FreePercentage,
  tblLinuxHardDisks.MountedOn,
  tblLinuxHardDisks.LastChanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblLinuxHardDisks On tblAssets.AssetID = tblLinuxHardDisks.AssetID
  Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.IPNumeric,
  tblLinuxHardDisks.Filesystem

[bdowell]

Greetings!

I found this message and was very happy because I was going to be asking for pretty much the exact same report. Unfortunately when I tried to use the code from the original message I am getting an error:

Error: Data conversion failed. [ OLE DB status value (if known) = 2 ]

-

I have the following code:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblLinuxSystem.OSRelease,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblLinuxHardDisks.Filesystem,
tblLinuxHardDisks.Size,
tblLinuxHardDisks.Used,
tblLinuxHardDisks.Available,
100 - Cast(SubString(tblLinuxHardDisks.Percentage, CharIndex('%',
tblLinuxHardDisks.Percentage) - 250, 250) As Int) As FreePercentage,
tblLinuxHardDisks.MountedOn,
tblLinuxHardDisks.LastChanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblLinuxHardDisks On tblAssets.AssetID = tblLinuxHardDisks.AssetID
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where 100 - Cast(SubString(tblLinuxHardDisks.Percentage, CharIndex('%',
tblLinuxHardDisks.Percentage) - 250, 250) As Int) < 10 And
tblAssetCustom.State = 1
Order By tblAssets.IPNumeric,
tblLinuxHardDisks.Filesystem


Any hints on what is wrong?

[Susan_A · Accepted solution]

We received and answered this question via email. Below is a copy of the reply we sent via email, for anyone else interested.

Creating a report that lists available disk space on Linux machines is fairly easy, but only listing drives with less than 1GB of free space is not. The fields storing the size, free space and used space of Linux drives are text fields, not numeric fields, so adding "less than" filters to these fields will give you unexpected results. In addition, there is no specific database field that indicates whether a Linux is a server or a workstation.

We've included a sample report below that lists any Linux disk with less than 10% of free space instead. There is a separate database field that stores the percentage of used space. While this is a text field as well, converting this field to a numeric value is a lot easier. (Be aware that the percentage value may be an approximation, as it is pulled directly from the SSH protocol on the client machine.) You can insert your own percentage value into the highlighted part of the query.

Instructions for adding the report below to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblLinuxSystem.OSRelease,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblLinuxHardDisks.Filesystem,
  tblLinuxHardDisks.Size,
  tblLinuxHardDisks.Used,
  tblLinuxHardDisks.Available,
  100 - Cast(SubString(tblLinuxHardDisks.Percentage, CharIndex('%',
  tblLinuxHardDisks.Percentage) - 250, 250) As Int) As FreePercentage,
  tblLinuxHardDisks.MountedOn,
  tblLinuxHardDisks.LastChanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblLinuxHardDisks On tblAssets.AssetID = tblLinuxHardDisks.AssetID
  Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where 100 - Cast(SubString(tblLinuxHardDisks.Percentage, CharIndex('%',
  tblLinuxHardDisks.Percentage) - 250, 250) As Int) < 10 And
  tblAssetCustom.State = 1
Order By tblAssets.IPNumeric,
  tblLinuxHardDisks.Filesystem