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 Posts: 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  
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
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
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  
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
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

Report Center Tickets Over 30 Days old
by  dconway   Go to last post Go to first unread
Last post: 6/26/2017 11:09:35 PM(UTC)
Report Center Shorten Active Directory user and computer OUs
by  mmeetze  
Go to last post Go to first unread
Last post: 6/6/2017 9:21:11 PM(UTC)
Report Center Windows update report
by  myersac   Go to last post Go to first unread
Last post: 6/6/2017 2:28:48 PM(UTC)
Report Center List of who made changes to assets or settings and when
by  hakan35  
Go to last post Go to first unread
Last post: 6/1/2017 9:52:07 AM(UTC)
Report Center Helpdesk Management Report
by  harringg   Go to last post Go to first unread
Last post: 5/25/2017 2:20:26 PM(UTC)