→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Danielbo
Engaged Sweeper II
Hello,

When we set asset state to Repair we also add a comment about what the issue was. I have made a report wich finds the assets with the correct asset ID and then shows the comments sorted by asset ID since i didnt mange to sort on date. But how do i make it only show the last comment ? Maybe also hide the commentID if thats possible ?

My current report :

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.State,
tblAssetCustom.Warrantydate,
tblAssetComments.Comment,
tblAssetComments.Added,
tblAssetComments.AddedBy,
tblAssetComments.CommentID
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
Where tblAssetCustom.State = 13
Order By tblAssetComments.CommentID Desc


EDIT : is it also possible to send a alert if there havent been a comment the last 14 days ? Eventually a modified report.
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
The report below lists "repair" assets whose last comment is from more than 14 days ago and the details of that last comment. To remove the date filter, clear the Criteria column of the tblAssetComments.Added expression. You can have the results of any report in the Reports tab sent via email on a scheduled basis by following the instructions in this knowledge base article.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.State,
tblAssetCustom.Warrantydate,
tblAssetComments.Comment,
tblAssetComments.AddedBy,
tblAssetComments.Added
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
Inner Join (Select tblAssetComments.AssetID,
Max(tblAssetComments.Added) As Max
From tblAssetComments
Group By tblAssetComments.AssetID) SubQuery On SubQuery.AssetID =
tblAssetComments.AssetID And SubQuery.Max = tblAssetComments.Added
Where tblAssetCustom.State = 13 And tblAssetComments.Added < GetDate() - 14
Order By tblAssetComments.Added

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
The report below lists "repair" assets whose last comment is from more than 14 days ago and the details of that last comment. To remove the date filter, clear the Criteria column of the tblAssetComments.Added expression. You can have the results of any report in the Reports tab sent via email on a scheduled basis by following the instructions in this knowledge base article.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.State,
tblAssetCustom.Warrantydate,
tblAssetComments.Comment,
tblAssetComments.AddedBy,
tblAssetComments.Added
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
Inner Join (Select tblAssetComments.AssetID,
Max(tblAssetComments.Added) As Max
From tblAssetComments
Group By tblAssetComments.AssetID) SubQuery On SubQuery.AssetID =
tblAssetComments.AssetID And SubQuery.Max = tblAssetComments.Added
Where tblAssetCustom.State = 13 And tblAssetComments.Added < GetDate() - 14
Order By tblAssetComments.Added