Notification

Icon
Error

Adding more info to asset report

Posted: Monday, June 10, 2019 1:56:13 PM(UTC)
Lance

Lance

Member Original PosterPosts: 5
0
Like
Hi,

I've seen following report and in need of adding more info to it. I'm trying to add Fist name, Last Name and email address attached to username.

I need to cross reference with other sheet but having just username from this report greatly increases time to match with other sheet.

Below is current report

Thank you!

Code:
Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblOperatingsystem.Caption As [Operating System],
  tblAssets.SP As SP,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tsysOS.OSname = 'Win XP' Then '#ffadad'
    When tsysOS.OSname = 'Win 7' And tblAssets.SP <> 1 Then '#ffdb8e'
    When tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then '#ffbe38'
    When GetDate() > '12/14/2019' And GetDate() < '01/14/2020' And
      tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then '#ff8b00'
    When GetDate() > '01/14/2020' And tsysOS.OSname = 'Win 7' And
      tblAssets.SP = 1 Then '#ffadad'
    Else '#d4f4be'
  End As backgroundcolor,
  Case
    When tsysOS.OSname = 'Win 7' And tblAssets.SP = 1 Then DateDiff(dd,
      GetDate(), '01/14/2020')
  End As DaysRemaining
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName
RC62N
#1RC62N Member Posts: 374  
posted: 6/13/2019 9:46:31 PM(UTC)
Code:
LEFT JOIN tblADusers ON tblADUsers.Userdomain = tblAssets.Userdomain
                    AND tblADusers.Username = tblAssets.Username

Once tblADUsers is linked in, you should have access to the first & last names, email address, etc. assuming that information is populated in your AD.

Active Discussions

Lansweeper Remove all users from old domain
by  cmuter   Go to last post Go to first unread
Last post: 9/20/2019 8:03:58 PM(UTC)
Lansweeper Static IP Address
by  cycleheat  
Go to last post Go to first unread
Last post: 9/20/2019 4:07:16 PM(UTC)
Lansweeper Bitlocker Encryption Recovery Key no information found
by  Stephane   Go to last post Go to first unread
Last post: 9/20/2019 2:26:19 PM(UTC)
Lansweeper InTune Scanning Issues
by  Esben.D  
Go to last post Go to first unread
Last post: 9/20/2019 12:34:59 PM(UTC)
Lansweeper Office 365 scanning issues
by  Esben.D   Go to last post Go to first unread
Last post: 9/20/2019 12:23:30 PM(UTC)
Lansweeper Hard Drive Tracking - Start to Finish
by  Lone Jedi  
Go to last post Go to first unread
Last post: 9/19/2019 8:11:56 PM(UTC)
Lansweeper Lansweeper Reporting Old Assets as New
by  Jpatterson   Go to last post Go to first unread
Last post: 9/19/2019 12:52:14 PM(UTC)
Lansweeper Cannot edit a ticket anymore
by  Esben.D  
Go to last post Go to first unread
Last post: 9/19/2019 12:32:42 PM(UTC)