cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Filip_V
Lansweeper Alumni
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 <> ''
4 REPLIES 4
Mister_Nobody
Honored Sweeper II
You can use original report and sort or filter by year then export to Excel.
Mister_Nobody
Honored Sweeper II
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
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
Engaged Sweeper II
I get this when I run it..

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

New to Lansweeper?

Try Lansweeper For Free

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

Try Now