1

I would like to call an API to Sitecore to get changes of Media Library files stored in Sitecore.

So given a Media Library, I would like to list the New Files, Updated Files and Deleted files since a timestamp. SharePoint, Google Drive, Box.com etc all have a similar feature.

The only thing I can seem to find is the push API where I install a Sitecore ASP.NET application into Sitecore itself and it will then send updates to my app that way.

But isn't there some sort of pull-based event api?

  • 1
    Hi Nicholas - welcome to SSE, we are happy to have you as part of this community. Sitecore doesn't have anything like you are asking for out of the box. While you point to SharePoint, Google Drive & DropBox, these systems are dedicated document storage systems, so it makes sense that they would have that. But Sitecore is primarily a content management system, document storage is a very small part of the system and it is very limited in its functionality. – Richard Seal Sep 4 '18 at 16:47
  • By files stored in Sitecore, I assume you mean in the Media Library? You might be able to use an RSS feed to pull this info, but the OOTB one will not perform well with a lot of items in the media library so better to do something custom. – jammykam Sep 4 '18 at 17:18
  • if you mean the media library files, i think the closest thing to what you are asking is to patch the upload process (such as Sitecore.Pipelines.Upload.Save) and use custom solution to store the information you need – Naim Alkouki Sep 4 '18 at 17:38
  • hi yes i mean the media library files. so i want to know when someone has created a new library, deleted a file from a library, edited a file from a library, and added a file to a library. that kind of thing. @jammykam what do you mean by something custom? – Nicholas DiPiazza Sep 4 '18 at 18:31
  • By custom, I mean writing something yourself. RSS would possibly work but would be slow, since it works by crawling the tree and not using Search indexes (for example) so will take a long time to generate for large media library. Also it would not notify for deletions, only insert/update. – jammykam Sep 4 '18 at 18:49
1

There is no OOTB API for your requirements. You would need to build it from scratch. One solution would be to use PublishQueue table from master database. This table is used to store all item changes. Information from this table is used during incremental publishing.

You can have this kind of SQL script in your stored procedure (quickly made by me as a starting point for you) which will be used by your API to retrieve data:

WITH ItemsTable (ItemPath, ID, Name, TemplateID, MasterID, ParentID, Created, Updated)
    AS
    (
        SELECT CAST('/' + base.Name AS nvarchar(MAX)) as ItemPath,
            base.ID, base.Name, base.TemplateID, base.MasterID, base.ParentID, base.Created, base.Updated

        FROM Items as base
        WHERE base.ID = '11111111-1111-1111-1111-111111111111'  

        UNION ALL

        SELECT CAST(ItemPath + '/' + child.Name AS nvarchar(MAX)),
            child.ID, child.Name, child.TemplateID, child.MasterID, child.ParentID, child.Created, child.Updated

        FROM ItemsTable as parent 
        INNER JOIN Items as child 
            ON child.ParentID = parent.ID 

            UNION ALL

        SELECT CAST(ItemPath + '/' + archived.Name AS nvarchar(MAX)),
            archived.ItemId, archived.Name, archived.TemplateID, archived.MasterID, archived.ParentID, archived.Created, archived.Updated

        FROM ItemsTable as parent 
        INNER JOIN ArchivedItems as archived 
            ON archived.ParentID = parent.ID 
    )   
    SELECT ItemPath, ItemsTable.ID, Name, PublishQueue.Action, PublishQueue.Date
    FROM [sc9u2_Master].[dbo].[PublishQueue] PublishQueue
    INNER JOIN ItemsTable ON PublishQueue.ItemID = ItemsTable.ID
    WHERE ItemPath LIKE '/sitecore/content/Habitat Sites/Habitat Home/home%'
    ORDER BY ItemPath DESC, Date

Your WHERE clause would be obviously something like this as you want to have changes from media library:

WHERE ItemPath LIKE '/sitecore/media library%'

Result is something like this:

enter image description here

In highlighted section you see that you have three particularly interesting actions for your case Created, VersionAdded and Deleted. These three actions are your changes that you would like to track:

Created action = New file

VersionAdded action = Update file

Delete action = Delete file

So you can also adjust SQL script to get only these actions:

AND [Action] IN ('Created', 'VersionAdded', 'Deleted')

To fulfill also your Timestamp requirement you can introduce another statement to WHERE clause:

AND Date > '2018-09-05'

Full SQL script that you can use is below (not production ready, you need to tweak it and tune up):

WITH ItemsTable (ItemPath, ID, Name, TemplateID, MasterID, ParentID, Created, Updated)
    AS
    (
        SELECT CAST('/' + base.Name AS nvarchar(MAX)) as ItemPath,
            base.ID, base.Name, base.TemplateID, base.MasterID, base.ParentID, base.Created, base.Updated

        FROM Items as base
        WHERE base.ID = '11111111-1111-1111-1111-111111111111'  

        UNION ALL

        SELECT CAST(ItemPath + '/' + child.Name AS nvarchar(MAX)),
            child.ID, child.Name, child.TemplateID, child.MasterID, child.ParentID, child.Created, child.Updated

        FROM ItemsTable as parent 
        INNER JOIN Items as child 
            ON child.ParentID = parent.ID 

            UNION ALL

        SELECT CAST(ItemPath + '/' + archived.Name AS nvarchar(MAX)),
            archived.ItemId, archived.Name, archived.TemplateID, archived.MasterID, archived.ParentID, archived.Created, archived.Updated

        FROM ItemsTable as parent 
        INNER JOIN ArchivedItems as archived 
            ON archived.ParentID = parent.ID 
    )   
    SELECT ItemPath, ItemsTable.ID, Name, PublishQueue.Action, PublishQueue.Date
    FROM [sc9u2_Master].[dbo].[PublishQueue] PublishQueue
    INNER JOIN ItemsTable ON PublishQueue.ItemID = ItemsTable.ID
    WHERE ItemPath LIKE '/sitecore/media library%' AND [Action] IN ('Created', 'VersionAdded', 'Deleted')
    AND Date > '2018-09-05'
    ORDER BY ItemPath DESC, Date

And result:

enter image description here

  • What is the WHERE base.ID = '11111111-1111-1111-1111-111111111111' about? is that a special base ID that has some sort of special significance or is that something I need to substitute? – Nicholas DiPiazza Sep 26 '18 at 16:05
  • 1
    by the way - THANK YOU. your query works amazingly. – Nicholas DiPiazza Sep 26 '18 at 16:13
  • 11111111-1111-1111-1111-111111111111 is id of Sitecore root item sitecore – Peter Procházka Sep 26 '18 at 16:21
  • Did you specify /sitecore/media library% as an example of where to look for content? Or is there some reason to use that only? I want to use /sitecore/content/% for example. If I use /sitecore/% will I get too much data? – Nicholas DiPiazza Sep 26 '18 at 17:38
  • That was only example :) You can use any path – Peter Procházka Sep 26 '18 at 17:40
0

It sounds to me like the only way Sitecore has to do this is by using the History table

https://doc.sitecore.net/sitecore_experience_platform/setting_up_and_maintaining/security_and_administration/the_history_table_and_the_history_engine

There is unfortunately no api or UI to this feature. So you must use database connections to query for new events.

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.