Notification

Icon
Error

Informes de antiguedad - antiguedad de equipamiento tecnologico

Posted: Wednesday, August 7, 2019 7:25:40 PM(UTC)
Martind

Martind

Member Original PosterPosts: 11
0
Like
Buenas tardes, necesito poder sacar un reporte donde me indique el año del equipamiento para poder sacar la antigüedad de los mismos, esto es posible? Gracias
RC62N
#1RC62N Member Posts: 361  
posted: 8/7/2019 8:20:13 PM(UTC)
I'm working from a Google translation of the question.
Quote:
Good afternoon, I need to be able to get a report indicating the year of the equipment to be able to take out the age of the same, is this possible? Thank you


LANSweeper automatically retrieves purchase and warranty dates from several equipment manufacturers, storing the result in tblAssetCustom. If you have equipment for which the data isn't automatically retrieved, you can fill it in manually by editing the assets.

If there are too many to manually edit, you could consider using the BIOS date as a less-accurate date from which to calculate ages. Windows BIOS information is recorded in tblBIOS and Linux information should be in tblLinuxBIOS. (My network inventory is Windows-only, so I can't confirm the Linux data.)

Worst case, you could refer to the asset's first-seen date.

Once you determine what you want to use as your start date, DateDiff() can be used to determine the age, e.g.
Code:
DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate())/365.25

If you only want to see or work with the year from a date, use the DatePart() function, e.g.
Code:
DatePart(yyyy, tblAssetCustom.PurchaseDate)
Martind
#2Martind Member Original PosterPosts: 11  
posted: 8/8/2019 5:50:30 PM(UTC)
Como hago para realizar un reporte? o donde pongo ese codigo?
RC62N
#3RC62N Member Posts: 361  
posted: 8/8/2019 6:55:42 PM(UTC)
In LANSweeper, there's a Reports menu. On that menu is an option to Create new report. (If the program is localized, the phrasing may be different, of course.)

LANSweeper "reports" are SQL queries. If you are unfamiliar with SQL, it's worth spending some time to learn the basics.

When you "Create new report", you always start with a base query. You can build on it or delete it and start your own from scratch.
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  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

You can use the mouse to select fields to add to the report (e.g. PurchaseDate from the tblAssetCustom table) or you can manually edit the query.
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.PurchaseDate,
  DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25 As Age
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
  tblAssetCustom.State = 1

If you only want to see machines older than a specific age, e.g. 4 years, you can add a condition after the WHERE. The base condition
Code:
Where
  tblAssetCustom.State = 1
says "where the asset is active" (i.e. it has been seen by LANSweeper within the last 90 days).

You can expand that with the age condition.
Code:
Where
  tblAssetCustom.State = 1
  And DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25 >= 4

If you have assets for which there is no purchase date recorded, you may want to include those too for further examination.
Code:
Where
  tblAssetCustom.State = 1
  And (   tblAssetCustom.PurchaseDate Is Null
       OR DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25 >= 4
      )

If you're only interested in seeing your Windows computers, you can add a filter for that to the WHERE clause.
Code:
Where
  tblAssetCustom.State = 1
  And tsysAssetTypes.AssetTypename = 'Windows'
  And (   tblAssetCustom.PurchaseDate Is Null
       OR DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25 >= 4
      )
Martind
#4Martind Member Original PosterPosts: 11  
posted: 8/8/2019 7:52:35 PM(UTC)
Estoy viendo que en el reporte las PC que son de marca (HP, IBM, etc) me trae la fecha de compra y el vencimiento de la garantia, esto no es posible de aplicar a las PC "clones"? gracias
RC62N
#5RC62N Member Posts: 361  
posted: 8/8/2019 8:25:07 PM(UTC)
No, you won't see the purchase & warranty date automatically filled in for clone PCs. Those you will need to fill in manually.

The big-name brands -- Dell, HP, etc. -- provide a means for automated querying of their data, so LANSweeper does that for us, to our benefit. For smaller brands, we have to do the work ourselves. Alternatively, you can use something less accurate, like the BIOS date.
Martind
#6Martind Member Original PosterPosts: 11  
posted: 8/8/2019 8:46:09 PM(UTC)
Cual seria el campo "fecha del BIOS" la tabla seria "tblBIOS "?
Martind
#7Martind Member Original PosterPosts: 11  
posted: 8/8/2019 8:57:58 PM(UTC)
Cual seria el campo "fecha del BIOS" la tabla seria "tblBIOS "?
RC62N
#8RC62N Member Posts: 361  
posted: 8/8/2019 9:30:20 PM(UTC)
If you want to turn to the BIOS date instead of the purchase date:
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblBIOS.ReleaseDate AS [BIOS Date],
  DateDiff(dd, tblBIOS.ReleaseDate, GetDate()) / 365.25 As [BIOS Age]
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblBIOS ON tblBIOS.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1

If you want to pick the best option -- use the purchase date if available, otherwise use the BIOS date as better than nothing -- you could do that, too.
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.PurchaseDate,
  tblBIOS.ReleaseDate AS [BIOS Date],
  CASE
    WHEN tblAssetCustom.PurchaseDate IS NOT Null
    THEN DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25
    ELSE DateDiff(dd, tblBIOS.ReleaseDate, GetDate()) / 365.25
  END As [Age]
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblBIOS ON tblBIOS.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1

Note that the BIOS date, while better than nothing, won't be too useful if you've updated the BIOS since purchase. As an example, I'm looking at a couple of computers in my inventory that were purchased in 2008 but their BIOSes were updated within the last couple of years.
Martind
#9Martind Member Original PosterPosts: 11  
posted: 8/9/2019 2:01:11 PM(UTC)
El problema esta en que la fecha que me trae no es la del bios, sino la del ultimo escaneo del bios que le hizo al equipo, puede ser?

UserPostedImage
RC62N
#10RC62N Member Posts: 361  
posted: 8/9/2019 3:55:07 PM(UTC)
Those results don't include the BIOS information. The last example code block adds it.
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.PurchaseDate,
  tblBIOS.ReleaseDate AS [BIOS Date],
  CASE
    WHEN tblAssetCustom.PurchaseDate IS NOT Null
    THEN DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25
    ELSE DateDiff(dd, tblBIOS.ReleaseDate, GetDate()) / 365.25
  END As [Age]
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblBIOS ON tblBIOS.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1


Code:
FROM
  ...
  Inner Join tblBIOS ON tblBIOS.AssetID = tblAssets.AssetID
links in the table containing the BIOS information.

Code:
SELECT
  ...
  tblBIOS.ReleaseDate AS [BIOS Date],
  ...
displays the BIOS date.

Code:
SELECT
  ...
  CASE
    WHEN tblAssetCustom.PurchaseDate IS NOT Null
    THEN DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25
    ELSE DateDiff(dd, tblBIOS.ReleaseDate, GetDate()) / 365.25
  END As [Age]
  ...
uses either the purchase date, if available (PurchaseDate IS NOT NULL), or the BIOS date if the purchase date is not available, to calculate the age in years.

Active Discussions

Lansweeper Email attachments
by  krejar   Go to last post Go to first unread
Last post: 8/15/2019 10:15:07 PM(UTC)
Lansweeper HP Printer not showing correctly
by  Fathom  
Go to last post Go to first unread
Last post: 8/15/2019 4:14:55 PM(UTC)
Lansweeper Install LsAgent on Mac
by  Jonnym   Go to last post Go to first unread
Last post: 8/14/2019 10:14:08 AM(UTC)
Lansweeper Asset State Question
by  rtaylor  
Go to last post Go to first unread
Last post: 8/13/2019 5:53:24 PM(UTC)
Lansweeper LSAgent Version
by  roberto.m  
Go to last post Go to first unread
Last post: 8/13/2019 3:33:50 PM(UTC)
Lansweeper Lansweeper and snmp v3
by  Justin5135   Go to last post Go to first unread
Last post: 8/12/2019 7:07:22 PM(UTC)
Lansweeper Hikvision CCTV Cameras SNMP scan
by  thejduk  
Go to last post Go to first unread
Last post: 8/12/2019 2:34:05 PM(UTC)