Combined report for WIndows and Linux

[jraymond1405]

Hi!


I need to create a report or of available built-in report for windows servers and Linux operating system details with basic information such as IP address, Asset Name,Asset Type, Primary User, Domain, OS Release, Manufacturer, Model, First and Last seen, Processor, Number of Processor, Memory, Serial number and Warranty details.

Currently i can have this kind of report separately so we need something combined for both windows and linux in one report.

Regards,
Raymond

[yayitazale]

Yes, but I don't know how to merge it with the actual table. I tried with a inner join of usernames but it gives me an error. I did it with Excel.

[ProfileNL]

I suggest to take a look at the table tblADusers

[yayitazale]

Is there a way to show the users Active Directory real Name and email?

[MikeMc]

You are free to try the below query, but we don't use Lansweeper to scan Linux assets so the info may be hit or miss especially on the processors.

Select *
From (Select Distinct Top 100000 tblAssets.IPAddress,
    tblAssets.AssetID,
    tblAssets.AssetName,
    tblAssets.Domain,
    tblAssets.Username,
    tblAssets.Userdomain,
    (Case When tblAssets.Assettype = -1 Then tblOperatingsystem.Caption
      Else tblLinuxSystem.OSRelease End) As [OS Release],
    tblAssetCustom.Manufacturer,
    tblAssetCustom.Model,
    tblAssets.Firstseen,
    tblAssets.Lastseen,
    (Case When tblAssets.Assettype = -1 Then (Select Top 1 TP2.Name
      From tblProcessor TP2 Where TP2.AssetID = TP1.AssetID)
      Else (Select Top 1 TLP2.Manufacturer + ' ' + TLP2.Family + ' ' +
        TLP2.CurrentSpeed From tblLinuxProcessors TLP2
      Where TLP2.AssetID = tblAssets.AssetID And TLP2.Status Like '%Enabled%')
    End) As Processor,
    (Case
      When tblAssets.Assettype = -1 Then (Select Count(TP3.WIN32_PROCESSORid)
      From tblProcessor TP3 Where TP3.AssetID = TP1.AssetID)
      Else (Select Count(TLP3.ProcessorID) From tblLinuxProcessors TLP3
      Where TLP3.AssetID = TLP1.AssetID And TLP3.Status Like '%Enabled%')
    End) As ProcessorCount,
    tblAssets.Memory,
    tblAssetCustom.Serialnumber,
    tblAssetCustom.Warrantydate
  From tblAssets
    Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
    Left Join tblOperatingsystem On tblOperatingsystem.AssetID =
      tblAssets.AssetID
    Left Join tblLinuxSystem On tblLinuxSystem.AssetID = tblAssets.AssetID
    Left Join tblProcessor TP1 On TP1.AssetID = tblAssets.AssetID
    Left Join tblLinuxProcessors TLP1 On TLP1.AssetID = tblAssets.AssetID And
      TLP1.Status Like '%Enabled%'
  Where tblAssetCustom.State = 1 And (tblAssets.Assettype = -1 Or
      (tblAssets.Assettype = 11 And Exists(Select tblLinuxSystem.AssetID
        From tblLinuxSystem
        Where tblLinuxSystem.AssetID = tblAssets.AssetID)))) T1
Order By T1.AssetName