→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Technut27
Champion Sweeper
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!
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

8 REPLIES 8
RCorbeil
Honored Sweeper II
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
Champion Sweeper
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
Lansweeper Alumni
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
Honored Sweeper II
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
Champion Sweeper
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
Honored Sweeper II
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
Champion Sweeper
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
Honored Sweeper II
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