Custom report for finding "Comments"

[bsbruc3]

I'm looking for a way to see all asset Comments & Docs, who added it, and when it was added for any machine in Lansweeper 5.0.

Basically,
I only care about documents and comments and using them as a trouble ticket system.

e.g.,
User1 works on workstation1 (WS).
User1 has to go on a business trip that will last a few months.
User1 is assigned to Active Directory (AD) Security Groups (SG) that receive a lot of emails.
User1's Boss1 doesn't like to see return notices because of full email boxes.
User1 can't check his email while he is on the trip.
Boss1 constantly issues a policy to the dismay of TechSupport1 (TS1) to remove User1 from all his AD SG.
Eventually, User1 returns.
TS1 has to put User1 back into all of his AD SG.
TS1 wants to add a document (e.g., snapshot of his ad member of profile) or comment to User1's WS1 (unless you know of some way to add it to his user account in lansweeper only) so TS1 can recall it later to add User1 back to the AD SG.
TS1 don't really care about User1's WS1's name and User1 wont be any help in remembering the name of his WS1 when he gets back for me to look up the comment. To add to the dilema, User2 might have broke WK1 while User1 was gone. This would mean that WK1 would no longer be User1's WS.

All of that wouldn't matter if I could run a report and only bring up any comments or docs attached to any machine along with who created that comment/doc and when.

Sorry for the long drawn out explination but TS1 (me) is looking for a easy fix.

Thanks for any help.

I know it'll need to us one or all of the following; however, I'm not code savy enough to know how to build the report (yet) :
tblADusers:
Username
Firstname
Lasname
Description

tblAssets:
AssetID
Username

tblAssetDocs:
DocsID
AssetID
Docname
AddedBy
Added

tblAssetComments:
CommentID
AssetID
Comment
AddedBy
Added

[bsbruc3]

So, not being one to give up, I dove back through other prebuilt reports and figured out the essentials. Below is what I came up with.

Instead of combining, I did two separate reports to separate the same doc from showing up with multiple comments on the same machine.


1. For the "Users: All comments" report I used:
tblAssets: These two together gave me the ability to be able to click on the machine name.
AssetID
AsserUnique

tblAssetComments:
Comment
AddedBy
Added

These two gave me the following:
The machine name that the comments were on (useful for deleting/editing them).
Who added the comment.
The comments themselves (each comment on the same workstation has a separate line).
When it was added.

SQL query was as follows:

Select Top 1000000 tblAssets.AssetUnique,
tblAssetComments.AddedBy,
tblAssetComments.Comment,
tblAssetComments.Added,
tblAssets.AssetID
From tblAssets
Inner Join tblAssetsComments On tblAssets.AssetID = tblAssetComments.AssetID
Order By tblAssets.AssetUnique,
tblAssetComments.AddedBy,
tblAssetComments.Comment,
tblAssetComments.Added


2. For the "Users: All documents" report I used:
tblAssets: These two together gave me the ability to be able to click on the machine name.
AssetID
AsserUnique

tblAssetDocs:
Docname
AddedBy
Added

These two gave me the following:
The machine name that the comments were on (useful for deleting/editing them).
The document's names (each document on the same workstation has a separate line).
Who added the document.
When it was added.
Note: You have to click on the worksation name and then on the Docs tb to view the
documents.

SQL query was as follows:

Select Top 1000000 tblAssets.AssetID,
tblAssetDocs.Docname,
tblAssetDocs.AddedBy,
tblAssets.AsserUnique,
tblAssetDocs.Added
From tblAssetDocs
Inner Join tblAssets On tblAssets.AssetID = tblAssetDocs.AssetID
Order By tblAssetDocs.Docname,
tblAssetDocs.AddedBy,
tblAssets.AssetUnique,
tblAssetDocs.Added

[Hemoco]

We would recommend using at least two reports for this, as mixing comments and docs would require you to create a union query. Sample comment and doc reports can be found on pages 103 and 105 of our online documentation. To run a report:
- Browse to Configuration/Reports/Report Builder.
- Paste the SQL query into the bottom pane.
- Click in the upper pane to make the query apply.
- Give the report a Title.
- Hit the Save & Run Report button.