ADUsers "Feature Request" UniqueID

[stimpy_555]

Hi,

I have a quick question regarding the last login / username field from dbo.tblAssets.UserName

As we are a Multiple Domain environment the username refernce could be the same in 3 domains which means I get duplicate results when creating a new view;

Create VIEW dbo.AssetReport as
Select TOP 100 PERCENT dbo.tblAssets.AssetName
,dbo.tblAssetCustom.Serialnumber As "SerialNumber"
,dbo.tblAssetCustom.Manufacturer as "Vendor"
,dbo.tblAssetCustom.Model
,dbo.tblOperatingSystem.Caption as "OperatingSystem"
,dbo.tblOperatingSystem.ServicePackMajorVersion as "OperatingSystemServicePack"
,dbo.tblAssets.Userdomain
,dbo.tblAssets.Username
,dbo.tblADusers.Firstname
,dbo.tblADusers.Lastname
,dbo.tblADusers.email
,dbo.tblADusers.Department
,dbo.tblADComputers.Location as "WKS_AD_Location"
,dbo.tblADusers.Office
,dbo.tblAssets.IPAddress As "IPAddress"
,dbo.tblADComputers.OU AS "ComputerOU"
,dbo.tblADUsers.OU as "UserOU"
,dbo.tblAssets.Lastseen
,dbo.tblAssets.LastActiveScan
,dbo.tblADComputers.LastChanged

From dbo.tblAssets
Inner Join dbo.tblADComputers on dbo.tblAssets.AssetID = dbo.tblADComputers.AssetID
Inner Join dbo.tblAssetCustom On dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID
Inner Join dbo.tblOperatingSystem On dbo.tblAssets.AssetID = dbo.tblOperatingSystem.AssetID
Left Join dbo.tblADUsers On dbo.tblAssets.Username = dbo.tblADUsers.Username

Order By dbo.tblAssets.AssetName

I need to create a view to select all data into excel for lookup on another AssetRegister, but on some systems I am geeting duplicates which then inturn invalidates the data. Can anyone think of a way to allow for these exceptions within the view or to allow for the "Last" login for a user on the host?

Or is there a table that records the Last user who logged into a pc and keeps only the last value?

[stimpy_555 · Accepted solution]

If anyone is interested in the fixed up lookup which returns the Last logon information for the Users. Remember to update the "WHERE" or "CASE" Statements to suit your reporting requirements except "WHERE Seq=1" as this is the sequence number to get the last logon details;

-------------------------------------------------------------------------------------------------
SELECT DISTINCT
ASSETS.AssetName AS AssetID
,ASCUST.Serialnumber As SerialNumber
,CASE SYSEN.ChassisTypes
when '3' then 'Desktop'
when '4' then 'Desktop'
when '6' then 'Desktop'
when '15' then 'Desktop'
when '7' then 'CAD'
when '8' then 'Laptop'
when '9' then 'Laptop'
when '10' then 'Laptop'
when '1' then 'Virtual'
when '23' then 'Server'
else 'Unknown'
end
AS DeviceType
,ASCUST.Manufacturer as Vendor
,ASCUST.Model
,ASOP.Caption as OperatingSystem
,ASOP.ServicePackMajorVersion as OperatingSystemServicePack
,LogonInfo.Username
,LogonInfo.UserDomain
,LogonInfo.FirstName
,LogonInfo.LastName
,LogonInfo.email
,LogonInfo.Department
,LogonInfo.Office
,LogonInfo.UserOU
,ASSETS.IPAddress
,COMP.OU AS ComputerOU
,ASSETS.Lastseen
,ASSETS.LastActiveScan
,COMP.LastChanged
,LogonInfo.logontime
,DATEDIFF(day,LogonInfo.logontime,GETDATE()) as 'LastLogon(Days)'
From dbo.tblAssets ASSETS
Left Join dbo.tblADComputers COMP on ASSETS.AssetID = COMP.AssetID
Left Join dbo.tblAssetCustom ASCUST On ASSETS.AssetID = ASCUST.AssetID
Left Join dbo.tblOperatingSystem ASOP On ASSETS.AssetID = ASOP.AssetID
Left Join dbo.tblSystemEnclosure SYSEN On ASSETS.AssetID = SYSEN.AssetID
Left Join
(SELECT * FROM
(SELECT AssetID
,dbo.tblCPlogoninfo.logontime
,dbo.tblADUsers.Username
,dbo.tblADUsers.UserDomain
,dbo.tblADUsers.FirstName
,dbo.tblADUsers.LastName
,dbo.tblADUsers.email
,dbo.tblADUsers.Department
,dbo.tblADUsers.Office
,dbo.tblADUsers.ou as UserOU
,ROW_NUMBER() OVER (Partition By dbo.tblCPlogoninfo.AssetId Order by dbo.tblCPlogoninfo.logontime desc) Seq
FROM dbo.tblCPlogoninfo
Inner Join dbo.tblADUsers on (dbo.tblCPlogoninfo.Username = dbo.tblADUsers.Username AND dbo.tblCPlogoninfo.Domain = dbo.tblADUsers.UserDomain)
WHERE dbo.tblCPlogoninfo.Username NOT LIKE '%SVC%'
) AS LogonInfo
WHERE Seq=1
) LogonInfo ON ASSETS.AssetId = LogonInfo.AssetId
WHERE ASSETS.AssetType = -1

-------------------------------------------------------------------------------------------------

Hope this helps some of you out there..

[stimpy_555]

My SQL Statement so far joined together;

Select top 100 percent AssetInfo.AssetName
,AssetInfo.SerialNumber
,CASE AssetInfo.ChassisTypes
when '3' then 'Desktop'
when '4' then 'Desktop'
when '6' then 'Desktop'
when '15' then 'Desktop'
when '7' then 'CAD'
when '8' then 'Laptop'
when '9' then 'Laptop'
when '10' then 'Laptop'
when '1' then 'Virtual'
when '23' then 'Server'
else 'Unknown'
end
AS DeviceType
,AssetInfo.Vendor
,AssetInfo.Model
,AssetInfo.OperatingSystem
,AssetInfo.OperatingSystemServicePack
,AssetInfo.WKS_AD_Location
,AssetInfo.IPAddress
,AssetInfo.ComputerOU
,AssetInfo.Lastseen
,AssetInfo.LastActiveScan
,AssetInfo.LastChanged
,LogonInfo.Username
,LogonInfo.Domain
From
(
SELECT DISTINCT
ASSETS.AssetName
,ASCUST.Serialnumber As SerialNumber
,ASCUST.Manufacturer as Vendor
,ASCUST.Model
,ASOP.Caption as OperatingSystem
,ASOP.ServicePackMajorVersion as OperatingSystemServicePack
,COMP.Location as WKS_AD_Location
,ASSETS.IPAddress As IPAddress
,COMP.OU AS ComputerOU
,ASSETS.Lastseen
,ASSETS.LastActiveScan
,COMP.LastChanged
,SYSEN.ChassisTypes
From dbo.tblAssets ASSETS
Left Join dbo.tblADComputers COMP on ASSETS.AssetID = COMP.AssetID
Left Join dbo.tblAssetCustom ASCUST On ASSETS.AssetID = ASCUST.AssetID
Left Join dbo.tblOperatingSystem ASOP On ASSETS.AssetID = ASOP.AssetID
Left Join dbo.tblSystemEnclosure SYSEN On ASSETS.AssetID = SYSEN.AssetID
Where ASSETS.AssetType = -1
) AS AssetInfo

Left Join
(
SELECT DISTINCT
dbo.tblAssets.AssetName As AssetName
,MAX(dbo.tblCPlogoninfo.logontime) As LastLogon
,dbo.tblCPlogoninfo.Domain
,dbo.tblCPlogoninfo.Username
FROM dbo.tblCPlogoninfo
Inner Join dbo.tblAssets on dbo.tblCPlogoninfo.AssetID = dbo.tblAssets.AssetID
Where dbo.tblCPlogoninfo.Username not like '%SVC%'
GROUP BY dbo.tblAssets.AssetName,dbo.tblCPlogoninfo.Domain,dbo.tblCPlogoninfo.Username
) AS LogonInfo on AssetInfo.Assetname = LogonInfo.Assetname

Order By AssetInfo.AssetName

[stimpy_555]

I still get duplicates on the AssetName when trying to get the MAX Value;

SELECT DISTINCT
dbo.tblAssets.AssetName As AssetName
,MAX(dbo.tblCPlogoninfo.logontime) As LastLogon
,dbo.tblCPlogoninfo.Domain
,dbo.tblCPlogoninfo.Username

FROM dbo.tblCPlogoninfo

Inner Join dbo.tblAssets on dbo.tblCPlogoninfo.AssetID = dbo.tblAssets.AssetID

Where dbo.tblCPlogoninfo.Username not like '%SVC%'

GROUP BY dbo.tblAssets.AssetName,dbo.tblCPlogoninfo.Domain,dbo.tblCPlogoninfo.Username


Any thoughts?

[Hemoco]

Please note that tblADusers must be linked to tblAssets on both the Username and Userdomain fields. If it isn't, you will get invalid data and duplication.

[stimpy_555]

May have answered my own question by Selecting the MAX value from dbo.tblCPlogoninfo with join on AssetID. Will give this a try shortly, but any help would be appreciated.