→ 🚀Are you a Lansweeper Champion?! Join our Contributor Program Sign up here!

Community FAQ
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."

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now