Any help needed to get me in the right direction would be greatly appreciated. I'm looking for a URL query I can enter to obtain all list items with a specific chosen field.

Example: I have subsites A, B, and C, and on each of those subsites has a same-name list in them, named "List B". I am looking to return all subsite names with List B with entries that have a "Status" column marked as "Closed".

Is this possible as a URL OData query? Or possibly a job for Power Bi?


  • To add to this, would the results show in-browser? Or is there a way to export the results to Excel? – jaredjohn Apr 10 '18 at 18:57
  • 1
    - show us what you tried in your question (via code snippets), and explain the trouble you are having. – Rex Whitten Apr 10 '18 at 21:35
  • I have https://***sharepoin_tenant***/sites/***site_collection***/_api/Web/Webs?$expand=Lists/Items – jaredjohn Apr 11 '18 at 12:35
  • It returns a ton of info, but I don't know how to filter this out by displaying subsite, list name, then all the subsites with that lists specific field value... – jaredjohn Apr 11 '18 at 12:36

You can use the Content Search web part to achieve your purpose:

(However, you need to enter the path of all the subsites. I have done a lot of test about this but still cannot find a better way to automatically display all subsites in one query)

  1. Edit the page that you want to display content and add a Content Search Web Part.

  2. Edit the web part, click “change query” button and specify you query.

  3. Switch to advanced mode, in “query text” region, enter the following text:

    Path:http://{ URL of subsite A} OR Path:http://{ URL of subsite B} OR Path:http://{ URL of subsite C} AND Status= Closed

Then Save your changes.

Pay attention:

The Status in above query is a managed property.

If the “Status” column is a Site Column, it will come in your crawled properties and an associated Managed Property will also be available. And you can use it in your query directly;

However, if the “Status” column is a List Column (Created through a list), after the full crawl is done, it should appear in the crawled property, but it will not be any managed property mapped to it. You must manually map this crawled property to a Managed Property.

| improve this answer | |
  • Thanks for your reply! Because of the number of subsites it would have to work through, I don't think it would be worth the time to enter all the paths (and they get created often); The crawl can also be a hindrance as well, so that's why I was looking into Odata. I seem to have found some good tools using Power Bi, so I think I'll go that route. Thanks again! – jaredjohn Apr 12 '18 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.