nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

querying for checked out images

Joe_Opland

Is there an easy way to query the database for checked out images, in a given MediaBin folder?

Thank you,
Jim

Find more posts tagged with

Comments

msrinivas

What version of MB do you have? Depending on that there are OOTB SQL Report Views that includes ReportAssetsCheckedOut.

HTH

lyman

The search API's give methods for searching for all assets "checked out" and for searching all assets "checked out by me".

Cheers,

Lyman Hurd

Joe_Opland

The search API's give methods for searching for all assets "checked out" and for searching all assets "checked out by me".

Cheers,

Lyman Hurd

I'm on version 4.6 Build 25. I did find the view, and it worked.

However, since then, the person requesting the information, has modified the criteria of the result set that he needs:

- it needs to list all assets, their folders, and their keywords that are

1) checked out

AND

- 2) have the phrase "Product Incorrect" in their Keywords

So, I am trying to achieve this with the views right now. Any tips would be greatly appreciated.

Thank you,
Jim

msrinivas

You should be able to get this information by using the Find feature and constructing a search query. You can use the windows or web client.

lyman

If you want to use the report views I suggest searching DevNet for my presentation:

"Getting Business Data out of MediaBin"

The ReportAssetMetaData view will get assetid, name and metadata (note that the id for keywords is:

{5BE195ED-0726-11d3-9DDE-0060081F836F})

To get check-out status as well you will have to join with either the ReportAssetsCheckedOut view (simpler) or the ReportAssetList view (this view has everything that ReportAssetsCheckedOut has and more).

To determine checked out assets in a container you can simply query ReportAssetList provided you have the container id. If you simply have a path you can join with the ReportAssetInventory view.

Cheers,

Lyman

Joe_Opland

Thank you for all of that useful information, Lyman.

OK, so the present "ReportAssetMetadata" source code looks like below. How should it look if we add the
"keyword {5BE195ED-0726-11d3-9DDE-0060081F836F} contains 'Product Incorrect'" criterion to it? Would it be in the "where" clause of each of the three select statements?

SELECT ar.name, ar.id, s.factoryid, dbo.getMetaDataName(s.factoryid) AS Expr1, s.val
FROM dbo.SearchableStrings AS s INNER JOIN
dbo.assetrefs AS ar INNER JOIN
dbo.assets AS a ON ar.baseid = a.id INNER JOIN
dbo.revisions AS r ON a.id = r.baseid AND a.maxrevision = r.revision ON s.revisionid = r.id
WHERE (ar.isContainer = 0) AND (ar.baseid <> '{4FE3EE06-A9CF-4917-88F0-EAE56054C676}')

UNION

SELECT ar.name, ar.id, dbo.getMetaDataId(s.factoryid, s.idx) AS Expr1, dbo.getMetaDataName(s.factoryid) AS Expr2, CAST(s.val AS nvarchar(255))
AS Expr3
FROM dbo.SearchableLongs AS s INNER JOIN
dbo.assetrefs AS ar INNER JOIN
dbo.assets AS a ON ar.baseid = a.id INNER JOIN
dbo.revisions AS r ON a.id = r.baseid AND a.maxrevision = r.revision ON s.revisionid = r.id
WHERE (ar.isContainer = 0) AND (ar.baseid <> '{4FE3EE06-A9CF-4917-88F0-EAE56054C676}')

UNION

SELECT ar.name, ar.id, dbo.getMetaDataId(s.factoryid, s.idx) AS Expr1, dbo.getMetaDataName(s.factoryid) AS Expr2, CAST(s.val AS nvarchar(255))
AS Expr3
FROM dbo.SearchableDoubles AS s INNER JOIN
dbo.assetrefs AS ar INNER JOIN
dbo.assets AS a ON ar.baseid = a.id INNER JOIN
dbo.revisions AS r ON a.id = r.baseid AND a.maxrevision = r.revision ON s.revisionid = r.id
WHERE (ar.isContainer = 0) AND (ar.baseid <> '{4FE3EE06-A9CF-4917-88F0-EAE56054C676}')

Thank you,
Jim

lyman

You should never be doing a select against the base tables. The internal structure is undocumented and subject to change. There are two approaches:

1) Use one of our scripting API's (web services, COM, C++) to go via the search interface and ask the MediaBin server to return the results for you. This is the only approach that, for example, preserves security.

2) Use our specially designed reporting views:

Who is using MediaBin?
ReportUserLogins.

Which assets are they using?
ReportAssetDownloads.
ReportAssetUploads.
ReportAssetsCheckedOut.

What is in the MediaBin Repository?
ReportAssetInventory.
ReportAssetList.
ReportAssetMetaData.

What assets are associated to others?
ReportAssetAssociations.

Your query would join ReportAssetsCheckedOut and ReportAssetMetadata.

See my presentation in DevNet: "Getting Business Data OUt of MediaBin".

Cheers,

Lyman Hurd

Joe_Opland

Hi Lyman,

Sorry for the dumb question this morning. I was still waking up. I have obtained what I need with the query

Select * from dbo.ReportAssetMetadata, dbo.ReportAssetsCheckedOut where PATINDEX ( '%PRODUCT INCORRECT%' , UPPER(metadata_value)) > 0 and dbo.ReportAssetMetadata.asset_id = dbo.ReportAssetsCheckedOut.asset_id

And thank you for your "Gear Up: Getting Business Data Out of MediaBin". I have it on my desk and have been reviewing it.

I have one more query to write and that is all items in a couple given folders, where the Asset Status metadata field reads "Needs Validation".

I should be able to deduce how to do that from your previous two responses, and your "Gear Up: Getting Business Data Out of MediaBin" presentation.

Still, if you have any immediate tips you wish to share, that would be great.

Jim