Cisco date of manufacture

[Filip_V]

SELECT TOP 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 AS icon,
  tblAssets.IPAddress,
  Serialnumber,
  DATEADD(DAY,7*SUBSTRING(Serialnumber,6,2)-7,
  DATEADD(YEAR,96+SUBSTRING(Serialnumber,4,2),0)) 'Date of Manufacture',
  tblAssets.Lastseen,
  tblAssets.Lasttried
FROM tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE tblAssetCustom.State = 1 AND Manufacturer LIKE '%cisco%' AND Serialnumber <> ''

[Mister_Nobody]

You can use original report and sort or filter by year then export to Excel.

[Mister_Nobody]

Quick chart code for Cisco Switches:

Select Top 1000000 DatePart(yyyy, DateAdd(DAY, 7 *
  SubString(tblAssetCustom.Serialnumber, 6, 2) - 7, DateAdd(YEAR, 96 +
  SubString(tblAssetCustom.Serialnumber, 4, 2), 0))) As ManYear,
  Count(tblAssets.AssetID) As Total
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.Serialnumber <> '' And tblAssetCustom.State = 1 And
  tblAssetCustom.Manufacturer Like '%cisco%' And tsysAssetTypes.AssetType = 6
Group By DatePart(yyyy, DateAdd(DAY, 7 * SubString(tblAssetCustom.Serialnumber,
  6, 2) - 7, DateAdd(YEAR, 96 + SubString(tblAssetCustom.Serialnumber,
  4, 2), 0)))
Order By ManYear

[CVannest]

Mister Nobody wrote:
Quick chart code for Cisco Switches:

Select Top 1000000 DatePart(yyyy, DateAdd(DAY, 7 *
  SubString(tblAssetCustom.Serialnumber, 6, 2) - 7, DateAdd(YEAR, 96 +
  SubString(tblAssetCustom.Serialnumber, 4, 2), 0))) As ManYear,
  Count(tblAssets.AssetID) As Total
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.Serialnumber <> '' And tblAssetCustom.State = 1 And
  tblAssetCustom.Manufacturer Like '%cisco%' And tsysAssetTypes.AssetType = 6
Group By DatePart(yyyy, DateAdd(DAY, 7 * SubString(tblAssetCustom.Serialnumber,
  6, 2) - 7, DateAdd(YEAR, 96 + SubString(tblAssetCustom.Serialnumber,
  4, 2), 0)))
Order By ManYear

This is a great report and shows a grouped year for the devices, but I need to drill into those devices and see what's in each group. How can I pull that report?

i.e. 2014 = 647 Cisco Devices (What are those devices)

[MichaelFunk]

I get this when I run it..

"Error: Conversion failed when converting the nvarchar value 'R1' to data type int."