Informes de antiguedad

[Martind]

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

[RCorbeil]

Those results don't include the BIOS information. The last example code block adds it.

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

FROM
  ...
  Inner Join tblBIOS ON tblBIOS.AssetID = tblAssets.AssetID

links in the table containing the BIOS information.

SELECT
  ...
  tblBIOS.ReleaseDate AS [BIOS Date],
  ...

displays the BIOS date.

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.

[Martind]

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?

[RCorbeil]

If you want to turn to the BIOS date instead of the purchase date:

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.

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]

Cual seria el campo "fecha del BIOS" la tabla seria "tblBIOS "?

[Martind]

Cual seria el campo "fecha del BIOS" la tabla seria "tblBIOS "?

[RCorbeil]

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]

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

[RCorbeil]

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.

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.

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

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.

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.

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.

Where
  tblAssetCustom.State = 1
  And tsysAssetTypes.AssetTypename = 'Windows'
  And (   tblAssetCustom.PurchaseDate Is Null
       OR DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25 >= 4
      )

[Martind]

Como hago para realizar un reporte? o donde pongo ese codigo?