Last logon on description is real?

[Alexey]

Hi!
Can I specify the "last logon" variable of the user in the description of a specific computer?

For example, I have a "pc-20-18" computer with AssetID = 516. The last logged in user on it is always the same, because this is his personal corporate PC. Can I use a variable or key for this asset in the description field?

[Alexey]

Hi prighi61,
Understood you, thanks! How wrong I was thinking that this is something not complicated 😞 As a solution by using a trigger, I cannot solve this on my own, given your latest versions of scripts. So I will look for other options for now.

[Alexey]

Thanks a lot again!!!
I'll try it soon, as soon as I have free time again 🙂

[Alexey]

Hey again! Checked the trigger. To my surprise, it was executed even through a report inside the LanSwepper web interface! 🙂
But now I realized that I would like to display slightly different information, namely the "display name", and not "username". As I understand it, I need to use the "web40repADusernodisplayname" table for these purposes? Because it contains the desired "display name" of the AD user.

In general, I imagine it something like this, but I just do not know how to link these two tables correctly for this trigger:

CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;
Update [tblAssets]
Set [Description] = [Description] + ' used by ' + Display name
where Userdomain is not null and Username is not null and Description not like '% used by ' + Display name + '%'
END

May I ask for help fixing this trigger again?

[prighi61]

Hi Alexey T, you can try this:

CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;

DECLARE @Displayname nvarchar(301), @deletedUserdomain nvarchar(150), @deletedUsername nvarchar(150), @insertedUserdomain nvarchar(150), @insertedUsername nvarchar(150);

SELECT @deletedUserdomain = Userdomain, @deletedUsername = Username from deleted;

SELECT @insertedUserdomain = Userdomain, @insertedUsername = Username from inserted;

if @insertedUserdomain is not null and @insertedUsername is not null and ( @insertedUserdomain <> @deletedUserdomain or @insertedUsername <> @deletedUsername )

SELECT @Displayname = Displayname from web40repADusernodisplayname, inserted where web40repADusernodisplayname.Userdomain = inserted.Userdomain and web40repADusernodisplayname.Username = inserted.Username;

Update [tblAssets]
Set [Description] = [Description] + ' used by ' + @Displayname
where Description not like '% used by ' + @Displayname + '%'
END

[prighi61]

prighi61 wrote:
Hi Alexey T, you can try this:

CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;

DECLARE @Displayname nvarchar(301), @deletedUserdomain nvarchar(150), @deletedUsername nvarchar(150), @insertedUserdomain nvarchar(150), @insertedUsername nvarchar(150);

SELECT @deletedUserdomain = Userdomain, @deletedUsername = Username from deleted;

SELECT @insertedUserdomain = Userdomain, @insertedUsername = Username from inserted;

if @insertedUserdomain is not null and @insertedUsername is not null and ( @insertedUserdomain <> @deletedUserdomain or @insertedUsername <> @deletedUsername )

SELECT @Displayname = Displayname from web40repADusernodisplayname, inserted where web40repADusernodisplayname.Userdomain = inserted.Userdomain and web40repADusernodisplayname.Username = inserted.Username;

Update [tblAssets]
Set [Description] = [Description] + ' used by ' + @Displayname
where Description not like '% used by ' + @Displayname + '%'
END

Please replace underlined statement with:
SELECT @Displayname = Displayname from tblADusers, inserted where tblADusers.Userdomain = inserted.Userdomain and tblADusers.Username = inserted.Username;

[Alexey]

prighi61 wrote:

prighi61 wrote:
Hi Alexey T, you can try this:

CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;

DECLARE @Displayname nvarchar(301), @deletedUserdomain nvarchar(150), @deletedUsername nvarchar(150), @insertedUserdomain nvarchar(150), @insertedUsername nvarchar(150);

SELECT @deletedUserdomain = Userdomain, @deletedUsername = Username from deleted;

SELECT @insertedUserdomain = Userdomain, @insertedUsername = Username from inserted;

if @insertedUserdomain is not null and @insertedUsername is not null and ( @insertedUserdomain <> @deletedUserdomain or @insertedUsername <> @deletedUsername )

SELECT @Displayname = Displayname from web40repADusernodisplayname, inserted where web40repADusernodisplayname.Userdomain = inserted.Userdomain and web40repADusernodisplayname.Username = inserted.Username;

Update [tblAssets]
Set [Description] = [Description] + ' used by ' + @Displayname
where Description not like '% used by ' + @Displayname + '%'
END

Please replace underlined statement with:
SELECT @Displayname = Displayname from tblADusers, inserted where tblADusers.Userdomain = inserted.Userdomain and tblADusers.Username = inserted.Username;

Good day! 🙂
Tested your script today. For some reason, he does not always enter the display name. Therefore, the question arises why in the final version you suggested replacing the "web40repADusernodisplayname" table with "tblADusers"?

[prighi61]

Hi Alexey,
the web40repADusernodisplayname only filters users without Displayname, as you can see looking at its definition below:

CREATE VIEW [dbo].[web40repADusernodisplayname]
AS
SELECT TOP 1000000
Userdomain + '\' + Username AS Displayname,
Username,
Userdomain,
Name,
'usersm.png' AS icon,
IsEnabled AS EnabledInAD,
Lastchanged
FROM tblADusers
WHERE (Displayname LIKE '') OR (Displayname IS NULL)
ORDER BY Userdomain + '\' + Username

You need instead merging two selects (with a union) one providing the tblADusers.Displayname when it is not null or empty and the other the opposite case.

However, instead of using a union you can check whether @Displayname is null or empty and set it via another select.

CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;

DECLARE @Displayname nvarchar(301), @deletedUserdomain nvarchar(150), @deletedUsername nvarchar(150), @insertedUserdomain nvarchar(150), @insertedUsername nvarchar(150);

SELECT @deletedUserdomain = Userdomain, @deletedUsername = Username from deleted;

SELECT @insertedUserdomain = Userdomain, @insertedUsername = Username from inserted;

if @insertedUserdomain is not null and @insertedUsername is not null and ( @insertedUserdomain <> @deletedUserdomain or @insertedUsername <> @deletedUsername )
BEGIN

SELECT @Displayname = Displayname from tblADusers, inserted where tblADusers.Userdomain = inserted.Userdomain and tblADusers.Username = inserted.Username;

if @Displayname is null or @Displayname = ''
SELECT @Displayname = Userdomain + '\' + Username from inserted;

Update [tblAssets]
Set [Description] = [Description] + ' used by ' + @Displayname
where Description not like '% used by ' + @Displayname + '%'
END
END

[Alexey]

prighi61 wrote:

...
However, instead of using a union you can check whether @Displayname is null or empty and set it via another select.

CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;

DECLARE @Displayname nvarchar(301), @deletedUserdomain nvarchar(150), @deletedUsername nvarchar(150), @insertedUserdomain nvarchar(150), @insertedUsername nvarchar(150);

SELECT @deletedUserdomain = Userdomain, @deletedUsername = Username from deleted;

SELECT @insertedUserdomain = Userdomain, @insertedUsername = Username from inserted;

if @insertedUserdomain is not null and @insertedUsername is not null and ( @insertedUserdomain <> @deletedUserdomain or @insertedUsername <> @deletedUsername )
BEGIN

SELECT @Displayname = Displayname from tblADusers, inserted where tblADusers.Userdomain = inserted.Userdomain and tblADusers.Username = inserted.Username;

if @Displayname is null or @Displayname = ''
SELECT @Displayname = Userdomain + '\' + Username from inserted;

Update [tblAssets]
Set [Description] = [Description] + ' used by ' + @Displayname
where Description not like '% used by ' + @Displayname + '%'
END

Hi again! This option doesn't seem to work at all 🙂

[prighi61]

Hi Alexey,

a trigger is designed to work on a "variation" event. It can't set the initial state of your tables. I tested mytrigger and found out only a mistake: the update affected too many rows, so a condition should be added:

Update [tblAssets]
Set [Description] = [Description] + ' used by ' + @Displayname
where Description not like '% used by ' + @Displayname + '%' and [tblAssets].Userdomain = @insertedUserdomain and [tblAssets].Username = @insertedUsername;

To test it try changing a username (maybe using the Edit feature of SSMS).

To, instead, set the initial values in the Description field you need to design a script to be executed only once, like the following (I haven't now enough time to test and debug it):

Update [tblAssets]
Set [Description] = [Description] + ' used by ' + (Select Displayname from tblADusers where tblADusers.Userdomain = [tblAssets].Userdomain and tblADusers.Username = [tblAssets].Username and
Displayname is not null and Displayname <> ''
union
Select tblADusers.Userdomain + '\' + tblADusers.Username from tblADusers where tblADusers.Userdomain = [tblAssets].Userdomain and tblADusers.Username = [tblAssets].Username and
not (Displayname is not null and Displayname <> '' ))
where [tblAssets].Userdomain is not null and [tblAssets].Userdomain <> '' and [tblAssets].Username is not null and [tblAssets].Username <> ''