Menu

Finding documents that have been made public within ConnectWise

Google+0
LinkedIn0

As a follow up to my recent post managing document security in ConnectWise and in response to a reader comment, here’s a SQL script you can use to retrieve a list of documents stored within ConnectWise that are attached to service or project tickets, are marked as public, and weren’t automatically added to a ticket by the email connector.

SELECT dbo.DM_Relationship.SR_Service_RecID AS [Service Ticket #], 
       dbo.DM_Document.Title AS [Document Title], 
       dbo.Member.Member_ID AS [Member ID] 
FROM   dbo.DM_Document 
       LEFT OUTER JOIN dbo.Member 
                    ON dbo.DM_Document.Member_RecID = dbo.Member.Member_RecID 
       LEFT OUTER JOIN dbo.DM_Relationship 
                    ON dbo.DM_Document.DM_Document_RecID = 
                       dbo.DM_Relationship.DM_Document_RecID 
WHERE  ( dbo.DM_Document.DM_Type_RecID <> 89 ) 
       AND ( dbo.DM_Relationship.SR_Service_RecID IS NOT NULL ) 
       AND ( dbo.Member.Member_ID <> 'zAdmin' ) 
       AND ( dbo.DM_Document.Public_Flag = 1 )

If you’re not comfortable working directly with SQL you can download the Public Documents Report I built using Excel. When you open the XLS for the first time, you’ll receive a few errors. To fix these go to the Data tab, click the Connections button, select ConnectWise Database from the list of connections and click the Properties button. The click the Definition tab and edit the Connection String:

Provider=SQLOLEDB.1;Server=mycwserver;Initial Catalog=cwwebapp_nexnow;
User ID=cwsqluser;Password=cwsqlpwd;Trusted_Connection=False;

Update the connection string to reference the name of your ConnectWise SQL server (Server) and ConnectWise database (Initial Catalog). You’ll also need to provide credentials for a SQL account that has at least read-only access to your ConnectWise database (User ID and Password). Click the OK button to save your changes, then click the Refresh All button from the Data tab.

A big thanks to Juan Trevino for providing the idea for this post.

Want to do more?

Do you want to do more of your own ConnectWise reporting in Report Writer, Power BI, or another reporting tool? Contact us for one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.

Related Posts