Notification

Icon
Error

Report for Hardware Maintenance

Posted: Tuesday, November 23, 2021 9:17:03 AM(UTC)
PBjelly

PBjelly

Member Original PosterPosts: 4
0
Like
This issue has been solved! Click here to view the solution
Hey everyone,
I'm quiet new to SQL and my company wants to add a maintenance report to our lansweeper.
I got it figuered out, how to make the report and also adding colors depending on how many days left until the maintenance of one device runs out (green >60 days left, orange >30 days left, red <30 days left).
Now I want to add a column where it actually tells, how many days left until next maintenance, but I'm not sure how to do so... d'oh!
And I have another question: Is it possible to also add a report, when the days left is under 30 days, so it sends out a reminder?

I hope someone can help me out with this issue.
Here is, what I have got so far:
Quote:
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
Convert(datetime,tblAssetCustom.Custom1,120) As [Last maintenance],
Convert(datetime,tblAssetCustom.Custom2,120) As [Maintenance expired],
tblAssetCustom.Serialnumber,
Convert(nvarchar,tblAssetCustom.PurchaseDate,103) As [Purchase date],
Convert(nvarchar,tblAssetCustom.Warrantydate,103) As [Warranty expiration],
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
Case
When tblAssetCustom.Custom2 < GetDate() + 30 Then '#ffadad'
When tblAssetCustom.Custom2 < GetDate() + 60 Then '#ffe662'
Else '#d4f4be'
End As backgroundcolor
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.Custom1 < GetDate() + 120 And tblAssetCustom.Custom1 >
GetDate() - 120
Order By [Maintenance expired] Desc
Options
PBjelly
#1PBjelly Member Original PosterPosts: 4  
posted: 11/23/2021 2:53:33 PM(UTC)
I've got it working for myself.
0
PBjelly
#2PBjelly Member Original PosterPosts: 4  
posted: 12/22/2021 8:44:37 AM(UTC)
Originally Posted by: PBjelly Go to Quoted Post
I've got it working for myself.


EDIT: I have to re-open this post, because there is one major issue with this report:
Because of this line: "Where tblAssetCustom.Custom1 > GetDate() - 120" nearly at the end, the report will only show up hardware where the maintenance date isn't older than 120 days.

This is a realy aggressive limit, as there is a maintenance schedule every 2 or 3 years. So hardware, 120 days past the "GetDate()" will no longer show up in the list. Does anyone has an idea how to change this?
I can't get past the 120 days, as this will result in an conversion error.
0
PBjelly
#3PBjelly Member Original PosterPosts: 4  
posted: 12/22/2021 12:54:27 PM(UTC)
Originally Posted by: PBjelly Go to Quoted Post
Originally Posted by: PBjelly Go to Quoted Post
I've got it working for myself.


EDIT: I have to re-open this post, because there is one major issue with this report:
Because of this line: "Where tblAssetCustom.Custom1 > GetDate() - 120" nearly at the end, the report will only show up hardware where the maintenance date isn't older than 120 days.

This is a realy aggressive limit, as there is a maintenance schedule every 2 or 3 years. So hardware, 120 days past the "GetDate()" will no longer show up in the list. Does anyone has an idea how to change this?
I can't get past the 120 days, as this will result in an conversion error.


It was just a little adjustment and the problem was solved.
For everyone who's interested in such kind of report. Here is the final code:

Code:
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Serialnumber,
  Convert(datetime,tblAssetCustom.Custom2) As [Last Maintenance],
  Convert(datetime,tblAssetCustom.Custom1) As [Maintenance expired],
  Case
    When GetDate() < tblAssetCustom.Custom1 Then Cast(DateDiff(DAY, GetDate(),
      tblAssetCustom.Custom1) As nvarchar) + ' days'
    When GetDate() >= 0 Then 'expired'
  End As [Days left],
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen,
  Case
    When tblAssetCustom.Custom1 < GetDate() + 30 Then '#ffadad'
    When tblAssetCustom.Custom1 < GetDate() + 60 Then '#ffe662'
    Else '#d4f4be'
  End As backgroundcolor
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.Custom1 < GetDate() + 1000000 And tblAssetCustom.Custom1 >
  GetDate() - 120 And tblAssetCustom.State = 1
Order By [Maintenance expired]
SOLUTION 0

Active Discussions

Lansweeper Clearing Thermal State Warning
by  calmed-anodises   Go to last post Go to first unread
Last post: 5/20/2022 6:28:27 PM(UTC)
Lansweeper Quality problems with Lansweeper
by  Hendrik.VE   Go to last post Go to first unread
Last post: 5/20/2022 3:43:44 PM(UTC)
Lansweeper Bitlocker keys
by  SWResearch   Go to last post Go to first unread
Last post: 5/20/2022 1:34:18 PM(UTC)
Lansweeper Lansweeper Helpdesk instructional webinar
by  Erik.T   Go to last post Go to first unread
Last post: 5/20/2022 10:40:30 AM(UTC)
Lansweeper Outlook task and calendar
by  ITVTECH   Go to last post Go to first unread
Last post: 5/20/2022 8:26:51 AM(UTC)
Lansweeper SCCM scanning incorrectly reporting last seen results
by  SWResearch   Go to last post Go to first unread
Last post: 5/20/2022 12:34:52 AM(UTC)
Lansweeper Scanning certificates
by  EagleEyeJoe   Go to last post Go to first unread
Last post: 5/19/2022 7:39:24 PM(UTC)
Lansweeper Lansweeper Cloud Location
by  FrankSc   Go to last post Go to first unread
Last post: 5/19/2022 7:07:49 PM(UTC)