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

Lansweeper Import data to custom field
by  Alan_LS   Go to last post Go to first unread
Last post: Today at 4:58:11 AM(UTC)
Lansweeper Bitdefender machine showing AV Disabled
by  acooney  
Go to last post Go to first unread
Last post: Yesterday at 9:55:52 PM(UTC)
Lansweeper How to setup Actions
by  Charles.X   Go to last post Go to first unread
Last post: Yesterday at 5:30:18 PM(UTC)
Lansweeper Active Directory Actions
by  acooney  
Go to last post Go to first unread
Last post: Yesterday at 2:55:02 PM(UTC)
Lansweeper forms
by  dan508   Go to last post Go to first unread
Last post: Yesterday at 1:38:36 PM(UTC)
Lansweeper looking for a report on disconnected RDP
by  AZHockeyNut  
Go to last post Go to first unread
Last post: 2/20/2018 11:43:30 PM(UTC)
Lansweeper Helpdesk Reports Tickets Closed and Notes
by  BrendanCook  
Go to last post Go to first unread
Last post: 2/20/2018 9:54:51 PM(UTC)