hello I have a super awesome technique to share with you today on how to get information about your SharePoint environment into Power bi and the Brain behind this technique is Jordan Murphy who sent me a message on LinkedIn and I'm going to put his LinkedIn information in the video description in case you want to reach out to him but essentially he offered this up as a videotopic idea and I thought it was super awesome so thank you to Jordan for sharing this and long story short there's a ton of really good data in the O data feeds in the back end of SharePoint and what Jordan did is he came up with a query that can get sites and subsides from that oh data feed and then invoke a function to run a query on the particular information you're looking for across all of those sites in the query so that you can essentially get a tenant level reporting out of the SharePoint or data feeds today we're going to start with a getting a list of the sites and the list of sites actually does have the latest modified date and the recent and lifetime views information and in it so that's super awesome if you're a SharePoint administrator you know that this is in the SharePoint admin portal but with this we can create a power bi report off of that data so that other people can see it so for instance your it manager or you could also link it to for instance your site collection administrators with row level security so that they can see all of the sites that they manage and again this this list of sites is important to do first because we need the paths of each of the sites in order to run queries against all sites in our environment you can also use this technique on single specific sites so if you're just trying to get information about the site you own this will work for you also but I do want to point out that you need permission to at least read the things that you're running a lot of these on otherwise your results list will be showing you only the things that you have access to so even if you're a SharePoint administrator oftentimes in modern SharePoint you won't have explicit permission on every single site collection in your environment and this will only pull the list of sites that you explicitly have access to so what I would recommend if you're trying to do this long term to report on everything in the tenant is to have a service account that is set up to have access to all sites that you can use to run the data connection here and if you're not familiar with service accounts they're just basically a licensed account that has the permission level that you're looking for that people don't log into on a regular basis so it's just used for the data refresh it's better practice to use a service account for this sort of thing than it is a regular account both for business continuity reasons meaning you don't want it to fail if you leave but also for security because you don't want to always have access to absolutely everything in your environment consider a service account and I do also before we jump into how to do this I want to give you a little look at everything that is in the oh data feeds you can see if there's something in there that you might find useful other than the site list so what I've done here is I've connected to the root of the odata feed and what you can do is you can click through these things and just browse the content that's in here in this case I have it connected to the root site in my tenant so it's not since we haven't created our functions yet it's not doing this across all Sites yet but I just want to show you the stuff that's in the odata feed and also show you how to set this up too in a minute I just want to give you an idea of what's in here first so essentially what you can do is just scroll through here and click on things to browse what's available in the odata feed so for example if I go to lists down here and I click on the white space around table at the bottom here if I drag this up a little bit you can see what's in here without actually navigating down into it so it's got basically all sorts of information about all the lists so it's got the number of versions that the library is set to or the list is set to and whether or not it appears in search results somewhere in here is the last modified date which is very useful the title of the list obviously is in here but also the views and all the fields and if you find something that you want you can click into it so if I click on this word table here it's going to navigate down and then I can expand the different things that I am interested in so for example somewhere in here I like to use this view Tab and go to columns you search for item the items is something that you can expand so you can expand all lists to show all items and information about those items you can do the same thing for views so if I expand this default view that'll give me the path for the list which is useful and then if I want to go back up a level I can just delete this step that I did to navigate down and go look at something else so other things of interest in here if I search for cycle bin there is all of the content of the recycle bin for this site meaning we can make a global searchable recycle bin to browse there is also activity data so somewhere at the bottom here this activities table that's going to be things that are happening in SharePoint so this one is a little bit trickier to work with because it uses item IDs instead of say file names so you have to kind of really massage the data to get what you're after but it does work just keep in mind that if you have a really large environment that you're going to want to limit activity data or item data to a specific site not all sites because SharePoint will throttle you for sure so the just the massive amount of data that it would try and pull doing that it'll time out before it finishes running so for activity data if you just expand action here it'll tell you what activity and type it is and then you can filter on the specific type you're interested in and then keep expanding down I'm going to do more videos on how to get stuff out of these things so keep an eye out for that I'm gonna go I'm not going to go into Super detail right now on every single one of these because today we're just going to be getting a list of our sites so that we can invoke functions on that list of sites so back to what we were originally doing I'm going to open up a new power bi file and start this from scratch so you can see the process all right so we're going to go to transform data and what I'm going to do is I'm going to make a parameter here for our tenant root URL and I'm going to do that just because later on when we make functions in here it's nice to be able to decide what you want to be running that function on whether you want it to be running on a single site or all your sites or specific other site that kind of thing so it just the parameters makes it easier to switch back and forth between those things so I'm going to make a new parameter I'm going to call this sentence root URL you can call this whatever you want and this is going to be https colon flash this is your root site in your SharePoint environment so mine is like this and no trailing slash because we're going to put that trailing slash into the odata feed URL instead so just be mindful you don't want double slashes here all right so we're ready to get our odata site information so if we go to the new source menu select odata feed and then go to the advanced tab it'll let us select the parameter we just created as the base for the URL and then I'm going to paste in the query bit here so I'll pick out my Extra Spaces put in the extra forward slash here so this API path is what we were looking at a minute ago when we were browsing through the different things in the API so it's inside there it's doing a search and it's querying for content class STS site and STS web so STS web is going to be our subsites if you for whatever reason don't want this to return subsites just take that part out I feel like having the subsites in there is very valuable because getting reporting on subsites in SharePoint is very difficult and difficult I mean it doesn't exist without third party tools so we want that um and then click ok so here's our query I'm going to rename it it's peace sites oops that's peace sites and now we're going to expand this down a whole bunch of times so go to the primary query results click on the word record to expand it then go to relevant results here and click on the word record to expand that and same thing we're going to go to table click on the word record to expand it again and now we're going to click on the word list next to the word rows here expand that and this we're going to convert to a table so there's this two table button in the top left click that and then click OK on the box that pops up so next up we need to add a column so under add column go to index column this index column is very important so don't skip this step and then we can keep expanding this column one to get to our actual data so just keep clicking this double arrows button and keep expanding it down and when you get to here do expand to new rows and then click it again and now we have our actual data so we're going to uncheck this value type field here because that's going to interfere with the pivot so you just want to keep key and value click Ok We're going to filter this down to just the fields we care about before we pivot because what I've found is if you don't filter it down a bit you get an error so a lot of this is garbage by the way we don't need all of this data because a lot of them are just blank so we're going to deselect all and then select the things we want to keep so I'm going to keep author that's going to be who created this site and content class is going to tell us if it's a site or sub site this culture and deep links are blank I'm not sure what those are about I'm going to keep the description importance is blank so don't bother with that you can browse through this and look and see what the values are to see if you want to keep them we definitely need to keep pass I'm going to do Parent Link also because that's going to tell us which subsites belong to which parent sites and this last modified time we want rank is kind of interesting I'm not sure what goes into the rank calculation but it does appear to be pretty valid all right I'm not sure the difference between site description and description are I'll just keep both for now and we'll look at it site ID we want site name is actually the path so you don't want that one you want title instead and the unique ID for whatever reason is always a bunch of zeros so what you actually want is the site ID and then the views information so views lifetime views recent and then web template is going to tell you what site type it is so like is it a team site versus a communication site and click ok now we need to do our pivot so go to the transform Tab and the ribbon and then with your key column selected click on pivot column that's good and then for aggregate change it from count to don't aggregate that's under the advanced options and click ok so that puts it in a lovely table for us if you want to you can remove the index now I'm just going to leave it in there let's see what our description and site description appear to be the same thing they have the same values in it so we can go back to this step and uncheck one of those through the gear menu just going to delete this one okay so now we need to set our column types because that wasn't automatic so you'll notice that these are all abc123 which means power bi doesn't know what data type these are so I'm going to start with our views and make that a number so data type whole number then last modified time that is going to be a date time time zone don't try and go directly to date time it won't work because it's got the time zone in it and you can go to date time time zone and then change it to date if you want to so for example can change this to date if I do replace current it'll break so I'm going to add it as a new Step because you can't go directly from the text value of the date time time zone directly to date it doesn't quite figure that out so I need to take a jump through the daytime time zone type to get there and the rest of these I'm just gonna make text so I'm gonna hold Ctrl and select these Okay so we've got our site list now so we can so we can put this information in a table visual we can conditionally format the path so that it's a clickable link if we want to we could clean up this content class column by and say adding a conditional column that relabels it to site and subsite there's also the template here that tells you what type of site this is so you could friendlyify this up group is going to be your team sites site page publishing is your communication sites STS is going to be a team site but one that is not linked to a team if that makes sense so a team site is a type of site template doesn't necessarily have to be linked to a teams group the other thing we want to do with this is filter out our OneDrive and some of the back end SharePoint sites that exist so since the odata feeds are security trimmed you'll only see your own OneDrive sites in here however you do want to filter them out because you you don't want them to show in reporting so to do that we can use this web template column so we want to filter out this SPS purse which stands for personal I think it's the OneDrive and point publishing hub and the point publishing topic we want to take out the app catalog also so that'll leave just our normal SharePoint sites all right and while we're in here let's do our query for the API browser so we can kind of look around and see what's in here so to do that we're going to go to the Home tab again go to new source and oh data feed just like last time except this time before our URL parts we want our parameter and we want forward lash underscore API forward slash and that's it click OK most of the interesting stuff is going to be in web but you could also do files and lists here but keep in mind that files and lists also exists under web so web really has most of the content in it so I'm going to do that one click OK and here's what we end up with so again the purpose of this query is to be able to browse and see what's available in the odata feed so you can highlight these cells to get a preview down here at the bottom you can click into these to expand and browse that way we could use this later on when we're getting particular things out of the API but I just wanted to show you that it's in here so that you can see what's in there and I like to convert this to a table so that I can search easier and filter easier and again since we're right now this is linked to a single SharePoint site in another video we're going to set up a function that goes and does whatever we're querying here from our site list up here so just for the sake of closure I'm going to put together a report page with this content so you can see what it looks like if you know how to create a table visual that has the conditional formatting for clickable site links and all that and then you could probably skip this part but I just wanted to kind of close the loop here so we did a close and apply on our SP sites data so now we're going to make a table visual and we're going to drop our content in there so we want the site title the template views last modified date and we're going to change this last modified date and format so click on the name of the field there and change the format to whatever you want we'll do short date and the author and by the way I think the most recent views is going to be the last 14 days but I'm not entirely certain about that so now what we need to do is to tell power bi that the path field is actually a URL so I'm going to click on the path field and the sidebar here and then go to data category and change the data category to web URL maybe I'll do the same thing for the parent link too because that's also a URL and that's going to let us do conditional formatting that's a clickable link so I'm going to go to the site title field here I'm going to go to conditional formatting web URL and then I'm going to select the path so it's automatically summarizing by first path but since there's only ever One path per Row in this table that's fine click OK and now we have a clickable link so the content class I'm going to go back into the transform data and relabel that a little bit so I'm going to do a conditional column and I'm going to say the content class equals yes site then bytes or if content class is STS web then this bite else no so I'll give us a more friendly oops I should have named that something huh this site site okay so we're going to use that as a filter on our page so I'm just going to put this in the page level filters so you can filter on which ones are sub sites these are actually my subsite names by the way they're they're called I'm sorry I guess I was just testing what how subsites came through in the um API but that allowed us to filter on that and you could also make this a matrix but we have a whole bunch of text columns and the text columns and matrixes don't mesh super well so I'm just going to leave it as is we could also make a chart with this data so we could do a site title the sum of recent views and I'm going to import a theme real quick because the default theme bothers me and I kind of like the table style of minimal okay maybe I'll do one of these hierarchy ones so let's do the web template that's the category and the accounts of the site ID the values count give this a better title and we can turn on the data labels all right so there's our super awesome report for some reason the order on tables never follows the theme there we go so there's our super quick rundown on how to get the basic site statistics out of the SharePoint odata feed you could at this point publish the report and schedule Refresh on it so that it is automatically up to date so I hope this was useful to you and thank you again to Jordan for sharing this technique with us and stay tuned for a few more detailed explanations of how to do very specific things with this data so I think next up I'm going to do how to get the information about lists and libraries to keep an eye on what your top most used libraries are where you have the most activity and maybe where you're approaching the list view threshold in SharePoint lists so keep an eye out for that and I hope you have a great day