Video Screencast Help

Report for Business and Media Files, Counts and Sizes

Created: 01 May 2012 • Updated: 03 May 2012 | 3 comments

Based on the post by KSchroeder (https://www-secure.symantec.com/connect/forums/infuriating-report-problem) I have amended the report to work on NS7.1.

I've also added to the original File Extention lists using the following

Pictures
'JPG','JPEG','PNG','GIF','BMP','TIF','TIFF','PSD'

Video
'AVI','FLV','M1V','M2V','M4V','MKV','MPG','MPEG','MP2','MP4','MOV','DIVX','XVID','WMV','OGG','WLMP'

Audio
'AAC','M4A','MP1','MP2','MP3','MP4','M4P','OGG','SWA','WMA'

ALL
'JPG','JPEG','PNG','GIF','BMP','TIF','TIFF','PSD','AVI','FLV','M1V','M2V','M4V','MKV','MPG','MPEG','MP2','MP4','MOV','DIVX','XVID','WMV','OGG','WLMP','AAC','M4A','MP1','MP3','M4P','SWA','WMA'

The Updated SQL Report is:

SELECT vc.name, vc.[User] AS [Primary User] 
, SUM(du.[Total Files Reported]) as [Total Files]
, SUM(du.[Total File Sizes (Kilobytes)])/1024 as [Total Size(MB)]
, SUM(case when [File Type] in ('AVI','MPG','MOV','M4A','MP3','AAC','MP4','OGG','DIVX','M1V','M2V','MP2','MP4','MPE','MPEG','MPA','AA','WMA','WMV','JPG') 
	then du.[Total Files Reported] else 0 end) as [Total Media Files]
, SUM(case when [File Type] in ('AVI','MPG','MOV','M4A','MP3','AAC','MP4','OGG','DIVX','M1V','M2V','MP2','MP4','MPE','MPEG','MPA','AA','WMA','WMV','JPG') 
	then du.[Total File Sizes (Kilobytes)] else 0 end)/1024 as [Total Media Size(MB)]
,SUM(du.[Total File Sizes (Kilobytes)])/1024 -SUM(case when [File Type] in ('AVI','MPG','MOV','M4A','MP3','AAC','MP4','OGG','DIVX','M1V','M2V','MP2','MP4','MPE','MPEG','MPA','AA','WMA','WMV','JPG') 
	then du.[Total File Sizes (Kilobytes)] else 0 end)/1024 as [Business Files(MB)]
FROM vComputer vc 
JOIN Inv_AeX_AC_Identification acid ON vc.Guid = acid._ResourceGuid
JOIN dbo.Inv_SW_Disk_Usage_By_File_Type du ON vc.Guid = du._ResourceGuid
WHERE vc.IsManaged = 1
	AND DATEDIFF(dd, acid.[Client Date], GETDATE()) < 14
	AND (acid.[OS Type] = 'Professional' OR vc.[OS Name] LIKE '%XP%' )
GROUP BY vc.Name, vc.[User], ACID._ResourceGuid
ORDER BY [Total Media Files] DESC

Comments 3 CommentsJump to latest comment

ianatkin's picture

Good Job -Hope to see more SQL from you in the future!

Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads

JimChud's picture

i have a question about this, i tried to parameterise this to use individual scripts for file sets however due to the way the database handles the apostrophes it tries to comment them out by adding an additional apostrophe adding two of them then comments it out when it goes in to the DB, adding 3 doesnt help either. along with that i've tried hashing them out with slashes to no avail.

 

any clues? 

Regards Jim.
Connect Etiquette: "Mark as Solution" those posts which resolve your problem and give a thumbs up to useful comments, articles and downloads.

ianatkin's picture

Throw me your report and I'll take a look 

Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads