Get ALL SharePoint Sites & Subsites Metrics with Power BI
Вставка
- Опубліковано 22 лип 2024
- SharePoint has a wealth of data available on sites, lists, files, and activities in the OData feeds. This is particularly useful to get reporting on subsites, which has been historically hard to get. Here’s how to access this data with Power BI. In this example, we get a list of all of our SharePoint sites, their recent/lifetime views, and last activity date.
Here’s the text for the Power Query site query mentioned in the video:
/_api/search/query?querytext='(contentclass:STS_Site)(contentclass:STS_Web)'&trimduplicates=false&rowlimit=50000
This technique is courtesy of Jordan Murphy, who reached out suggesting it as a video topic. He’s done some awesome work creating queries for all sorts of things in relation to this:
/ jordanmurphysphr
0:00 Intro
2:39 Overview of what's in the API
7:00 Creating your queries
17:00 Visualizing - Наука та технологія
Christine, Thanks for the shout out. It was great talking to you about this topic.
One thing of note in the API Query, if the SharePoint Tenant is HUGE, there may be a need to raise the rowlimit value above 50000
Hey Jordan, Christine says you might have a a way to access folders within sites. Would you be able to share and exmaple?
Any idea how to get Page Views ? , i just want a report on 1 specific site, not all tenant. but cant seems to find view count per page . Awesome video BTW.
looking for this too
Hi Christine, is it possible to get an overview of all files and folders which is shared externally in sharepoint with PowerBI, from this information?
That is a really hard one to nail down without third-party tools right now. It's a lot easier to get info about who viewed things externally as a "viewed file" action, because that's in the audit logs with their username (username contains #EXT is external), but getting current permission info for the files is a lot harder.
The content is dynamic and currently cannot be refreshed in the Power BI service. Are there any alternative methods or solutions available for refreshing the dynamic content? I need the contributor/ group on the list/document with the modified date.
You are totally right, I didn't try scheduling refresh so I didn't notice that. The only workaround I'm aware of would be to use Power Automate Desktop to refresh and republish it, since it can directly interact with the desktop app (my trick with these things is to use "send keys" keyboard shortcuts if menu items aren't directly selectable). Other than using a real ETL tool to move the data to a database from the API, that is. 🙃
Hello Christine, super great video. Thanks for making and sharing it.
Quick Question : How do I get more than 500 records with this query? I would like to see all records from the SPO tenant.
Do you have a row limit set in your sites query? Make sure to set it above whatever you think it will return (in the example we used 50000). You might also try excluding OneDrive sites from the initial search, which should reduce the load quite a bit. You can do that by specifying the Path, since OneDrive sites have a different path pattern, e.g.:
"/_api/search/query?querytext='(contentclass:STS_Site) (contentclass:STS_Web) Path:""yourtenantname.sharepoint.com/*""'&trimduplicates=false&rowlimit=50000"
@bi-ome Yes, the query limit is set to 500000 but I keep hitting the 500 limit. I tried your suggested one drive exclusion query but that failed to yield any result. However the results shown by the query you used in this video shows spo sites only and not onedrives. I think this is a limit on power query. It be great if I can get all sites. We surely have more than 50K sites
@@ChanderjeetHi
Were you able to solve this issue?
Hi Christine, Thanks for this video and it is very helpful to make a report for SharePoint data. I have another query to make a report foe SharePoint permissions for all users for all lists, libraries, and sites. Can we get that info through this api?
Kind of - getting the members for the groups, given there's multiple types of groups going on, is convoluted and not always practical. There are products that can do this that are reasonably priced, though (e.g. ShareGate).
Amazing!!! Thanks!
How did you get this excel report ? Do you have any script ?
Not sure what you mean - this is Power BI, but you can do the same technique in Power Query for Excel. It is not using a script per se, it's running a query against the OData feed in SharePoint to get the list of sites. The video explains exactly how to get everything in it step by step, and the query we used is in the video description.
Can you see groups, security, metadata, content type. lables, retention etc information too?
Kind of. You can see groups with permission on objects, but not the groups inside the groups if that makes sense - so for example a site linked to an M365 group has a security group nested inside the SharePoint group and that does not show members. It's hard to join it with separate member data too, if I recall it doesn't have a group ID to match on, just the name. Anything that is physically a column on the list should be in there (e.g. labels).
Thanks for sharing this solution. Do you know if we can get list of all pages for a specific site with views stats for each page? Many thanks 🙏
I think you might need to use the Graph API to get item-level view activity (or PowerShell on audit logs). You're probably aware, but there's analytics in the "site contents" in a browser that has view stats for each site too - assuming you mean from a reporting standpoint but just in case. :)
Thanks Christine, I will explore Graph api. I know about default stats in SP. We have about 200 pages on our site and getting usage analytics manually is not very efficient. So I am looking for a way to get usage data into power bi on a regular basis 😊
@@yurionadventure Yeah, I hear you. You might also look into MS Clarity - supposedly it gives you insight into how users interact with your content and integrates with SharePoint, but I've never heard from anyone who has used it before: learn.microsoft.com/en-us/clarity/third-party-integrations/sharepoint-integration
how can i add site security group and users part of that group so I can see who has access to what site
This is more complicated than you'd expect it to be, particularly if you're using AD groups or M365 groups in the permissions. The SharePoint group users are listed, but any AD/M365 group users you have to get separately, and there's not an ID link to use in the OData as far as I can tell to do a lookup on the group ID - just the group name - or that's my recollection from when I was trying to explore whether or not this was feasible. :)
Hi Christine.
Is this technique applicable for an API that gets info via the sites.selected permission? (I am not the integrationsdeveloper, so please forgive any mistakes in terminology)
We're going to create an api, that accesses roughly 200 specific subsites, preferably by a site collection and then draws the data into Azure.
Every site is built in an identical faction (identical column names), so it should be possible in theory to get the usage statistics for all of them.
We cannot use the broad Sharepoint Admin role, as that would give us access to everything, so we're going to use the sites.selected permission to gain admin access to the specific sites.
(Or whatever the new 2024 site-specific admin role is named).
From that point, I need to figure out, what specific data columns to extract to create a 12 month month-by-month usage statistic for each site (Last time a file was viewed/modified/created, number of unique users, number of each type of action by each user, etc.)
If you want file views, you want to use the activity data from the audit logs instead of this. This happens to have site views because Microsoft aggregated it up, but it won't have all the detail of who viewed what when like the audit logs will.
Hi, very interesting video. I have been struggling to visualize in power BI custom fileds I created in the risks list inside a project sharepoint site
If you’re working with SP lists, you probably want this video instead:
ua-cam.com/video/LYu3wqb2Nx4/v-deo.html 😊
@@bi-ome I saw it, but I still can't figure out out to import custom fields in power BI
I see all other fields, but not the one I created. I tried everything, but I'm under the impression that the = OData.Feed(#"PWA Site URL" & "/_api/ProjectData/[en-us]") is only getting std. fields, but does not import custom fields
Hey !
Just wanted to check once, the rowlimit=50000 is still only extracting 500 rows only for me. Can you confirm once if that is also the case with you?
I tried using rowlimit=500 and the total rows after pivoting was 500. I tried using rowlimit as 600 and it was still 500 after pivoting :(
Not sure what is going on.
Do you have permission on all the sites? I’ve not tried it with a large number as I don’t have 500+ sites in my tenant right now, but Jordan said he ran it on some extremely large number (I think it was 10k?) and it worked for him.
@@bi-ome My team is one of the admins so we should have all access. But even if we had limited access, then also topping at 500 rows should not happen right?
So, what is happening is, it is showing only 500 rows after pivoting. Then I am filtering the data based on what I need. (I am filtering based on a particular list name which is same in every site.) But when I do that, I can see some rows of list names missing. And yeah we have access to those missing lists, so access should not be an issue then I guess.
Also to reduce the rows returning, I modified initial source query to WebTemplate:PROJECTSITE instead of the contentclass filter but facing same 500 row capping issue.
@@bi-ome Also to reduce the rows returning to, I have modified the initial querytext to WebTemplate:PROJECTSITE instead of STS_Site and STS_Web. But same issue.
Did you close and apply the query? Or are you looking in the query editor at the row count?
@@bi-ome Yes I have.
I was actually working on this in a dataflow. So, I saved this, refreshed the dataflow and then connected with Power BI Desktop. It is showing exactly 500 rows only.
Hello Christine, is it possible to get unique visitors ? thanks
At what level? The admin panel has reports on unique users already, if you're looking for site-level and less digging. If you want to DIY it, my understanding is a lot of the activity data about what users are doing is in the Graph API e.g. learn.microsoft.com/en-us/graph/api/reportroot-getsharepointactivityusercounts?view=graph-rest-1.0&tabs=http
The SharePoint API has some data surfaced that's easy to grab, like basic view counts, but not everything is summarized cleanly like that there.
@@bi-ome thank you four your post, but in your video you get ViewsLifeTime and ViewsRecent, I need UniqueViewers ,can't we get this data by your method?
@@bi-ome Thanks for your post, can we get UniqueViewers as ViewRecent or ViewsLifeTime ?
HI Christine, Is it possible to get and overview of shared files and folders in all SharePoint sites with PowerBI
I am in the same boat, it would be good to get the list of files and folders under each site.👍
Technically the sharing link info is in there along with the role access, it's reallllly messy to dig out. There's multiple ways to share, and you can have sharing links that work for "everyone with the link"... it gets crazy. 😅