cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Martind
Engaged Sweeper II
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
10 REPLIES 10
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper II
Cual seria el campo "fecha del BIOS" la tabla seria "tblBIOS "?
Martind
Engaged Sweeper II
Cual seria el campo "fecha del BIOS" la tabla seria "tblBIOS "?
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper II
Como hago para realizar un reporte? o donde pongo ese codigo?