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 Ricoh printers
by  AM13337  
Go to last post Go to first unread
Last post: Today at 3:25:14 PM(UTC)
Lansweeper Report field question
by  Charles.X   Go to last post Go to first unread
Last post: Today at 2:21:02 PM(UTC)
Lansweeper Apple Mac HD Serial Numbers Report
by  Charles.X  
Go to last post Go to first unread
Last post: Today at 2:13:18 PM(UTC)
Lansweeper automated reports in Excel format
by  Charles.X   Go to last post Go to first unread
Last post: Today at 2:07:34 PM(UTC)
Lansweeper Conditional Ticket Configuration
Belarus  Charles.X  
Go to last post Go to first unread
Last post: Today at 11:55:07 AM(UTC)
Lansweeper Location updates
by  Charles.X   Go to last post Go to first unread
Last post: Today at 11:00:08 AM(UTC)
Lansweeper Installer Packages Not Running As Scheduled
by  Charles.X  
Go to last post Go to first unread
Last post: Yesterday at 4:04:08 PM(UTC)