Workstation: Out of warranty reports

[Technut27]

Hello Lansweeper,

Is it possible to create a report similar to the Workstation: Out of warranty in 90 days, that shows me the computers that will be out of warranty before a calendar date, such as 12/31/14, instead of adding days to the current date? I know there are 470 days between now and then but in a week I would have to modify the report to be + 463, etc... This would help with our forecasting.

Thanks!

[RCorbeil]

Exactly what Lansweeper support says. If everything in your database happens to have the same domain recorded then you'll luck out, but you should not assume that to always be true.

When you add tblADUsers in the visual report designer, you'll see little key icons beside both UserName and UserDomain. This tells you that both values are required to uniquely identify a record in the table.

[Technut27]

Thanks, that helped lead me down the right path, this the trick for me:

Inner Join tblADusers On tblAssets.Username = tblADusers.Username

So the entire report looks like this now:

Select Top 1000000 tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Description As [Local Description],
  tblADComputers.Description As [AD Description],
  tblAssets.Username As [Last User],
  tblADusers.Department,
  tblADusers.Title As [Job Title],
  tsysOS.OSname,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tsysOS.Image As icon
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblAssetCustom.Warrantydate < '2015-01-01' And
  (tblComputersystem.Domainrole < 2 Or tblComputersystem.Domainrole > 1) And
  tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc

[Hemoco · Accepted solution]

Technut27 wrote:
So the entire report looks like this now

This report will return incorrect results, as tblAssets/tblADusers are incorrectly linked. These tables must be linked on both the Username and Userdomain fields, as shown below.

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tblAssets.Description As [Local Description],
tblADComputers.Description As [AD Description],
tblAssets.Username As [Last User],
tblADusers.Department,
tblADusers.Title As [Job Title],
tsysOS.OSname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.Image As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADusers On tblAssets.Username = tblADusers.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssetCustom.Warrantydate < '2015-01-01' And
(tblComputersystem.Domainrole < 2 Or tblComputersystem.Domainrole > 1) And
tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc

[RCorbeil]

The pair of fields UserDomain and UserName constitute the unique key to each row in the tblADusers table, so you need to link against both. This should establish the link:

INNER JOIN tblADusers ON tblAssets.Userdomain = tblADusers.Userdomain AND tblAssets.Username = tblADusers.Username

and then you add the fields you want from tblADusers.

I'm trying this out on my database and keep being presented with errors. First
Error: Conversion failed when converting the nvarchar value 'exampledomain' to data type bit
and, after I filter tblAssets.UserDomain and tblAssets.UserName for NULL and '',
Error: Conversion failed when converting the nvarchar value 'exampleusername' to data type int
(where 'exampledomain' and 'exampleusername' represent actual values from my database in the error messages)

Since there are no bit or int data types in the link, I'm at a loss to explain. I tried to link tblADusers against the default Lansweeper report and it worked fine.

SELECT TOP 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblADusers.Name
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblADusers ON tblAssets.Userdomain = tblADusers.Userdomain AND tblAssets.Username = tblADusers.Username
WHERE
 tblAssetCustom.State = 1

I don't have time to troubleshoot right this minute, so I'm going to assume it's just something wonky on my side. You may want to make a copy of your report to try this with, just to be safe, though.

[Technut27]

Thanks for the tip, I don't work in SQL that much. I was asked to add the AD job title to the report as well. How can I add the title from tblADusers for the username returned by tblAssets.Username? I have been experimenting with the query and either end up with 0 results or a million.

[RCorbeil]

Since you say that you want both workstations and servers, you can eliminate the following condition from the WHERE clause

And
(tblComputersystem.Domainrole < 2 Or tblComputersystem.Domainrole > 1)

since it's redundant. SQL Server will likely optimize it out, but why not keep it tidy if you can? <grin>

[Technut27]

Just what I was looking for thanks! I used it almost as is with a few extra tweaks to try and help determine who the assigned user is and department the machine is in. The descriptions are not consistent in my environment. Plus we have workstation class machines with a server OS installed, I wanted to see those too.

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tblAssets.Description As [Local Description],
tblADComputers.Description As [AD Description],
tblAssets.Username As [Last User],
tsysOS.OSname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.Image As icon,
tblADComputers.OU As [AD Department]
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssetCustom.Warrantydate < '2015-01-01' And
(tblComputersystem.Domainrole < 2 Or tblComputersystem.Domainrole > 1) And
tblAssetCustom.State = 1
Order By [Warranty Expiration] Desc

[RCorbeil]

Easily done. Just copy the existing report and modify the date-test condition.

SELECT TOP 1000000
  tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate AS [Purchase Date],
  tblAssetCustom.Warrantydate AS [Warranty Expiration],
  tsysOS.Image AS icon
FROM
  tblAssetCustom
  INNER JOIN tblAssets ON tblAssetCustom.AssetID = tblAssets.AssetID
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
  INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
WHERE
  tblAssetCustom.State = 1
  AND tblComputersystem.Domainrole < 2
  AND tblAssetCustom.Warrantydate < '2015-01-01'
ORDER BY
  [Warranty Expiration] Desc

The three WHERE conditions:
(1) State is active
(2) Domain role is workstation
(3) Warranty expiration date is before 2015