From: admin@lists.myITforum.com [mailto:admin@lists.myITforum.com] On Behalf Of Tev Sanders
Sent: Thursday, June 18, 2009 2:04 PM
To: mssms@lists.myitforum.com
Subject: RE: [mssms] SCCM report (computer name, last logged on user, OU,serial number, model)

NICE!

SELECT DISTINCT

                      dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_R_System.User_Name0, MAX(dbo.v_RA_System_SystemOUName.System_OU_Name0) AS Expr1,

                      dbo.v_GS_COMPUTER_SYSTEM.Description0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0,

                      dbo.v_GS_PC_BIOS.SerialNumber0

FROM         dbo.v_GS_COMPUTER_SYSTEM INNER JOIN

                      dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN

                      dbo.v_RA_System_SystemOUName ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_RA_System_SystemOUName.ResourceID INNER JOIN

                      dbo.v_GS_PC_BIOS ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_GS_PC_BIOS.ResourceID

GROUP BY dbo.v_GS_COMPUTER_SYSTEM.Name0, dbo.v_R_System.User_Name0, dbo.v_GS_COMPUTER_SYSTEM.Description0,

                      dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_PC_BIOS.SerialNumber0

 

You can use MAX(system_ou_name0) to get the "real" OU.

 

 

Ut oh.

 

Greg, you gonna take that?

 

 

 

OU will skew the report because it will show up odd.  For example, if your OU is Blah.Dell.Com\Austin\Computers\Workstations\GregSucks then the database stores it as

Blah.Dell.Com\Austin

Blah.Dell.Com\Austin\Computers

Blah.Dell.Com\Austin\Computers\Workstations

Blah.Dell.Com\Austin\Computers\Workstations\GregSucks

 

It is stored in v_ra_system_systemouname if you really want to wade into it.

 

Disclaimer:  All OU names in the above example are purely hypothetical.  Really, they are.  Really. J

 

Donnie Taylor | Systems / Configuration Management Engineer | Dell Inc.
512-769-4316 | 512-728-8149 | donnie_taylor@dell.com

 

 

Here is most of what you.

cant find the OU for some reason this morning :-) i'll keep looking.

SELECT     v_R_System.User_Name0 AS User_name, v_GS_COMPUTER_SYSTEM.Domain0 AS PC_Domain, v_GS_COMPUTER_SYSTEM.Manufacturer0 AS PC_Manufacture, v_GS_COMPUTER_SYSTEM.Model0 AS PC_Model, v_GS_PC_BIOS.SerialNumber0 AS PC_Serial_number, v_R_System.Operating_System_Name_and0 AS OS_Name, v_GS_OPERATING_SYSTEM.CSDVersion0 AS Service_pack_number, v_GS_OPERATING_SYSTEM.Description0 AS Computer_Description, v_GS_COMPUTER_SYSTEM.Status0 AS Client_Status
FROM v_GS_COMPUTER_SYSTEM INNER JOIN v_R_System ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN v_GS_PC_BIOS ON v_GS_OPERATING_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID
WHERE     (v_GS_COMPUTER_SYSTEM.Name0 Like @MachineName)



Thanks,
Christopher Stauffer <><
Enterprise SMS Admin
MCTS ConfigMgr 2007
MCP SMS 2003
Email: CStauffer@myitforum.com
Blog: http://myitforum.com/cs2/blogs/cstauffer/

From: "Tev Sanders" <tevs@microsoft.com>
Sent: Thursday, June 18, 2009 9:16 AM
To: "mssms@lists.myitforum.com" <mssms@lists.myitforum.com>
Subject: [mssms] SCCM report (computer name, last logged on user, OU,serial number, model)

Anyone have a report with the following columns (computer name, last logged on user, OU of PC,description,serial number, model, )? If not, are there any sql/reporting experts out there that can write the report?

 

If you could please export the mof and send to me so that we can import.

 

Thanks!

Tev

 

PS no warranties....

==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/

==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/

==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/

==============================================================================CONFIDENTIALITY NOTICE: This email contains information from the sender that may be CONFIDENTIAL, LEGALLY PRIVILEGED, PROPRIETARY or otherwise protected from disclosure. This email is intended for use only by the person or entity to whom it is addressed. If you are not the intended recipient, any use, disclosure, copying, distribution, printing, or any action taken in reliance on the contents of this email, is strictly prohibited. If you received this email in error, please contact the sending party by reply email, delete the email from your computer system and shred any paper copies. Note to Patients: There are a number of risks you should consider before using e-mail to communicate with us. See our Privacy Policy and Henry Ford My Health at www.henryford.com for more detailed information. If you do not believe that our policy gives you the privacy and security protection you need, do not send e-mail or Internet communications to us. ==============================================================================

==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/

==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/