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 View installed vs available memory
by  prenckens   Go to last post Go to first unread
Last post: Yesterday at 9:11:05 PM(UTC)
Lansweeper static grouping assets by deparment
by  eis-sps-support@buffalo.edu   Go to last post Go to first unread
Last post: Yesterday at 6:41:27 PM(UTC)
Lansweeper Ping by IP, not Netbios
by  MrVal  
Go to last post Go to first unread
Last post: Yesterday at 6:09:00 PM(UTC)
Lansweeper Emails to Agents when Tickets Assigned
by  dpoll7428   Go to last post Go to first unread
Last post: Yesterday at 5:23:32 PM(UTC)
Lansweeper Some users unable to submit tickets
by  wxfisch  
Go to last post Go to first unread
Last post: Yesterday at 5:09:00 PM(UTC)
Lansweeper Kanban Print-out of tickets
by  gkloimstein   Go to last post Go to first unread
Last post: Yesterday at 4:40:13 PM(UTC)
Lansweeper Not working - Computer: No daylight saving time enabled
by  JesseS  
Go to last post Go to first unread
Last post: Yesterday at 4:25:45 PM(UTC)