→ 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: 
tomscott2340
Engaged Sweeper III
I've been trying to get a report that provides all my Windows servers and all my Red Hat Linux servers in one report..

Here is my "All Windows Servers" that has some custom fields returned..

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Case
When tblADComputers.Description Like '%DC1%' Then 'DC1'
When tblADComputers.Description Like '%DC2%' Then 'DC2'
When tblADComputers.Description Like '%PH1%A/A%' Then 'Phase 1 A/A'
When tblADComputers.Description Like '%PH1%A/M%' Then 'Phase 1 A/M'
When tblADComputers.Description Like '%PH2%A/M%' Then 'Phase 2 A/M'
When tblADComputers.Description Like '%PH2%A/A%' Then 'Phase 2 A/A'
When tblADComputers.Description Like '%PH3%A/M%' Then 'Phase 3 A/M'
When tblADComputers.Description Like '%PH3%A/A%' Then 'Phase 3 A/A'
When tblADComputers.Description Like '%PH4%A/M%' Then 'Phase 4 A/M'
When tblADComputers.Description Like '%PH4%A/A%' Then 'Phase 4 A/A'
When tblADComputers.Description Like '%PH4%M/M%' Then 'Phase 4 M/M'
Else 'Phase UNKNOWN'
End As 'Patching Phase',
tsysIPLocations.IPLocation,
tsysOS.OSname + ' SP' + Convert(VARCHAR,tblAssets.SP) As 'Operating System',
tblAssets.IPAddress,
tblAssetCustom.Custom1 As Purpose,
tblAssetCustom.Custom2 As 'Business Owner',
tblAssetCustom.Custom3 As 'IT Owner'
From tblAssets
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%server%'
Order By tblAssets.AssetName


I tried creating one using the code below but it's not returning my Windows servers, just my Red Hat.. Not sure how to fix.. Thanks

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Domain,
Case
When tblADComputers.Description Like '%DC1%' Then 'DC1'
When tblADComputers.Description Like '%DC2%' Then 'DC2'
When tblADComputers.Description Like '%PH1%A/A%' Then 'Phase 1 A/A'
When tblADComputers.Description Like '%PH1%A/M%' Then 'Phase 1 A/M'
When tblADComputers.Description Like '%PH2%A/M%' Then 'Phase 2 A/M'
When tblADComputers.Description Like '%PH2%A/A%' Then 'Phase 2 A/A'
When tblADComputers.Description Like '%PH3%A/M%' Then 'Phase 3 A/M'
When tblADComputers.Description Like '%PH3%A/A%' Then 'Phase 3 A/A'
When tblADComputers.Description Like '%PH4%A/M%' Then 'Phase 4 A/M'
When tblADComputers.Description Like '%PH4%A/A%' Then 'Phase 4 A/A'
When tblADComputers.Description Like '%PH4%M/M%' Then 'Phase 4 M/M'
Else 'Phase UNKNOWN'
End As 'Patching Phase',
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblLinuxSystem.OSRelease,
tblLinuxSystem.OperatingSystem,
tblOperatingsystem.Caption

From tblAssets
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where OSRelease like '%Red%Hat%' OR tblOperatingsystem.Caption Like '%server%'
1 ACCEPTED SOLUTION
tomscott2340
Engaged Sweeper III
Never mind.. Got it..

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Case
When tblADComputers.Description Like '%DC1%' Then 'DC1'
When tblADComputers.Description Like '%DC2%' Then 'DC2'
When tblADComputers.Description Like '%PH1%A/A%' Then 'Phase 1 A/A'
When tblADComputers.Description Like '%PH1%A/M%' Then 'Phase 1 A/M'
When tblADComputers.Description Like '%PH2%A/M%' Then 'Phase 2 A/M'
When tblADComputers.Description Like '%PH2%A/A%' Then 'Phase 2 A/A'
When tblADComputers.Description Like '%PH3%A/M%' Then 'Phase 3 A/M'
When tblADComputers.Description Like '%PH3%A/A%' Then 'Phase 3 A/A'
When tblADComputers.Description Like '%PH4%A/M%' Then 'Phase 4 A/M'
When tblADComputers.Description Like '%PH4%A/A%' Then 'Phase 4 A/A'
When tblADComputers.Description Like '%PH4%M/M%' Then 'Phase 4 M/M'
Else 'Phase UNKNOWN'
End As 'Patching Phase',
tsysIPLocations.IPLocation,
case when (OSRelease like '%Red%Hat%' OR OSRelease like '%ORACLE%' OR OSRelease like '%Debian%') then tbllinuxsystem.OSRelease
else tsysOS.OSname + ' SP' + Convert(VARCHAR,tblAssets.SP)
end As 'Operating System',
tblAssets.IPAddress,
tblAssetCustom.Custom1 As Purpose,
tblAssetCustom.Custom2 As 'Business Owner',
tblAssetCustom.Custom3 As 'IT Owner'


From tblAssets
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype

left outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where (OSRelease like '%Red%Hat%' OR OSRelease like '%ORACLE%' OR OSRelease like '%Debian%' OR tblOperatingsystem.Caption Like '%server%')
Order By tblAssets.AssetName

View solution in original post

1 REPLY 1
tomscott2340
Engaged Sweeper III
Never mind.. Got it..

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Case
When tblADComputers.Description Like '%DC1%' Then 'DC1'
When tblADComputers.Description Like '%DC2%' Then 'DC2'
When tblADComputers.Description Like '%PH1%A/A%' Then 'Phase 1 A/A'
When tblADComputers.Description Like '%PH1%A/M%' Then 'Phase 1 A/M'
When tblADComputers.Description Like '%PH2%A/M%' Then 'Phase 2 A/M'
When tblADComputers.Description Like '%PH2%A/A%' Then 'Phase 2 A/A'
When tblADComputers.Description Like '%PH3%A/M%' Then 'Phase 3 A/M'
When tblADComputers.Description Like '%PH3%A/A%' Then 'Phase 3 A/A'
When tblADComputers.Description Like '%PH4%A/M%' Then 'Phase 4 A/M'
When tblADComputers.Description Like '%PH4%A/A%' Then 'Phase 4 A/A'
When tblADComputers.Description Like '%PH4%M/M%' Then 'Phase 4 M/M'
Else 'Phase UNKNOWN'
End As 'Patching Phase',
tsysIPLocations.IPLocation,
case when (OSRelease like '%Red%Hat%' OR OSRelease like '%ORACLE%' OR OSRelease like '%Debian%') then tbllinuxsystem.OSRelease
else tsysOS.OSname + ' SP' + Convert(VARCHAR,tblAssets.SP)
end As 'Operating System',
tblAssets.IPAddress,
tblAssetCustom.Custom1 As Purpose,
tblAssetCustom.Custom2 As 'Business Owner',
tblAssetCustom.Custom3 As 'IT Owner'


From tblAssets
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype

left outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where (OSRelease like '%Red%Hat%' OR OSRelease like '%ORACLE%' OR OSRelease like '%Debian%' OR tblOperatingsystem.Caption Like '%server%')
Order By tblAssets.AssetName

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now