Notification

Icon
Error

Alert or report or notification for an Asset when then time/date has expired

Posted: Wednesday, May 3, 2017 2:24:42 PM(UTC)
Overload223295

Overload223295

Member Original PosterPosts: 4
2
Like
I have some laptops that are in a Asset Group. These laptops are borrowed for presentations , or work in travel etc. What I need is an notification (alert or email or report etc) that can notify me when the borrowed time/date has expired so I can call the user to return the laptop.

Thank you
Nick.VDB
#1Nick.VDB Member Administration Posts: 251  
posted: 5/8/2017 10:09:15 AM(UTC)
To achieve the behaviour that you are after we would recommend using an asset custom field to give in a date.

We can then create a report where we give back only the assets who have a 'Return Date' value that is lower than the current date with the following code, Where tblAssetCustom.Custom1 > Convert(nvarchar(10),GetDate(),103). Do make sure that the date given in follows the DD/MM/YYYY format.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.


Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetCustom.Custom1 As DueDate,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.Custom1 < Convert(nvarchar(10),GetDate(),103) And
  tblAssetCustom.Custom1 Is Not Null And tblAssetCustom.Custom1 != '' And
  tblAssetCustom.State = 1
Overload223295
#2Overload223295 Member Original PosterPosts: 4  
posted: 5/17/2017 9:59:50 AM(UTC)
Thank you for help Nick, but right now I have some issues with this SQL querry. If I enter a date in the valid format date MM/DD/YYYY will show me the laptops in the report. The issue is that is showing all the laptops , and this was supposed to show me only the laptops that has the date expired or at least the same.
Overload223295
#3Overload223295 Member Original PosterPosts: 4  
posted: 5/19/2017 2:33:07 PM(UTC)
Can you help please with this?

Originally Posted by: Nick.VDB Go to Quoted Post
To achieve the behaviour that you are after we would recommend using an asset custom field to give in a date.

We can then create a report where we give back only the assets who have a 'Return Date' value that is lower than the current date with the following code, Where tblAssetCustom.Custom1 > Convert(nvarchar(10),GetDate(),103). Do make sure that the date given in follows the MM/DD/YYYY format.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.


Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssetCustom.Custom1 As DueDate,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.Custom1 < Convert(nvarchar(10),GetDate(),103) And
  tblAssetCustom.Custom1 Is Not Null And tblAssetCustom.Custom1 != '' And
  tblAssetCustom.State = 1


Nick.VDB
#4Nick.VDB Member Administration Posts: 251  
posted: 5/19/2017 3:01:32 PM(UTC)
We did a small test and found that the date should be added in DD/MM/YYYY format instead of MM/DD/YYY and then it did work correctly in our environment. We would recommend doing the same for your assets.
Overload223295
#5Overload223295 Member Original PosterPosts: 4  
posted: 5/19/2017 3:21:07 PM(UTC)
I did that and the report stil showing me all the laptops that have the date inserted , I entered 01/01/2018 at custom asset1 and still is showing? Am i missing something ?

Originally Posted by: Nick.VDB Go to Quoted Post
We did a small test and found that the date should be added in DD/MM/YYYY format instead of MM/DD/YYY and then it did work correctly in our environment. We would recommend doing the same for your assets.


Active Discussions

Lansweeper Computers/Systems with no Anti-Virus Installed
by  Charles.X   Go to last post Go to first unread
Last post: Today at 3:02:29 PM(UTC)
Lansweeper Average time to close helpdesk tickets
by  Charles.X  
Go to last post Go to first unread
Last post: Today at 2:56:06 PM(UTC)
Lansweeper A count of repeat lockouts
by  Charles.X   Go to last post Go to first unread
Last post: Today at 2:53:02 PM(UTC)
Lansweeper Windows Logs>Application
by  Charles.X  
Go to last post Go to first unread
Last post: Today at 2:25:22 PM(UTC)
Lansweeper set threshold to report
by  farsad   Go to last post Go to first unread
Last post: Today at 12:52:50 PM(UTC)
Lansweeper Syntax for showing drive C only
by  RC62N  
Go to last post Go to first unread
Last post: 4/24/2018 8:56:35 PM(UTC)
Report Center Windows update report
by  HqrNL207   Go to last post Go to first unread
Last post: 4/24/2018 8:16:38 PM(UTC)
Lansweeper Network Port Assets Inventory
by  DavidGT  
Go to last post Go to first unread
Last post: 4/24/2018 7:03:57 PM(UTC)