Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SCCM query filename and OU

Posted on 2010-09-07
3
Medium Priority
?
1,266 Views
Last Modified: 2013-11-21
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

0
Comment
Question by:EfrenM
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
TarekIsmail earned 2000 total points
ID: 33621993
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
 
LVL 6

Expert Comment

by:TarekIsmail
ID: 33622119
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
 
LVL 2

Author Closing Comment

by:EfrenM
ID: 33622304
works great, creates duplicates but thats easily fixed with trusty excel :) , thanks a mill spent all day trying to get it to work
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The environment that this is running in is SCCM 2007 R2 running on a Windows 2008 R2 server. The PXE Distribution point is running on its own Windows 2008 R2 box. This is what Event viewer showed after trying to start the WDS service:  An erro…
Remote Apps is a feature in server 2008 which allows users to run applications off Remote Desktop Servers without having to log into them to run the applications.  The user can either have a desktop shortcut installed or go through the web portal to…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question