Sum of HD space used by file type

The question below was posted within the Newsgroups. This is a typical question asked by Management, Servers or Exchange teams.

 

Managements reasons for know varies from “just because” to “Who is in violation of X Policy”

 

Server Team wants to know where they can clean up hard drive space. aka who is using all our HD space?

 

Exchange Team wants to know, How big are all the PST out there.

 

In all case this is a good example of how SMS can be used to help these team understand your environment.

 

I have a report that returns all MP3s on our users’ workstations, but
what I would like to do is to alter it so that it shows only the disk
space used by MP3s.  The purpose is to try to find out who is filling
their hard disks up with MP3s.  I have looked all over the place but I
cannot work out the syntax (my SQL ain’t brilliant)

 

Within this SQL query I will use *.EXE but it could be any file as long as you are collecting that information about it use.

 

 

select

            v_GS_COMPUTER_SYSTEM.Name0 as ‘PC Name’,

            v_GS_COMPUTER_SYSTEM.UserName0 as ‘User Name’,

            sum(v_GS_SoftwareFile.fileSize/1024) as ‘Size in KB’

from

            v_GS_SoftwareFile,

            v_GS_COMPUTER_SYSTEM

Where

            v_GS_SoftwareFile.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID

            and v_GS_SoftwareFile.FileName like (‘%.exe’)

Group by

            v_GS_COMPUTER_SYSTEM.Name0,

            v_GS_COMPUTER_SYSTEM.UserName0

Order By

            v_GS_COMPUTER_SYSTEM.Name0,

            v_GS_COMPUTER_SYSTEM.UserName0

Leave a Reply

Your email address will not be published. Required fields are marked *