Notification

Icon
Error

Last logon on description is real? - need variable or key

Posted: Tuesday, October 5, 2021 2:04:50 PM(UTC)
Alexey T

Alexey T

Member Original PosterPosts: 12
0
Like
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?
prighi61
#1prighi61 Member Posts: 41  
posted: 10/15/2021 10:51:56 AM(UTC)
Hi Alexey, I think you need to define a trigger on the tblassets to achieve that.
Alexey T
#2Alexey T Member Original PosterPosts: 12  
posted: 10/27/2021 1:52:09 PM(UTC)
Hi, prighi61!
Thx for answer.
What trigger? Where and how to set it up? Have an article?
prighi61
#3prighi61 Member Posts: 41  
posted: 10/27/2021 2:23:01 PM(UTC)
Hi Alexey, you can google for "sql server create trigger after update" to search for trigger examples. I don't think the Last Logon field gets filled during the first insertion (in case you should add the insert clause).
You should check whether the "Last Logon" contents exists and it has not already been added to the Description (so to don't add it multiple times).
An update script should be something like:

Update [tblAssets]
Set [Description] = [Description] + ' used by ' + Userdomain + '\' + Username
where Userdomain is not null and Username is not null and Description not like '% used by ' + Userdomain + '\' + Username

If the field that you want to append to the description is a custom field then you should include tblAssetCustom and build subqueries.


Alexey T
#4Alexey T Member Original PosterPosts: 12  
posted: 10/27/2021 2:54:07 PM(UTC)
Oh.
Thanks, but I'm confused. :)
At first you seemed to be talking about a trigger, now about a script. I don't know where to run it yet.
prighi61
#5prighi61 Member Posts: 41  
posted: 10/27/2021 3:05:53 PM(UTC)
Originally Posted by: Alexey T Go to Quoted Post
Oh.
Thanks, but I'm confused. :)
At first you seemed to be talking about a trigger, now about a script. I don't know where to run it yet.


I'm sorry I used the term script, but I should have used the term statement. The trigger should be like this:

CREATE TRIGGER mytrigger ON [tblAssets]
AFTER UPDATE AS
BEGIN
Update [tblAssets]
Set [Description] = [Description] + ' used by ' + Userdomain + '\' + Username
where Userdomain is not null and Username is not null and Description not like '% used by ' + Userdomain + '\' + Username
END
Alexey T
#6Alexey T Member Original PosterPosts: 12  
posted: 10/27/2021 3:19:56 PM(UTC)
Everything is fine. :)
Thanks for the suggested solution, but I just didn't understand where to create this trigger? In the web interface, I can create reports using SQL, but this trigger obviously does nothing there. Forgive me not my knowledge, I am still far from being with SQL. :(
prighi61
#7prighi61 Member Posts: 41  
posted: 10/27/2021 3:26:16 PM(UTC)
Originally Posted by: Alexey T Go to Quoted Post
Everything is fine. :)
Thanks for the suggested solution, but I just didn't understand where to create this trigger? In the web interface, I can create reports using SQL, but this trigger obviously does nothing there. Forgive me not my knowledge, I am still far from being with SQL. :(


You have to add it to the SQL database. You'd better use SQL Server Management Studio and open a new Query window where paste the SQL command to, select the lansweeper database and execute it.

Be aware that every time the last user changes, it will be appended (not replacing the previous one).
Alexey T
#8Alexey T Member Original PosterPosts: 12  
posted: 10/27/2021 3:33:13 PM(UTC)
Now I understand. I will try.
And shouldn't this part of the condition (where... and Description not like '% used by ' + Userdomain + '\' + Username) prohibit updating these descriptions if one owner is already indicated there?
prighi61
#9prighi61 Member Posts: 41  
posted: 10/27/2021 3:38:05 PM(UTC)
Originally Posted by: Alexey T Go to Quoted Post
Now I understand. I will try.
And shouldn't this part of the condition (where... and Description not like '% used by ' + Userdomain + '\' + Username) prohibit updating these descriptions if one owner is already indicated there?


To prevent that you should slightly modify it into (add a '%' to the end):

where Userdomain is not null and Username is not null and Description not like '% used by ' + Userdomain + '\' + Username + '%'

so that it can be found even in the middle of the description.
Alexey T
#10Alexey T Member Original PosterPosts: 12  
posted: 10/27/2021 3:40:42 PM(UTC)
Thanks again! I will definitely try it soon.
(I hope nothing breaks ;D)
prighi61
#11prighi61 Member Posts: 41  
posted: 10/27/2021 3:52:37 PM(UTC)
Originally Posted by: Alexey T Go to Quoted Post
Thanks again! I will definitely try it soon.
(I hope nothing breaks ;D)


I forgot to suggest to add a:
SET NOCOUNT ON;

statement just under the BEGIN line.

To remove the trigger just execute the SQL command:

Drop trigger mytrigger.

Maybe you could test the update statement as a script, in advance, after taken a DB backup ;-)
Alexey T
#12Alexey T Member Original PosterPosts: 12  
posted: 10/27/2021 4:01:12 PM(UTC)
Then the entire script, taking into account the adjustments, will look like this:

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


Ok, thanks a lot! :)
Alexey T
#13Alexey T Member Original PosterPosts: 12  
posted: 10/28/2021 9:19:43 AM(UTC)
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
#14prighi61 Member Posts: 41  
posted: 10/28/2021 10:00:00 AM(UTC)
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
#15prighi61 Member Posts: 41  
posted: 10/28/2021 10:15:17 AM(UTC)
Originally Posted by: prighi61 Go to Quoted Post
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 T
#16Alexey T Member Original PosterPosts: 12  
posted: 10/28/2021 10:18:36 AM(UTC)
Thanks a lot again!!!
I'll try it soon, as soon as I have free time again :)
Alexey T
#17Alexey T Member Original PosterPosts: 12  
posted: 10/29/2021 1:08:55 PM(UTC)
Originally Posted by: prighi61 Go to Quoted Post
Originally Posted by: prighi61 Go to Quoted Post
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
#18prighi61 Member Posts: 41  
posted: 10/29/2021 1:33:10 PM(UTC)
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 T
#19Alexey T Member Original PosterPosts: 12  
posted: 11/1/2021 8:40:43 AM(UTC)
Originally Posted by: prighi61 Go to Quoted Post

...
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
#20prighi61 Member Posts: 41  
posted: 11/2/2021 9:22:49 AM(UTC)
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 <> ''
Alexey T
#21Alexey T Member Original PosterPosts: 12  
posted: 11/2/2021 2:21:06 PM(UTC)
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.

Active Discussions

Lansweeper Linux Agent - login user
by  Dinusha Chandrasinghe   Go to last post Go to first unread
Last post: Today at 11:03:07 AM(UTC)
Lansweeper device with multiple NICs
by  mzipperer  
Go to last post Go to first unread
Last post: Yesterday at 7:42:57 PM(UTC)
Lansweeper Internal Note
by  mdavis2503   Go to last post Go to first unread
Last post: Yesterday at 6:00:07 PM(UTC)
Lansweeper Problem with deploy software
by  Max90  
Go to last post Go to first unread
Last post: Yesterday at 12:09:40 PM(UTC)
Lansweeper Can lsagent be protected from deletion
by  RKCar   Go to last post Go to first unread
Last post: 11/29/2021 8:56:09 PM(UTC)
Lansweeper HTTPS not secure
by  Larry Rhea  
Go to last post Go to first unread
Last post: 11/29/2021 7:50:59 PM(UTC)
Lansweeper Warning about Built-in Admin
by  Larry Rhea   Go to last post Go to first unread
Last post: 11/29/2021 7:45:45 PM(UTC)