• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1686
  • Last Modified:

SCCM Sql Query to list "currently live" computers

Is there a way of listing all the "live" computers in SCCM Sql. When I do the below I get duplicate serial numbers where computers were reimaged.

select a.MachineID, b.SerialNumber00 As BIOSSN, e.SerialNumber00 As EncSN, a.*
from Computer_System_DATA as a
left join       System_Enclosure_Data As e On a.machineID = e.MachineID
left join       PC_BIOS_Data As b On a.MachineID = b.MachineID

Any idea what where clause I could add to get this listing only computers which are currently alive ?

thanks
0
plq
Asked:
plq
  • 5
  • 3
2 Solutions
 
merowingerCommented:
Better way is to make sure that duplicate computer get merged automatically
http://ckrim.blogspot.com/2010/12/obsolete-objects-reuse-your-old-sccm.html
0
 
plqAuthor Commented:
Hmmm well these are customer's SCCM's and I can't change their policy. However there's a good clue in that link you pasted:

               >>> And that is exactly what happens when a client is re-imaged; A new record is created and the “old” record is marked as “Obsolete”.

I just need to find that obsolete marker - any idea which database Table/Field that might be in ??

thanks
 
0
 
merowingerCommented:
v_R_System contains the information
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
KezziCommented:
System_AUX_info is the direct SQL table you're looking for.
0
 
plqAuthor Commented:
Yes thats got the Obsolete0 field - do you know how it could join to computer_system_data ?

I could join on netbios_name but I guess there might be 2 computers with the same name potentially, so a direct link to machineid somehow would be cool

thanks
0
 
merowingerCommented:
machineid to resourceid
0
 
plqAuthor Commented:
System_AUX_info has ItemKey, SMBIOS_GUID0, Hardware_ID0, and Netbios_Name0

Not resource id ??
0
 
plqAuthor Commented:
Looking at the design of vComputerSystemData the MachineID joins to ItemKey, so I think thats what I need

Will post back if I get stuck again but thats probably all I need to finish this.. thanks
0
 
plqAuthor Commented:
thank you
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now