Deployment and Imaging Group

 View Only
  • 1.  Report for Business and Media Files, Counts and Sizes

    Posted May 01, 2012 09:30 AM

    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


  • 2.  RE: Report for Business and Media Files, Counts and Sizes

    Trusted Advisor
    Posted May 03, 2012 12:28 PM

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



  • 3.  RE: Report for Business and Media Files, Counts and Sizes

    Posted May 04, 2012 09:00 AM

    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? 



  • 4.  RE: Report for Business and Media Files, Counts and Sizes

    Trusted Advisor
    Posted May 04, 2012 11:37 AM

    Throw me your report and I'll take a look