Endpoint Management and Virtualization Trusted Advisors Community merge into SED TA Community

Hello all,

Hopefully someone can help me understand the error of my SQL here and why it is not working.  Basically I'm trying to report on disk usage by various file types we monitor via the AuditPlus scan.  I'm trying to put together a nice concise report of (to start) computer name, user, count of "All Files" monitored, size of all files, count of "media" files (audio/video/etc), size of all media files, and finally the "business" file sizes.  The problem is that the column which should be showing the media files is actually coming back with a larger sum in many cases that all of the file types put together! For example:

My code:

 select vc.name, vc.[User] AS [Primary User] 
, SUM(allfiles.[Total Files Reported]) as [Total Files]
, SUM(allfiles.[Total FileSizes Kb])/1024 as [Total Size(MB)]
, SUM(media.[Total Files Reported]) as [Total Media Files]
, SUM(media.[Total FileSizes Kb])/1024 as [Total Media Size(MB)]
, (SUM(allfiles.[Total FileSizes Kb]) - SUM(media.[Total FileSizes Kb]))/1024 as [Business Files(MB)]
, ACID._ResourceGuid 

FROM vComputer vc 

JOIN Inv_AeX_AC_Identification acid ON vc.Guid = acid._ResourceGuid
JOIN dbo.Inv_AeX_SW_Disk_Usage media ON vc.Guid = media._ResourceGuid
JOIN dbo.Inv_AeX_SW_Disk_Usage allfiles ON vc.Guid = allfiles._resourceGuid 

WHERE vc.IsManaged = 1
AND DATEDIFF(dd, acid.[Client Date], GETDATE()) < 14
AND (acid.[OS Type] = 'Professional' OR vc.[OS Name] LIKE '%XP%' )
and allfiles.[Extension] LIKE '%'
AND media.[Extension] IN ('AVI','MPG','MOV','M4A','MP3','AAC','MP4','OGG','DIVX','M1V','M2V','MP2','MP4','MPE','MPEG','MPA','AA','WMA','WMV','JPG')

GROUP BY vc.Name, vc.[User], ACID._ResourceGuid 

ORDER BY 4 DESC 

Can anyone make any sense of why this happens?  The only thing I haven't tried yet is making the JOINs to the SW_Disk_Usage be inline sub-SELECT statements with the counts/totals summed by machine/GUID...but that doesn't seem like it would be necessary.  If I comment out either of the Disk_Usage JOINs and associated columns in the SELECT statement, the numbers come back correctly. I suspect I'm just doing something stupid and not seeing it...and maybe now I've just been staring at this for too long!  Any help greatly appreciated!

Hi Kyle,

It's a tricky one this. Your double-join was giving you grief and multiplying up your row count giving you duplicates. 

This will do what you want faster. The data is gathered first in the joins, and then I use a more complex sum to get the counting right. That keeps the row count minimal, and leaves the complex calculation for once all the data has been gathered.

It could be neatened up of course, but I'll leave that job for the enthusiastic...

 select vc.name, vc.[User] AS [Primary User] 

 ,SUM(du.[Total Files Reported]) as [Total Files]

, SUM(du.[Total FileSizes Kb])/1024 as [Total Size(MB)]

, SUM(case when extension 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 extension in ('AVI','MPG','MOV','M4A','MP3','AAC','MP4','OGG','DIVX','M1V','M2V','MP2','MP4','MPE','MPEG','MPA','AA','WMA','WMV','JPG') 

     then du.[Total FileSizes Kb] else 0 end)/1024 as [Total Media Size(MB)]


,SUM(du.[Total FileSizes Kb])/1024 -SUM(case when extension in ('AVI','MPG','MOV','M4A','MP3','AAC','MP4','OGG','DIVX','M1V','M2V','MP2','MP4','MPE','MPEG','MPA','AA','WMA','WMV','JPG') 

     then du.[Total FileSizes Kb] 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_AeX_SW_Disk_Usage 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  

I should have known you'd have the fix Ian...much appreciated!  I was thinking I must be getting duplicates because of the double join, but wasn't sure how to break them out.  I suppose doing inline JOINs and summing up the details into a single value per row would have worked too, but the CASE statement is another good approach.

Thanks again!