→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !
‎01-22-2015 10:21 AM
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 <> ''
‎10-05-2016 05:32 AM
‎09-06-2016 08:42 AM
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
‎10-04-2016 08:29 PM
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
‎09-05-2016 07:54 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now