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)

MYITFORUM ROCKS!!!

thank guys - the report was a hit in my preso

here is what we used - btw we had to add the ad description field to ad discovery...

select distinct

LEFT(RIGHT(v_ClientDeploymentState.NetBiosName, 9), 8) AS [Asset ID],

MAX(REVERSE(LEFT(REVERSE(system_ou_name0), CHARINDEX('/', REVERSE(system_ou_name0)) -1))) AS [Department],

v_R_System.User_Name0 AS [Assigned To],

v_GS_Computer_system.model0 AS [Model],

v_R_System.Description0 AS [Description],

v_gs_pc_bios.serialnumber0 AS [Serial Number],

v_R_System.AD_Site_Name0 AS [Location]

from v_ClientDeploymentState

inner join v_R_System on (v_ClientDeploymentState.SMSID = v_R_System.SMS_Unique_Identifier0)

inner join v_RA_System_systemouname ON (v_R_System.ResourceID = v_RA_System_systemouname.ResourceID)

inner join v_GS_Computer_system ON (v_R_System.ResourceID = v_GS_Computer_system.ResourceID)

inner join v_gs_pc_bios ON (v_R_System.ResourceID = v_gs_pc_bios.ResourceID)

GROUP BY

v_ClientDeploymentState.NetBiosName,

v_R_System.User_Name0,

v_GS_Computer_system.model0,

v_R_System.Description0,

v_gs_pc_bios.serialnumber0,

v_R_System.AD_Site_Name0


From: admin@lists.myITforum.com [admin@lists.myITforum.com] On Behalf Of Chris Stauffer [cstauffer@myitforum.com]
Sent: Thursday, June 18, 2009 10:44 AM
To: mssms@lists.myitforum.com
Subject: RE: [mssms] SCCM report (computer name, last logged on user, OU,serial number, model)

Max is cool. Would nave never thought of that :-)

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


From: "Jimmy Martin" <Jimmy.Martin@BMHCC.org>
Sent: Thursday, June 18, 2009 10:16 AM
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

The opinions expressed above are not necessarily those of Baptist.

Jimmy Martin

BMHCC - CORPORATE

HS IS-DESKTOP MANAGEMENT

Phone: (901) 227-8209 Fax: (901) 227-3195

www.baptistonline.org

From: admin@lists.myITforum.com [mailto:admin@lists.myITforum.com] On Behalf Of Meringer, Torsten (ext)
Sent: Thursday, June 18, 2009 9:06 AM
To: mssms@lists.myitforum.com
Subject: RE: [mssms] SCCM report (computer name, last logged on user, OU,serial number, model)

 

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

 

From: admin@lists.myITforum.com [mailto:admin@lists.myITforum.com] On Behalf Of Dzikowski, Michael
Sent: Donnerstag, 18. Juni 2009 15:55
To: mssms@lists.myitforum.com
Subject: RE: [mssms] SCCM report (computer name, last logged on user, OU,serial number, model)

 

Ut oh.

 

Greg, you gonna take that?

 

 

From: admin@lists.myITforum.com [mailto:admin@lists.myITforum.com] On Behalf Of Donnie_Taylor@Dell.com
Sent: Thursday, June 18, 2009 9:53 AM
To: mssms@lists.myitforum.com
Subject: RE: [mssms] SCCM report (computer name, last logged on user, OU,serial number, model)

 

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

 

From: admin@lists.myITforum.com [mailto:admin@lists.myITforum.com] On Behalf Of Chris Stauffer
Sent: Thursday, June 18, 2009 8:40 AM
To: mssms@lists.myitforum.com; mssms@lists.myitforum.com
Subject: re: [mssms] SCCM report (computer name, last logged on user, OU,serial number, model)

 

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/


 

Smart Medicine. Inspired Care. And the awards to prove it. Recognized as a Top 50 Healthcare Network. To learn about other recognition and awards Baptist has earned, visit: http://www.bmhcc.org/aboutus/awards/index.asp This message and any files transmitted with it may contain legally privileged, confidential, or proprietary information. If you are not the intended recipient of this message, you are not permitted to use, copy, or forward it, in whole or in part without the express consent of the sender. Please notify the sender of the error by reply email, disregard the foregoing messages, and delete it immediately.


==============
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/

Published with BlogMailr



Trackbacks

No Trackbacks

Comments

No Comments