Blog written by:
Liam Cleary
SharePoint MVP
Intro
During my daily work, I have been using many different PowerShell scripts to run reports, export data from both SharePoint On-premises and Office 365. Once this data is exported I end up opening it in Excel and manipulating it to create the reports I need. As such I have been looking for other tools that can remove the need to run PowerShell and make the whole process easier.
One tool that I came across is the SharePoint Essential Toolkit from the team over at QiPoint. The toolkit is actually a combination of specific tools that can help with more than just reports. The product once installed displays the following components.
To use the tool, firstly you need to add either a SharePoint On-premises or SharePoint Online site. This is done by pressing the “Add Site” button from the left navigation. This displays the login screen where you can then choose the site you want to add. A useful feature is in the advanced sites, where you can select the “Retrieve all Sites from Web Application“. Once you have your site or sites connected they are displayed within the “My SharePoint Environment” screen of the application.
Now you are able to utilize the other components that are part of the toolkit. For the types of reports, I need when performing an audit or review of a SharePoint or SharePoint Online environment, these can now be done directly within the application. To create a report, you can expand your SharePoint environment, navigate through the sites then select the report or task to perform.
To create a report, and use the four tools, Site Audit, Broken Link, Permissions and Site activity, we can right click on the SharePoint site and choose the “Create Reports” option.
The first report that is very useful is the Audit report that will retrieve Site Settings, Site Content Types, List Settings, List Content Types and can include System Lists too. All reports are created and ran using a full job scheduler. To create the report, you can complete the details for either a single specific site or multiple SharePoint sites. The credentials used can be changed as part of the configuration if needed, but does utilize the default settings that were used when you added the SharePoint site.
Each select property can then be tweaked to suit your needs, as well as specific the report is emailed as part of the process. A great feature of the report, is the ability to either export the data as MS Excel / CSV format or even upload the data into a SharePoint list, which is perfect for making these reports available quickly to others via SharePoint.
Once all the settings are completed, the job can either be saved, scheduled or ran straight away. Scheduled jobs can then be set to run at specific times, just as if you were using the Windows Task Scheduler.
Right clicking the newly created job, allows you to select the “Run Now” and start the job.
With the configuration for this job, set to store the Site Audit report to SharePoint, once it has completed the new list will be created and the report stored for access. Adding multiple sites to the job, will then setup multiple jobs with multiple exports of the data. This would be similar to using PowerShell, when exporting information. What is great here though, is that as the content is exported to Excel Format, instead of simply dumping as file out, new tabs are defined in the application with the results.
What is very useful now is the ability to group the results by dragging the columns onto the group canvas above the results.
Multiple columns can be used to create very specific reports, which would be the same as importing the data into Excel manually, then sorting and filtering as needed, however doing it right within the tool, is faster and much better experience, not only from performance but usability. If you don’t need to create custom reports, then out of the box there are many specific ones that can be used. For example, when using List Inventory data, you can select from the following predefined views.
Even better when using List Content Type data, you can then group the data so you can see the content type name, grouped by the sites that are using it.
Not only can the toolkit retrieve audit information, it can also retrieve Security information too, which for an Audit is the key information. Clicking on the “Security” menu, offers the ability to create new jobs, check user permissions or even check for orphaned user. The Check Permission report allows you to select multiple sites to check against, and set the user account to check.
This report once done functions like the other reports allowing for grouping of columns to facilitate fast comparison of results and viewing.
As you can see once you understand how the user interface works for creating the layout of reports you need, any of the components can be used with great ease. Clicking the “Links” menu option, allows you to create a new job that will look for broken links, or run the predefined reports. What is fantastic about the broken links feature is the ability to not only check SharePoint Lists, Pages Libraries, Document Libraries, but also to scan list item attachment contents and document file contents.
These jobs can take quite some time to run, if you have selected the inspect inside the content options. These reports are great to simply send you an email once the results have been retrieved. Once completed the broken links report will display status codes followed by the URLs that needed to be fixed.
If the standard view of results does not display as needed, you can either as before, drag the columns onto the canvas and create the report or simply select from one of the standard views that are available.
The real key here is once the data is retrieved through the jobs, the pre-canned reports can then be ran as needed. The Broken Link Report for example, allows you to very quickly see the links that needed to be fixed in various formats from a Pie Chart, Bar Chart to a Bubble Chart. Once you have run the report you can then export the results and create a mapping list from old URLs to new URLs within a file and re-import it into the job definition. There is a section called “Find/Replace” that allows you to perform blanket replacement, or from a list, using exact match or just flag to make sure before actually making changes. The find and replace process has great capabilities and can perform the following tasks:
- Scan/find/replace within file contents
- Support for out of the box web part modifications
- Perform differential scan
- Supports replacement of any HTML markup and Metadata within content
- Generate graphical and detailed reports on links
- View Historical reports
- Advanced granular filtering allowing for inclusion and exclusion of locations
- Schedule jobs for single time use or reoccurring schedule based
All these features are great and allow an audit process to be completed quickly and efficiently without the need of importing data to Excel and manipulating it. One requirement that is very important and really only comes up during a Security audit is the ability to look for specific markup that should not be within the site. The same system could also be used to search for specific forms of PII data such as credit card numbers. Clicking the “Content” menu item displays some core abilities to Bulk Upload, Copy Term Store, Copy Lists and a general Copy Wizard. More importantly we can use the “Item Query” to search for really anything that would live within the SharePoint sites. The use case here is trying to find content that contains the corporate AMEX credit card number throughout the site. Though this can be done using Data Loss Prevention (DLP) features within both SharePoint 2016 and SharePoint Online, it cannot be done using DLP for previous versions of SharePoint. Using the “Item Query” rule, you simply add a query option that searches for the AMEX credit card number.
Just have to state the credit card number is for testing only and not my real one, sure you would have loved for me to post my real AMEX number for all to see.
Anyway, once this job completes (it can take quite some time, remember it is scanning ALL content), you can then see the content that was found.
Reports like this are great to setup on a schedule and email each time it completes. Right now, there is no way to perform a regex query which would make this even more powerful, but have been assured that it is coming soon. This would mean you could use industry standard querying syntax for PII data or any format of data that you needed.
All in all, the toolkit is fantastic and is definitely something that could be used to replace manual PowerShell and Excel manipulation. Apart from the odd slowness as the jobs run which in all fairness is expected as they execute across the internet. The On-premises SharePoint tests ran quicker due to better bandwidth and of course it is local to the servers.
- SharePoint Top 5 Features for Newbies - December 17, 2018
- SharePoint versus OneDrive - December 17, 2018
- How to Cleanup SharePoint User Permissions - July 25, 2017