Create a report showing ubuntu/linux VM assets

[EricCao]

Hi, 

We need to create a report showing all Ubuntu or Linux VMs (we use Hyper-V) and their assigned # of vCPU, RAM and disk volume usage (total vs free).

I was able to create a report for Windows VMs, but for Linux it seems completely different.

I tried below but still no luck. Appreciate your help!!

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblLinuxSystem.OperatingSystem,
  Cast(Round(tblLinuxVolumeGroup.Size / 1024.0, 0) As Int) As
  [Total Disk Space (GB)],
  Cast(Round(tblLinuxVolumeGroup.Free / 1024.0, 0) As Int) As
  [Free Disk Space (GB)]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Inner Join tblLinuxVolumeGroup On
      tblAssets.AssetID = tblLinuxVolumeGroup.AssetID
Where tblAssetCustom.State = 1

[Mister_Nobody]

Try this 

https://community.lansweeper.com/t5/reports-analytics/hyper-v-report-with-cpu-information/m-p/79731#...

[Jacob_H]

Yo Eric -  welcome to the community - soooooo yeah.  The reason why you're getting dupes is because there are multiple records for something that you're JOIN'ing on... in this case, it's the volumes/partitions and the memory - but in general, it could be anything with multiple records such as a NIC/MAC should you query the network tables and get something with two or more NICs.  I digress.  I also removed the memory lookup as you get NULLs since there are no records sometimes, and replaced it with tblassets.memory as that's the tried-and-true.

There's a few ways of tackling this, depending on your needs of your output - the query builder doesn't handle some of the more complex/dynamic SQL queries/methods/commands, so here's a really dirty manual way of making the fields (add more as you need) for the volumes:

Manually Putting 5 volumes to show:

SELECT
    a.AssetID,
    a.AssetName,
    t.AssetTypename,
    t.AssetTypeIcon10 AS icon,
    -- Volume 1
    (SELECT TOP 1 Filesystem
     FROM tblLinuxHardDisks
     WHERE tblLinuxHardDisks.AssetID = a.AssetID
     ORDER BY Filesystem) AS Volume1,
    (SELECT TOP 1 CAST(Size / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks
     WHERE tblLinuxHardDisks.AssetID = a.AssetID
     ORDER BY Filesystem) AS Volume1_TotalMB,
    (SELECT TOP 1 CAST(Available / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks
     WHERE tblLinuxHardDisks.AssetID = a.AssetID
     ORDER BY Filesystem) AS Volume1_FreeMB,
    -- Volume 2
    (SELECT TOP 1 Filesystem
     FROM tblLinuxHardDisks AS t2
     WHERE t2.AssetID = a.AssetID
       AND t2.Filesystem NOT IN (
           SELECT TOP 1 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume2,
    (SELECT TOP 1 CAST(Size / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks AS t2
     WHERE t2.AssetID = a.AssetID
       AND t2.Filesystem NOT IN (
           SELECT TOP 1 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume2_TotalMB,
    (SELECT TOP 1 CAST(Available / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks AS t2
     WHERE t2.AssetID = a.AssetID
       AND t2.Filesystem NOT IN (
           SELECT TOP 1 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume2_FreeMB,
    -- Volume 3
    (SELECT TOP 1 Filesystem
     FROM tblLinuxHardDisks AS t3
     WHERE t3.AssetID = a.AssetID
       AND t3.Filesystem NOT IN (
           SELECT TOP 2 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume3,
    (SELECT TOP 1 CAST(Size / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks AS t3
     WHERE t3.AssetID = a.AssetID
       AND t3.Filesystem NOT IN (
           SELECT TOP 2 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume3_TotalMB,
    (SELECT TOP 1 CAST(Available / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks AS t3
     WHERE t3.AssetID = a.AssetID
       AND t3.Filesystem NOT IN (
           SELECT TOP 2 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume3_FreeMB,
    -- Volume 4
    (SELECT TOP 1 Filesystem
     FROM tblLinuxHardDisks AS t4
     WHERE t4.AssetID = a.AssetID
       AND t4.Filesystem NOT IN (
           SELECT TOP 3 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume4,
    (SELECT TOP 1 CAST(Size / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks AS t4
     WHERE t4.AssetID = a.AssetID
       AND t4.Filesystem NOT IN (
           SELECT TOP 3 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume4_TotalMB,
    (SELECT TOP 1 CAST(Available / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks AS t4
     WHERE t4.AssetID = a.AssetID
       AND t4.Filesystem NOT IN (
           SELECT TOP 3 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume4_FreeMB,
    -- Volume 5
    (SELECT TOP 1 Filesystem
     FROM tblLinuxHardDisks AS t5
     WHERE t5.AssetID = a.AssetID
       AND t5.Filesystem NOT IN (
           SELECT TOP 4 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume5,
    (SELECT TOP 1 CAST(Size / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks AS t5
     WHERE t5.AssetID = a.AssetID
       AND t5.Filesystem NOT IN (
           SELECT TOP 4 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume5_TotalMB,
    (SELECT TOP 1 CAST(Available / 1024 AS NUMERIC)
     FROM tblLinuxHardDisks AS t5
     WHERE t5.AssetID = a.AssetID
       AND t5.Filesystem NOT IN (
           SELECT TOP 4 Filesystem
           FROM tblLinuxHardDisks
           WHERE tblLinuxHardDisks.AssetID = a.AssetID
           ORDER BY Filesystem
       )
     ORDER BY Filesystem) AS Volume5_FreeMB,
    lpp.NumberOfLogicalCores,
    a.Memory,
    a.IPAddress,
    ls.OSRelease,
    a.Lastseen,
    a.Lasttried
FROM tblAssets a
INNER JOIN tblAssetCustom ac ON a.AssetID = ac.AssetID
INNER JOIN tsysAssetTypes t ON t.AssetType = a.AssetType
INNER JOIN tblLinuxPhysicalProcessor lpp ON a.AssetID = lpp.AssetID
INNER JOIN tblLinuxSystem ls ON a.AssetID = ls.AssetID
WHERE ac.State = 1
ORDER BY a.IPNumeric;

The second and dynamic way, but painful if you aren't ingesting the data programmatically, is to concatenate the records into a single field, using the STUFF command.  I used a semicolon as delimiter... so you'd have to parse it back out:

WITH HardDisks AS (
    SELECT
        AssetID,
        STUFF((
            SELECT '; ' + Filesystem
            FROM tblLinuxHardDisks
            WHERE tblLinuxHardDisks.AssetID = hd.AssetID
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Filesystems,
        STUFF((
            SELECT '; ' + CAST(Size / 1024 AS NVARCHAR)
            FROM tblLinuxHardDisks
            WHERE tblLinuxHardDisks.AssetID = hd.AssetID
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS TotalMB,
        STUFF((
            SELECT '; ' + CAST(Used / 1024 AS NVARCHAR)
            FROM tblLinuxHardDisks
            WHERE tblLinuxHardDisks.AssetID = hd.AssetID
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS UsedMB,
        STUFF((
            SELECT '; ' + CAST(Available / 1024 AS NVARCHAR)
            FROM tblLinuxHardDisks
            WHERE tblLinuxHardDisks.AssetID = hd.AssetID
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS FreeMB
    FROM tblLinuxHardDisks hd
    GROUP BY AssetID
)
SELECT TOP 1000000
    a.AssetID,
    a.AssetName,
    t.AssetTypename,
    t.AssetTypeIcon10 AS icon,
    hd.Filesystems,
    hd.TotalMB,
    hd.UsedMB,
    hd.FreeMB,
    lpp.NumberOfLogicalCores,
	a.Memory,
   -- md.MemorySizes,
    a.IPAddress,
    ls.OSRelease,
    a.Lastseen,
    a.Lasttried
FROM tblAssets a
INNER JOIN tblAssetCustom ac ON a.AssetID = ac.AssetID
INNER JOIN tsysAssetTypes t ON t.AssetType = a.AssetType
LEFT JOIN HardDisks hd ON a.AssetID = hd.AssetID
INNER JOIN tblLinuxPhysicalProcessor lpp ON a.AssetID = lpp.AssetID
INNER JOIN tblLinuxSystem ls ON a.AssetID = ls.AssetID
WHERE ac.State = 1
ORDER BY a.IPNumeric;

 The 2nd/dynamic way will break the builder/GUI but will work behind the scenes in SQL Server Management Studio or similar tool.

I hope this helps!

Jacob

[EricCao]

I made some changes it's working but too many duplicates entries:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblLinuxHardDisks.Filesystem,
  Cast(tblLinuxHardDisks.Size / 1024 As numeric) As TotalMB,
  Cast(tblLinuxHardDisks.Used / 1024 As numeric) As UsedMB,
  Cast(tblLinuxHardDisks.Available / 1024 As numeric) As FreeMB,
  tblLinuxHardDisks.LastChanged,
  tblLinuxPhysicalProcessor.NumberOfLogicalCores,
  tblLinuxMemoryDevices.Size,
  tblAssets.IPAddress,
  tblLinuxSystem.OSRelease,
  tblAssets.Lastseen,
  tblAssets.Lasttried
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
  Inner Join tblLinuxPhysicalProcessor On tblAssets.AssetID =
      tblLinuxPhysicalProcessor.AssetID
  Inner Join tblLinuxMemoryDevices On
      tblAssets.AssetID = tblLinuxMemoryDevices.AssetID
Where tblLinuxMemoryDevices.Size Is Not Null And tblAssetCustom.State = 1
Order By tblAssets.IPNumeric,
  tblLinuxHardDisks.Filesystem