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