→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Jeff_Henze
Engaged Sweeper III
I’m trying to do an asset change report showing the recently assigned assets (I put info in custom1..custom6). I store the assignee’s employee number in Custom1; but since that’s not the most descriptive information, I do a left-join on the tblAdusers to show me the name (displayname). This causes a small problem: when one employee number has multiple IDs in AD, it creates a duplicate line of assets, one for each ID. I would like it to only show one line, selecting whichever line from AD for the name I don’t really care, but only one. Here’s the report:
Select Top 1000000 tblAssets.AssetName,
tblAssets.Domain,
Cast(tblAssetCustom.Custom4 As datetime) As [Date Assigned],
tblAssetCustom.Serialnumber,
tblAssetCustom.Model,
tblAssets.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom1 As [Assigned Employee #],
tblADusers1.Displayname As [Assigned Employee],
tblAssetCustom.Custom2 As Assignment,
tblAssetCustom.Custom5 As Technician,
tblAssetCustom.Custom6 As PO,
tblAssetCustom.Lastchanged,
tblADusers.EmployeeNumber As [Last On Emp#],
tblADusers.Username As [Last On ID],
tblADusers.Displayname As [Last On Name]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join tblADusers tblADusers1 On tblAssetCustom.Custom1 =
tblADusers1.EmployeeNumber
Where (tblAssetCustom.Custom3 Like 'NoCo' And tblAssetCustom.Custom1 <> ''
And tblAssetCustom.State = 1) Or
(tblAssetCustom.Custom2 <> '') Or
(tblAssetCustom.Custom5 <> '')
Order By tblAssets.Domain,
[Date Assigned] Desc

Here’s an example of the output, showing how it duplicates that last asset twice because an employee with that number has multiple AD accounts – one as “Doc Smith” and one as “Doctor Smith” both having the same employee ID:
AssetName | Domain | Date Assigned | Serialnumber | Model | Lastseen | Company | Assigned Employee # | Assigned Employee
DESKTOP68D01 | nasa | 01/01/2016 | a12bc32 | OptiPlex 790 | 07/25/2016 16:41:08 NoCo | 123456 | Doe, John
LAPTOP10D01 | nasa | 01/01/2016 | 324klnl | OptiPlex 7010 | 08/01/2016 15:16:14 NoCo | 45678 | Smith, Doc
LAPTOP10D01 | nasa | 01/01/2016 | 324klnl | OptiPlex 7010 | 08/01/2016 15:16:14 NoCo | 45678 | Smith, Doctor

I’d like it to only show one entry per asset. If it grabs the whatever is the first assigned employee display name it finds, that would be fine. But I’d rather not have two of the same asset returned each with its unique AD account attached to it.

Any ideas on how to achieve that?
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
I tweaked your left join to return the first top result, but I didn't have a chance to really test it out.

Select Top 1000000 tblAssets.AssetName,
tblAssets.Domain,
Cast(tblAssetCustom.Custom4 As DATETIME) As [Date Assigned],
tblAssetCustom.Serialnumber,
tblAssetCustom.Model,
tblAssets.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom1 As [Assigned Employee #],
T1.Displayname As [Assigned Employee],
tblAssetCustom.Custom2 As Assignment,
tblAssetCustom.Custom5 As Technician,
tblAssetCustom.Custom6 As PO,
tblAssetCustom.Lastchanged,
tblADusers.EmployeeNumber As [Last On Emp#],
tblADusers.Username As [Last On ID],
tblADusers.Displayname As [Last On Name]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join (Select adu.EmployeeID,
Max(adu.Displayname) As Displayname
From tblADusers adu
Group By adu.EmployeeID) T1 On T1.EmployeeID = tblAssetCustom.Custom1
Where (tblAssetCustom.Custom3 Like 'NoCo' And tblAssetCustom.Custom1 <> '' And
tblAssetCustom.State = 1) Or
(tblAssetCustom.Custom2 <> '') Or
(tblAssetCustom.Custom5 <> '')
Order By tblAssets.Domain,
[Date Assigned] Desc

View solution in original post

6 REPLIES 6
Jeff_Henze
Engaged Sweeper III
I forgot about the differences in language (not that I'd recognize them anyway at this point) - I reran it and it ran perfect this time. I noted that my 'where and/or' statement was not working as I hoped so I simplified it. Just in case anyone reads this in the future, the below has your changes and include my simplified where statement.

Thanks for all the help MikeMc!

-Jeff

Select Top 1000000 tblAssets.AssetName,
tblAssets.Domain,
Cast(tblAssetCustom.Custom4 As DATETIME) As [Date Assigned],
tblAssetCustom.Serialnumber,
tblAssetCustom.Model,
tblAssets.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom1 As [Assigned Employee #],
T1.Displayname As [Assigned Employee],
tblAssetCustom.Custom2 As Assignment,
tblAssetCustom.Custom5 As Technician,
tblAssetCustom.Custom6 As PO,
tblAssetCustom.Lastchanged,
tblADusers.EmployeeNumber As [Last On Emp#],
tblADusers.Username As [Last On ID],
tblADusers.Displayname As [Last On Name]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join (Select adu.EmployeeID,
Max(adu.Displayname) As Displayname
From tblADusers adu
Group By adu.EmployeeID) T1 On T1.EmployeeID = tblAssetCustom.Custom1
Where tblAssetCustom.Custom3 Like 'NoCo' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
[Date Assigned] Desc
MikeMc
Champion Sweeper II
Jeff,

The error happens because you are running SQL CE with your Lansweeper DB. The query options available in that version versus full SQL Server are very limited. My updated query should work with CE and give you the result you want.
Jeff_Henze
Engaged Sweeper III
Ran it - I get an error now though
"There was an error parsing the query. [ Token line number = 1,Token line offset = 295,Token in error = Select ]"

This went beyond my SQL skills many revisions ago, so I'm not seeing the error but I'm sure it's incredibly obvious to others. Can you take another look?
MikeMc
Champion Sweeper II
I see the mistake I made in my original query. I didn't link back the employee id correctly. I updated my original query and changed the assigned employee field to a sub-query to retrieve the top display name linked from the original query.
Jeff_Henze
Engaged Sweeper III
<edit>

Thanks - it works *except* that I no longer see any info under "Assigned Employee". I'm trying to figure out why that is the case. All the other stuff seems to work great - no duplicates anymore.
MikeMc
Champion Sweeper II
I tweaked your left join to return the first top result, but I didn't have a chance to really test it out.

Select Top 1000000 tblAssets.AssetName,
tblAssets.Domain,
Cast(tblAssetCustom.Custom4 As DATETIME) As [Date Assigned],
tblAssetCustom.Serialnumber,
tblAssetCustom.Model,
tblAssets.AssetID,
tblAssets.Lastseen,
tblAssetCustom.Custom3 As Company,
tblAssetCustom.Custom1 As [Assigned Employee #],
T1.Displayname As [Assigned Employee],
tblAssetCustom.Custom2 As Assignment,
tblAssetCustom.Custom5 As Technician,
tblAssetCustom.Custom6 As PO,
tblAssetCustom.Lastchanged,
tblADusers.EmployeeNumber As [Last On Emp#],
tblADusers.Username As [Last On ID],
tblADusers.Displayname As [Last On Name]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join (Select adu.EmployeeID,
Max(adu.Displayname) As Displayname
From tblADusers adu
Group By adu.EmployeeID) T1 On T1.EmployeeID = tblAssetCustom.Custom1
Where (tblAssetCustom.Custom3 Like 'NoCo' And tblAssetCustom.Custom1 <> '' And
tblAssetCustom.State = 1) Or
(tblAssetCustom.Custom2 <> '') Or
(tblAssetCustom.Custom5 <> '')
Order By tblAssets.Domain,
[Date Assigned] Desc