Cisco Small Business Series Switches Vulnerability Audit

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Description,
Subquery1.Label As OIDLabel,
Subquery1.Data As Version,
Case
when 
(tblAssetCustom.Model Not Like '%Business 250%' or
tblAssetCustom.Model Not Like '%CBS350%' or
tblAssetCustom.Model Not Like '%CBS250%' or
tblAssetCustom.Model Not Like '%Business 350%') and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 4) As bigint) < 2 then 'Out of Date'
when 
(tblAssetCustom.Model Not Like '%Business 250%' or
tblAssetCustom.Model Not Like '%CBS350%' or
tblAssetCustom.Model Not Like '%CBS250%' or
tblAssetCustom.Model Not Like '%Business 350%') and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 4) As bigint) = 2 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 3) As bigint) < 5
then 'Out of Date'
when
(tblAssetCustom.Model Not Like '%Business 250%' or
tblAssetCustom.Model Not Like '%CBS350%' or
tblAssetCustom.Model Not Like '%CBS250%' or
tblAssetCustom.Model Not Like '%Business 350%') and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 4) As bigint) = 2 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 3) As bigint) = 5 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 2) As bigint) < 9
then 'Out of Date'
when
(tblAssetCustom.Model Not Like '%Business 250%' or
tblAssetCustom.Model Not Like '%CBS350%' or
tblAssetCustom.Model Not Like '%CBS250%' or
tblAssetCustom.Model Not Like '%Business 350%') and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 4) As bigint) = 2 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 3) As bigint) = 5 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 2) As bigint) = 9 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 1) As bigint) < 16
then 'Out of Date'
u200b
when 
(tblAssetCustom.Model Like '%Business 250%' or
tblAssetCustom.Model Like '%CBS350%' or
tblAssetCustom.Model Like '%CBS250%' or
tblAssetCustom.Model Like '%Business 350%') and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 4) As bigint) < 3 then 'Out of Date'
when 
(tblAssetCustom.Model Like '%Business 250%' or
tblAssetCustom.Model Like '%CBS350%' or
tblAssetCustom.Model Like '%CBS250%' or
tblAssetCustom.Model Like '%Business 350%') and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 4) As bigint) = 3 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 3) As bigint) < 3
then 'Out of Date'
when
(tblAssetCustom.Model Like '%Business 250%' or
tblAssetCustom.Model Like '%CBS350%' or
tblAssetCustom.Model Like '%CBS250%' or
tblAssetCustom.Model Like '%Business 350%') and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 4) As bigint) = 3 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 3) As bigint) = 3 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 2) As bigint) < 0
then 'Out of Date'
when
(tblAssetCustom.Model Like '%Business 250%' or
tblAssetCustom.Model Like '%CBS350%' or
tblAssetCustom.Model Like '%CBS250%' or
tblAssetCustom.Model Like '%Business 350%') and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 4) As bigint) = 3 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 3) As bigint) = 3 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 2) As bigint) = 0 and
Cast(ParseName(cast(Subquery1.Data as nvarchar), 1) As bigint) < 16
then 'Out of Date'
When Subquery1.Data is NULL then 'No data found.'
else 'Up to Date'
end as [Status],
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select tblOIDData.AssetID,
tblOIDData.Label,
tblOIDData.Data
From tblOIDData
Where tblOIDData.[Key] = '1.3.6.1.4.1.9.6.1.101.2.4.0' And
tblOIDData.Data Not Like '%data%') As Subquery1 On Subquery1.AssetID =
tblAssets.AssetID
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Where tblAssetCustom.Manufacturer Like '%cisco%' and
tblAssetCustom.Model Like '%SF250%' or
tblAssetCustom.Model Like '%SG250%' or
tblAssetCustom.Model Like '%SF350%' or
tblAssetCustom.Model Like '%SF352%' or
tblAssetCustom.Model Like '%SG350%' or
tblAssetCustom.Model Like '%SG355%' or
tblAssetCustom.Model Like '%SG350X%' or
tblAssetCustom.Model Like '%SX350X%' or
tblAssetCustom.Model Like '%SF550X%' or
tblAssetCustom.Model Like '%SG550X%' or
tblAssetCustom.Model Like '%SX550X%' or
tblAssetCustom.Model Like '%Business 250%' or
tblAssetCustom.Model Like '%CBS250%' or
tblAssetCustom.Model Like '%Business 350%' or
tblAssetCustom.Model Like '%CBS350%' or
tblAssetCustom.Model Like '%SF200%' or
tblAssetCustom.Model Like '%SG200%' or
tblAssetCustom.Model Like '%SF300%' or
tblAssetCustom.Model Like '%SF302%' or
tblAssetCustom.Model Like '%SG300%' or
tblAssetCustom.Model Like '%SF500%' or
tblAssetCustom.Model Like '%SG500%'
And tblState.Statename =
'Active'
Order By tblAssetCustom.Model,
tblAssets.IPAddress