Infrastructure Report

[bobrien]

Hi guys, hoping someone can assist with what I thought would be a pretty straightforward report, but obviously my new-to-sql status is getting in my way.

I'm trying to create a report which contains elements my organization defines as "Infrastructure" -- Windows Servers, Linux systems, Routers, Switches, etc.

I've got the following report...

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress As IP,
tsysIPLocations.IPLocation,
tblAssets.AssetID,
tblAssets.Lastseen,
tsysAssetTypes.AssetType,
tsysOS.OScode,
tsysOS.OSname,
tblOperatingsystem.Caption
From tblAssets
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tsysAssetTypes.AssetType = -1 And tsysOS.OSname = 'Win 2008 R2'
And tblAssetCustom.State = 1) Or
(tsysAssetTypes.AssetType = -1 And tsysOS.OSname = 'Win 2012') Or
(tsysAssetTypes.AssetType = -1 And tsysOS.OSname = 'Win 2008') Or
(tsysAssetTypes.AssetType = 1) Or
(tsysAssetTypes.AssetType = 2) Or
(tsysAssetTypes.AssetType = 3) Or
(tsysAssetTypes.AssetType = 4) Or
(tsysAssetTypes.AssetType = 5) Or
(tsysAssetTypes.AssetType = 6) Or
(tsysAssetTypes.AssetType = 7)
Order By tblAssets.IPNumeric


If I include the "tsysOS.OSname", I JUST get the windows systems in this query. If I remove the tsysOS.OSname, I get all the infrastructure requested in the listed tsysAssetTypes.AssetType entries I specified, but I also get ALL windows versions including client OSes.

How can I structure this to give me Windows Servers plus the other infrastructure items listed here?

(Note: I've abbreviated the full list of asset types I'm looking for -- this was sufficient to illustrate for purposes of this discussion. The full list is 32 discrete Asset Types).

Thanks in advance!!
Brennan

[Hemoco · Accepted solution]

Please use the following report:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress As IP,
tsysIPLocations.IPLocation,
tblAssets.AssetID,
tblAssets.Lastseen,
tsysAssetTypes.AssetType,
tsysOS.OScode,
tsysOS.OSname,
tblOperatingsystem.Caption
From tblAssets
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tsysAssetTypes.AssetType = -1 And tsysOS.OSname = 'Win 2008 R2'
And tblAssetCustom.State = 1) Or
(tsysAssetTypes.AssetType = -1 And tsysOS.OSname = 'Win 2012') Or
(tsysAssetTypes.AssetType = -1 And tsysOS.OSname = 'Win 2008') Or
(tsysAssetTypes.AssetType = 1) Or
(tsysAssetTypes.AssetType = 2) Or
(tsysAssetTypes.AssetType = 3) Or
(tsysAssetTypes.AssetType = 4) Or
(tsysAssetTypes.AssetType = 5) Or
(tsysAssetTypes.AssetType = 6) Or
(tsysAssetTypes.AssetType = 7)
Order By tblAssets.IPNumeric


To use the report above, do the following:
• Open the report builder under Reports/Create New Report.
• Paste the SQL code we provided at the bottom of the page.
• Left-click somewhere in the upper section of the page so the code applies.
• Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.