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

[jraymond1405]

The query above returns duplicate lines, please check again @yayitazale

[yayitazale]

I don't have all the credentials but someones are OK a some others no. At this report I can't see anyone of them, but I can see windows assets with bad credentials.

[ProfileNL]

Did you set the correct subnet/credentials for the linux assets? Because when I ran the report it just showed the linux assets without any problems

[yayitazale]

There something more: this is not reporting any linux type asset (11)

Any idea?

Thanks

[yayitazale]

BTW: I was getting some error because of a malfuction of the report builder. I reboot the machine and now is working like a charm again.

[yayitazale]

I changed a little bit more to see the IP locations and order it by Ip adress. Also, I changed the Distinct filter to see all the IPs of the DHCP ip ranges (the repeated ones too if they are different assets).

Select Top 100000 tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblADusers.Username,
  tblADusers.Name,
  tblADusers.email,
  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 tblADusers On tblAssets.Username = tblADusers.Username
  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%'
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1 And (tblAssets.Assettype = -1 Or
    (tblAssets.Assettype = 11 And Exists(Select tblLinuxSystem.AssetID
      From tblLinuxSystem Where tblLinuxSystem.AssetID = tblAssets.AssetID)))
Order By tblAssets.IPAddress

[ProfileNL]

Lol yeah, I see I defined Username twice! My fault!
Glad you got it working tho

[yayitazale]

yeah I was trying almost the same code. I changed your code to make it work, becouse you are defining Username 2 times.

Thanks.

Select Top 1000000 *
From (Select Distinct Top 100000 tblAssets.IPAddress,
    tblAssets.AssetID,
    tblAssets.AssetName,
    tblAssets.Domain,
    tblADusers.Username,
    tblADusers.Name,
    tblADusers.email,
    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 tblADusers On tblAssets.Username = tblADusers.Username
    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

[ProfileNL]

Can you try this SQL code?

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,
	tblADUsers.Username,
	tblADUsers.email
  From tblAssets
    Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
    Left Join tblOperatingsystem On tblOperatingsystem.AssetID =
      tblAssets.AssetID
	Left join tblADUsers On tblAssets.Username = tblADUsers.Username
    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