Need to show FileName in output

1

Can someone direct me on this script, I need to add the FIleName into the output as well, need this asap.
select Sys.Name0, Sys.User_Name0, SF.FilePath,

Count(SF.FileName) as FileCount,
Sum(SF.FileSize/1024/1024) as 'Used Space'

from v_R_System Sys INNER JOIN v_GS_SoftwareFile SF on
Sys.ResourceID = SF.ResourceID
where SF.FilePath like '%'+'Users'+'%' +'%documents%' and SF.FileSize > 0 Group by Sys.Name0, Sys.User_Name0, SF.FilePath

having Count(SF.FileName) > 0 order by Sys.Name0 Desc

2

Just add SF.FileName to your select list and sort as desired.

1 Like
3

That worked thanks, how would I list by particular file extensions?

4

You;ll have to split out the extensions into a new column (e.g. right(FileName, 4) as Ext) and include that in your select and sort

5

Ok, can you give me an example?

6

Something like this (untested):

SELECT Sys.name0, 
       Sys.user_name0, 
       SF.filepath, 
       Count(SF.filename)             AS FileCount, 
       Sum(SF.filesize / 1024 / 1024) AS 'Used Space' 
       RIGHT(SF.filename, 3)          AS FileExtension
FROM   v_r_system Sys 
       INNER JOIN v_gs_softwarefile SF 
               ON Sys.resourceid = SF.resourceid 
WHERE  SF.filepath LIKE '%' + 'Users' + '%' + '%documents%' 
       AND SF.filesize > 0 
GROUP  BY Sys.name0, 
          Sys.user_name0, 
          SF.filepath
          RIGHT(SF.filename, 3)
HAVING Count(SF.filename) > 0 
ORDER  BY Sys.name0 DESC
7

SQL Query does not recognize RIGHT.. and where do I put my criteria?

8

missing comma in the GROUP BY clause after SF.filepath

9

No, still getting error
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'RIGHT'.

10

post the entire query you are using. e.g. this works:

select abc.a, abc.b, right(abc.c ,3)
from (select a,b,c from (values('a', 'b', 'c.ext')) v(a,b,c)) abc
group by abc.a, abc.b, right(abc.c ,3)
11

select Sys.Name0, Sys.User_Name0, SF.FilePath, SF.Filename,

Count(SF.FileName) as FileCount,
Sum(SF.FileSize/1024/1024) as 'Used Space'

from v_R_System Sys INNER JOIN v_GS_SoftwareFile SF on
Sys.ResourceID = SF.ResourceID
Where SF.FilePath like '%'+'Users'+'%' +'%documents%'
and SF.FileSize > 0
And ( FileName Not Like '%.exe'
Or FileName Not Like '%.dll'
Or FileName Not Like '%.ico'
Or FileName Not Like '%.ini'
Or FileName Not Like '%.reg'
Or FileName Not Like '%.log'
Or FileName Not Like '%.xml'
Or FileName Not Like '%.msi' )
Group by Sys.Name0, Sys.User_Name0, SF.Filename, SF.FilePath

having Count(SF.FileName) > 0 order by Sys.Name0 Desc

Not working... EXE removed, but nothing else

12

I don't see RIGHT in your query anywhere.

Also note that:

FileName Not Like '%.exe' 
Or FileName Not Like '%.dll'

means include everything. that is every filename matches this test! (If it is a.exe, it matches not like '%.dll". if it is a.dll, it mathces not like '%.exe') If it is something else, it is not like either.

13

Sorry,

select Sys.Name0, Sys.User_Name0, SF.FilePath, SF.Filename,

Count(SF.FileName) as FileCount,
Sum(SF.FileSize/1024/1024) as 'Used Space'

from v_R_System Sys INNER JOIN v_GS_SoftwareFile SF on
Sys.ResourceID = SF.ResourceID
where SF.FilePath like '%'+'Users'+'%' +'%documents%'
and SF.FileSize > 1
AND RIGHT(FileName,4) NOT IN('.exe', '.dll', '.ico', '.msi', '.ini', '.log', '.xml', 'reg' )
Group by Sys.Name0, Sys.User_Name0, SF.Filename, SF.FilePath

having Count(SF.FileName) > 0 order by Sys.Name0 Desc

14

THis is working, I would like to also only see files bigger then 1 mb, is there a way to do this?

15

depends. do you have sizes available in your v_R_System table?

16

It is in v_GS_SoftwareFile called filesize, I use it as 'Used Space'