SCCM Report to collect video file information

collecting video file information using SCCM Report

1) add video file extension in inventory collection under software Inventory client agents properties as shown below.

once added all required extension in inventory collection, run software inventory client agent on all systems.

2) Create SCCM Report now, ( i used SQL Server Business intelligence studio configured already)

 In solution explorer, right click -> Reports -> Add new report

 Next

Next

Copy SQL Query into : query string and click on Query Builder ( sql query available below )

SQL Query : 

SELECT     SYS.Netbios_Name0 AS 'System Name', SYS.User_Name0 AS 'User Name', SF.FileName, SF.FilePath, SF.FileSize AS 'FileSize(Byte)', SF.FileModifiedDate,

FCM.CollectionID

FROM         v_GS_SoftwareFile AS SF INNER JOIN

v_R_System AS SYS ON SF.ResourceID = SYS.ResourceID INNER JOIN

v_FullCollectionMembership AS FCM ON FCM.ResourceID = SYS.ResourceID

WHERE     (SF.FileName LIKE '%.avi%'

or sf.filename like '%.3gp'

or sf.filename like '%.flv'

or sf.filename like '%.mov'

or sf.filename like '%.mp4'

or sf.filename like '%.mpg'

or sf.filename like '%.mpeg'

or sf.filename like '%.wmv'

or sf.filename like '%.divx'

or sf.filename like '%.mkv') AND (FCM.CollectionID = @CollectionID)

ORDER BY 'FileSize(Byte)'

Select check box : CollectionID and enter OK

Select CollectionID check box 

Enter the name of the Report

OUTPUT : 

Any help required : prakaash2109@gmail.com