→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jraymond1405
Engaged Sweeper II
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
13 REPLIES 13
jraymond1405
Engaged Sweeper II
The query above returns duplicate lines, please check again @yayitazale
yayitazale
Engaged Sweeper III
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
Engaged Sweeper III
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
Engaged Sweeper III
There something more: this is not reporting any linux type asset (11)

Any idea?

Thanks
yayitazale
Engaged Sweeper III
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
Engaged Sweeper III
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
Engaged Sweeper III
Lol yeah, I see I defined Username twice! My fault!
Glad you got it working tho
yayitazale
Engaged Sweeper III
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
Engaged Sweeper III
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