PowerShell script to save data from a specified URL into Dropbox

We can call the Dropbox API using PowerShell easily. Here is a script sample to use “/save_url" API.

$TargetFilePath = “test.doc"
$token = Get-Content -Path “C:\Dropboxkey\token.txt"
$authorization = “Bearer " + $token

$headers = New-Object “System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add(“Authorization", $authorization)
$headers.Add(“Content-Type", ‘application/json’)

$body = ‘{
“path": “/test.doc",
“url": “http://ourserver.com/test.doc"
}’
Invoke-RestMethod -Uri https://api.dropboxapi.com/2/files/save_url -Method Post -Headers $headers -Body $body

Regarding the $token, it is a string hash key to authenticate your identity to Dropbox. So that Dropbox server will perform your /save_url request (to download test.doc from http://ourserver.com)

To get your token, login your Dropbox account and goto App Console. Then click “Generate" button for your access token. Once you get the token (a string) copy it and save in a token.txt file on your computer. In my case, save the file into c:\dropboxkey\token.txt.

 

Add [Back to Top] button in SharePoint master page

If your SharePoint pages are tend to be bulky in content. I mean most page have a long length and users need to scroll up and down during browsing, it is a good idea to add a [Back to Top] button via SharePoint master page.

I believe there are tons of material online tell you how to create it. It actually happened to me and I found none of them fit my situation. Currently my company’s SharePoint is SP2016 on-premise. There are some difficulty apply those solution I found online. Therefore I made below setup based on my research online.

In my script, the button will only show up (fade in) when user scroll down for certain pixel.

1. Add JavaScript in your Master page:
   You can add below script right before the </body> tab. I added directly into the HTML master page.

<button type="button" id="BackTop" class="toTop-arrow"></button><script type="text/javascript">//<![CDATA[
$(function(){
$(‘#BackTop’).click(function(){
$(‘#s4-workspace’).animate({scrollTop:0}, 1000);
});
$(‘#s4-workspace’).scroll(function() {
if ( $(this).scrollTop()){
$(‘#BackTop’).fadeIn(500);
} else {
$(‘#BackTop’).fadeOut(500);
}
});
});
//]]>
</script>

2. Add style in your style sheet:

/*———————- Back to Top button———————————*/
.toTop-arrow {
display: none;
height: 100%;
position: fixed;
right: 30px;
z-index: 9999;
bottom: 0;
width: 70px;
height:70px;
background: url(../Style%20Library/backtotop.png) no-repeat left 0;
border: 0px;
}
.toTop-arrow a{
display: block;
}

/*———————-for go back button———————————*/

3. Upload your back-to-top image:

Create a back to top image with 70px x 70px, name it “backtotop.png" and upload to /Style Library/ library.

In some online tutorial, the arrow icon can be generated via CSS. However I failed to render a correct arrow in the SharePoint environment. It may because there is some conflict between existing CSS. After some hours of try and error, I decide to make my back-to-top image directly.

Hope it helps and enjoy your SharePoint hard life….

 

Using Puppeteer to test your web pages

hmm…. this article is not specific for SharePoint. It is because Puppeteer can be used to test against any web page, including SharePoint pages.

Puppeteer is a node library allow you script your web requests and capture the responses through programming. Here I provide sample for my SharePoint environment.

I am using Win10 with NodeJS installed (v12.13.0). My windows ID is able to browse SharePoint site directly (thru NTLM). To install Puppeteer, in your Node command prompt, type:

npm i puppeteer

above command will download latest Puppeteer from Internet and install on your PC. Note that a recent version of Chromium will be downloaded and installed in the same way. Chromium is essential for Puppeteer. If your environment is behind a firewall or proxy that block your download, you may need to troubleshooting for that unfortunately…

After install, you are now ready. You can directly copy my sample and save it as test.js.

const puppeteer = require(‘puppeteer’);

const siteurl = ‘https://mysharepoint.com/sites/test/&#8217;;

const urls = [
‘SitePages/Home.aspx’,
‘SitePages/Document.aspx’,
‘SitePages/Contact.aspx’,
‘SitePages/About.aspx’
];

(async () => {
const browser = await puppeteer.launch();

for (let i = 0; i < urls.length; i++) {
const url = siteurl+urls[i];
const page = await browser.newPage();
await page.setViewport({
width: 800,
height: 1200,
deviceScaleFactor: 1,
});
page.on(‘console’, msg => console.log(url+’ Console:’, msg.text()));
page.on(‘pageerror’, error => {console.log(error.message);});
//page.on(‘response’, response => {console.log(response.status(), response.url());});
//page.on(‘requestfailed’, request => {console.log(request.failure().errorText, request.url());});

await page.goto(`${url}`, {waitUntil: ‘networkidle2’});
await page.screenshot({path: i + ‘.png’});
}

await browser.close();
})();

Above script will loop thru each URL in the array urls, browse each url, capture the response (it will be shown in your command prompt) and captured a screen capture (size 800 x 1200px) saved in the same directory.

To run above script, simply type follow command:

node test.js

• Using a larger height parameter if your webpage require scroll down to see the full picture.
• waitUntil: ‘networkidle2’ means the web request will be wait until network traffic is idle to start the next request.
• console.log(response.status(), response.url()) will capture the response status code (e.g. 200, 302, 404) with the URL.

In case your PC is sitting behind a proxy,  update the script for this part:

(async () => {
const browser = await puppeteer.launch({
args: [
‘–proxy-server=proxy.server.hk:8080’,
// Use proxy for localhost URLs
‘–proxy-bypass-list=<-loopback>’,
]
});

Enjoy!

PowerShell script to export User Information List

There are several sources online providing script sample to export user list. However none of them can export user details (e.g. Login, email, and to distinguish between user and group). The login information is very important when your SharePoint is riding on multiple domain environment. You can have domainA\admin & domainB\admin at the same time while both Display Name is “Admin".

User Information List is a hidden list per each site collection. Whenever the user or group take any action to the site collection, the user’s information will be added to the list. Also note that even the user have been deleted from Active Directory, his name will remain in the list. The list is helpful to estimate the site usage for license estimation.

Here is the script.

$SiteUrl="http://sharepoint.com"
$OutPutFile = “E:\report\UserInfoList.csv"
$web = Get-SPWeb $siteUrl
$UserInfoList = $web.SiteUserInfoList

$ListItemCollection = @()
foreach ($item in $UserInfoList.Items){
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -name “User Name" -value $item.Name
$xml = [xml]$item.xml
$ExportItem | Add-Member -MemberType NoteProperty -name “Title" -value $xml.row.ows_Title
$ExportItem | Add-Member -MemberType NoteProperty -name “Login" -value $xml.row.ows_Name
$ExportItem | Add-Member -MemberType NoteProperty -name “Email" -value $xml.row.ows_EMail
$ExportItem | Add-Member -MemberType NoteProperty -name “Department" -value $xml.row.ows_Department
$ExportItem | Add-Member -MemberType NoteProperty -name “JobTitle" -value $xml.row.ows_JobTitle
$ExportItem | Add-Member -MemberType NoteProperty -name “ContentType" -value $xml.row.ows_ContentType
$ListItemCollection += $ExportItem
}

$ListItemCollection | Export-CSV $OutPutFile -NoTypeInformation
Write-host “User Information List Exported to $($OutputFile) for site $($SiteURL)"

$web.Dispose()

SQL Queries for SharePoint Content Database (SP groups related)

Recently I need to build some SQL reports with SSRS and come across this topic. By search on web I can find several sources provide the SQL statements. However most of them are not working on SP2016. Hence I re-write the SQL statement and share here. I tested in SP2016 on-premise only.

Query to get all the members of the SharePoint Groups

select dbo.GroupMembership.GroupId, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login FROM
dbo.UserInfo INNER join dbo.GroupMembership ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID
INNER join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
order by groupid

Query to get all SharePoint Groups without member

select dbo.Groups.ID, dbo.Groups.Title, dbo.GroupMembership.MemberId FROM
dbo.GroupMembership right outer join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
where dbo.GroupMembership.MemberId is null

Please note that if you created a SharePoint group with system account (SHAREPOINT\system) the account will be added as the member of new group. However system account is hidden in the web interface. In this case the group is actually NOT empty. You may need to re-write my SQL statement to handle this special case.

— update here, here is SQL statement to handle SHAREPOINT\system with UNION:

(select dbo.Groups.ID, dbo.Groups.Title FROM
dbo.GroupMembership right outer join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
where dbo.GroupMembership.MemberId is null)
UNION
(select dbo.Groups.ID, dbo.Groups.Title FROM
dbo.GroupMembership join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
where dbo.Groups.ID in (select dbo.Groups.ID FROM dbo.GroupMembership join dbo.Groups ON dbo.Groups.ID = dbo.GroupMembership.GroupId
where dbo.GroupMembership.MemberId in (select tp_ID from dbo.UserInfo where tp_Login = ‘SHAREPOINT\system’))
group by dbo.Groups.ID,dbo.Groups.Title having count(dbo.GroupMembership.MemberId)=1)
order by title

Recover password of IIS application pool identity

Recently I come across a very difficult situation… in a production deployment, I realize I need to key in a password I don’t even have! My ex-teammates and all existing document did not mention about it. The account is not setup in UAT or DEV environment so I have no idea the account exist until the deployment day.

After a hour of old document searching, I am almost give up. Until I suddenly find this blog article (How to Recover Passwords for SharePoint Managed Accounts – by Roger Cormier).

In short, if you are the administrator of server, you can use “appcmd.exe" to show you  application pool’s identity’s password. Roger’s blog have covered various usage and preparation. Here I only quote the command here:

cmd.exe /c C:\windows\system32\inetsrv\appcmd.exe list apppool “Name of Application Pool" /text:ProcessModel.Password

Remember, you need to open a DOS prompt with administration right first.

Connect your InfoPath form people picker to the farm

When you are editing your InfoPath form, added a people picker control, test run it (by preview and run the code behind), you may find the people picker always returning error like below:

The red italic fonts means people picker cannot find the user. However you are sure the user exist in your farm. To solve it is easy.

In the InfoPath edit view, right click the people picker control -> properties. In SharePoint Server tab, type in your farm’s address:

Of course, make sure the account you are using have permission on people pickers. (I am not 100% sure. But I think all DOMAIN USER on the DOMAIN SharePoint installed on should be sufficient).

Apply JQuery & Validation plugin in SP2010 list form

It is 2017 Jan and unfortunately I still need to work on SP2010… (cry) I found there are lesser and lesser knowledge about it. I have a hard time to find the tips so let me write one for reference.

Here is the requirement: I need to prepare a form for every staff. They have to input their email address and phone type. So I am going to use OOTB (out-of-the-box) function “Survey" to achieve my job.

The problem is validation. There is a question “Phone Type". Choose among iPhone/Android/Others. If the 3rd radio is chosen, user must fill the “please specific phone type" text field. I found no way to achieve this by OOTB validation column. So I decide to use JQuery + Validation plugin to do it.

Here is a brief summary on my work:
1. Create a Survey list
2. Using SharePoint Designer 2010, create a new custom form. Open and edit it in advanced mode.
3. Upload the needed JS and Validation plugin library files.
4. Insert the JavaScript in the page inline.

In my previous article, I tested newer version JQuery dose not work well with SP2010. I have to use v1.12.4. For Validation plugin, I choose the latest version available which is v1.15.0.

After you download and unzip the package, look for the library files and upload to your server. (You can also upload to your site collection library) I created a “JQuery" subfolder here:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\
so that the JavaScript library reference will be:
/_layouts/jquery/jquery-1.12.4.min.js
/_layouts/jquery/plugins/jquery.validate.min.js
Notice that if you have more than 1 WFE (web front end) server you need to upload the same files to all WFE in same path.

Here is the code for step 4. After you open the form (maybe call it newform2.aspx?) by “edit in advanced mode". Look for <asp:Content ContentPlaceHolderId="PlaceHolderAdditionalPageHead" runat="server">
then insert the reference like below:

<asp:Content ContentPlaceHolderId="PlaceHolderAdditionalPageHead" runat="server">
/_layouts/jquery/jquery-1.12.4.min.js
/_layouts/jquery/plugins/jquery.validate.min.js
<SharePoint:UIVersionedContent UIVersion="4″ runat="server"><ContentTemplate>
<SharePoint:CssRegistration Name="forms.css" runat="server"/>
</ContentTemplate></SharePoint:UIVersionedContent>
</asp:Content>

Then insert below script:

$().ready(function() {
// alert(“JQuery loaded");
$(“#ID_of_submit_button").attr(“Value","Submit");
jQuery.validator.addMethod(“validEmail", function(value, element)
{
if(value == “)
return true;
var temp1;
temp1 = true;
var ind = value.indexOf(‘@’);
var str2=value.substr(ind+1);
var str3=str2.substr(0,str2.indexOf(‘.’));
if(str3.lastIndexOf(‘-‘)==(str3.length-1)||(str3.indexOf(‘-‘)!=str3.lastIndexOf(‘-‘)))
return false;
var str1=value.substr(0,ind);
if((str1.lastIndexOf(‘_’)==(str1.length-1))||(str1.lastIndexOf(‘.’)==(str1.length-1))||(str1.lastIndexOf(‘-‘)==(str1.length-1)))
return false;
str = /(^[a-zA-Z0-9]+[\._-]{0,1})+([a-zA-Z0-9]+[_]{0,1})*@([a-zA-Z0-9]+[-]{0,1})+(\.[a-zA-Z0-9]+)*(\.[a-zA-Z]{2,3})$/;
temp1 = str.test(value);
return temp1;
}, “Please enter valid email.");

var emailInput = $(“input[title=’Email address Required Field’]");
var TxtPhoneType = $(“input[title=’Please input phone type’]");
var validator = $(“#aspnetForm").validate({
onfocusout: false,
invalidHandler: function(form, validator) {
var errors = validator.numberOfInvalids();
if (errors) {
validator.errorList[0].element.focus();
}
}
});
$(emailInput).rules(“add", {
validEmail: true,
required: true,
messages: {
validEmail: “Please enter a valid email“,
required: “Email is required"
}
});
$(TxtPhoneType).rules(“add", {
required: function(){return $(“#radio_ID_of_others").is(“:checked");},
messages: {
required: “Please input phone type if Others is chosen"
}
});
});

function PreSaveAction(){
return $(“#aspnetForm").valid();

};

Save and test it.

 

Object doesn’t support property or method ‘addEventListener’

After include Jquery library into SharePoint 2010, my IE11 is prompting “Object doesn’t support property or method ‘addEventListener'". I have not yet called any Jquery function,

/Scripts/jquery-3.1.1.min.js

I also found that Jquery 2.x have same problem. Finally I changed to Jquery v1.12.4 and no more error. Here is the download link.

Failed to configure ILM, will attempt during next rerun

I got the titled error when setting up a new SharePoint 2010 farm and try to start up the User Profile Synchronization Service in Manage Services on Server. In this article I will describe my understanding to UPS and FIM service how do I troubleshoot it.

Here is the pattern for my problem. After I press on start “User Profile Synchronization Service" within “Manage Services on Server". It shows “starting" for about 10-15 minutes and then it change back to “Stopped" status.

The “User Profile Synchronization Service" is actually calling 2 windows services. You will find them by Windows Services console (key in services.msc to call this console):

Forefront Identity Manager Service (FIMService)
Forefront Identity Manager Synchronization Service (FIMSynchronizationService)

During the “User Profile Synchronization Service" is “starting", FIMSynchronizationService is changed from disabled to started. I see FIMService is trying to start (from disabled to starting) and then fail after a minutes.

At the same moment, I check the ULS log and filter with keyword “ILM" and see following:

Above log have shown what FIMSynchronizationService is doing. In the log “Configuring database" I believe it is working with User Profile Sync DB as I can see the DB is modified the same time. It proved that FIMSynchronizationService have sufficient permission to the database.

In the last line, the full error message is “UserProfileApplication.SynchronizeMIIS: Failed to configure ILM, will attempt during next rerun. Exception: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list."

The same pattern of logs will show up several times (sorry I am too lazy to count) and then FIMSynchronizationService will give up. After it gave up,  “User Profile Synchronization Service" within “Manage Services on Server" will go back to “Stopped" status.

After hours of troubleshooting, I found this great article Best Practices for CRL Checking on SharePoint Servers. The blogger introduce how to handle CRL checking by SharePoint. If your SharePoint server is like mine, which DO NOT HAVE INTERNET ACCESS, I highly recommend you read it. Because Microsoft always assume your farm have Internet access and do not have official KB to tell you how to handle if you don’t.

Regarding this FIM problem, I solved by following steps:

1. Launch gpedit.msc as admin on the SharePoint server
2. Look for Local Computer policy > Windows Settings > Security settings > Public Key Policies
3. Open Certificate Path Validation Settings, in the Network retrieval tab check “Define the policy settings" and uncheck the option to “Automatically update certificates in the Microsoft Root Certificate Program (recommended)."
4. Run gpupdate /force

I am sure User Profile Synchronization Service is a permanent headache to all SharePoint administrators. If you search on Internet you will get tons of related error message and various solution. The same UPS will still in use for SharePoint 2013. I heard SharePoint 2016 will replace it with new function but I don’t have chance to play on new toy yet.