SCCM query filename and OU

Hi All
need some help i cant get it working, i am using a existing report to track a file by its name it works fine but i need it to show the OU the computer is in. i tried but failed :( any suggestions?  below is the sql query
select SYS.Netbios_Name0, SF.FileName, SF.FileDescription, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath
From v_GS_SoftwareFile SF
join v_R_System SYS on SYS.ResourceID = SF.ResourceID
Where SF.FileName LIKE @variable
ORDER BY SYS.Netbios_Name0

Open in new window

LVL 2
EfrenMAsked:
Who is Participating?
 
TarekIsmailConnect With a Mentor Commented:
Hi
this is the query you need to use:

SELECT v_R_System.Netbios_Name0, v_GS_SoftwareFile.FileName, v_GS_SoftwareFile.FileDescription, v_GS_SoftwareFile.FileVersion, v_GS_SoftwareFile.FileSize, v_GS_SoftwareFile.FileCount, v_GS_SoftwareFile.FilePath,v_RA_System_SystemOUName.System_OU_Name0
FROM   v_GS_SoftwareFile  join v_R_System
on  
v_R_System.ResourceID = v_GS_SoftwareFile.ResourceID
 inner JOIN v_RA_System_SystemOUName
on v_RA_System_SystemOUName.ResourceID = v_GS_SoftwareFile.ResourceID
where v_GS_SoftwareFile.FileName ='notepad.exe'
ORDER BY v_R_System.Netbios_Name0
0
 
TarekIsmailCommented:
Hi EfrenM,

two points you need to take care about them :

enable Active Directory System Group Discovery, to get your computer OU
computers located in Computers containers will not be listed in v_RA_System_SystemOUName view


Capture.PNG
0
 
EfrenMAuthor Commented:
works great, creates duplicates but thats easily fixed with trusty excel :) , thanks a mill spent all day trying to get it to work
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.