Solved

SCCM query filename and OU

Posted on 2010-09-07
3
1,211 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 500 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

The curse of the end user strikes again      

You’ve updated all your end user’s email signatures. Hooray! But guess what? They’re playing around with the HTML, adding stupid taglines and ruining the imagery. Find out how you can save your signatures from end users today.

Join & Write a Comment

##the calculator has been updated to version 1.6 please download the use the updated version## Hi there, After the previous post of the original version of the calculator here : http://www.experts-exchange.com/articles/OS/Microsoft_Operatin…
On a regular basis I get questions about slow RDP performance, RDP connection problems, strange errors and even BSOD, remote computers freezing or restarting after initiation of a remote session. In a lot of this cases the quick solutions made b…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now