Find that Dinosaur in Your Network?? doesn't show Linux machines?

[nekrosoft13]

I have a question about https://www.lansweeper.com/tutorial/find-that-dinosaur-in-your-network/

How can you make this report show Linux machines as well?

[RCorbeil]

From a Ctrl+F of the database documentation, it looks like tblLinuxSystem.OperatingSystem may be what you're after but, as previously noted, I don't have any Linux machines in my inventory so I can't swear to it.

If you want to confirm what you've got recorded, try running a simple dump of the table:

SELECT *
FROM tblLinuxSystem

[nekrosoft13]

Thanks!

Any idea, how to add OS in the linux portion? tsysOS.OSname As OS,

That seems to be missing.

[RCorbeil]

I don't have any Linux machines in my database, so I can't test to make sure it's 100%, but in theory this should work. If it doesn't, strip off the Windows part and adjust the Linux part to produce the expected results, then put the two together with the UNION again.

-- the original Windows-centric query
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  Convert(nVARCHAR(10),tblBIOS.ReleaseDate,101) As [BIOS Date],
  Cast(Round(DateDiff(day, tblBIOS.ReleaseDate, GetDate()) / 365.00, 2) As Numeric(8,2)) As [Years Old],
  tblAssets.Lastseen,
  tblAssets.Lasttried
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where
  tblState.Statename = 'Active'

UNION

-- a copy, modified to pull from tblLinuxBIOS instead of tblBIOS
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  Convert(nVARCHAR(10),tblLinuxBIOS.ReleaseDate,101) As [BIOS Date],
  Cast(Round(DateDiff(day, tblLinuxBIOS.ReleaseDate, GetDate()) / 365.00, 2) As Numeric(8,2)) As [Years Old],
  tblAssets.Lastseen,
  tblAssets.Lasttried
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblLinuxBIOS On tblAssets.AssetID = tblLinuxBIOS.AssetID
Where
  tblState.Statename = 'Active'
Order By
  [Years Old] Desc

[nekrosoft13]

could you show an example? I quite new to SQL.

[RCorbeil]

A UNION should do that for you.

[nekrosoft13]

hmm, would be nice if there was possibility to combine them.

[RCorbeil]

The report you reference draws from tblBIOS, so it's Windows-only. There is a tblLinuxBIOS table in the database, so conceivably you should be able to copy and modify the report to use that to report on your Linux machines.