Web Report SQL help

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Web Report SQL help

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Web Report SQL help

Page: [1]

Message

<< Older Topic Newer Topic >>

Web Report SQL help - 8/27/2008 4:28:55 PM

gkamenjati

Posts: 108
Score: 0
Joined: 6/8/2005
From: San Jose, California
Status: offline

I'm trying to list Last user Logged on

i modified this report to list Manufacturere, Model, Serial Number AD path, Creation Date Obsolete, Client etc... (see red FONT)

SELECT SYS.User_Name0, SYS.Netbios_Name0, Manufacturer0, Model0, SerialNumber0, SYS.User_Domain0, Obsolete0, Client0, Active0, Creation_Date0, Operating_System_Name_and0, ADSPath0, SYS.Resource_Domain_OR_Workgr0
FROM v_R_System SYS INNER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_PC_BIOS.ReosurceID = v_GS_COMPUTER_SYSTEM.ReosurceID
WHERE User_Name0 LIKE @variable
ORDER BY SYS.User_Name0, SYS.Netbios_Name0

I think my problem is in the FROM statement (red Font).

Any help is appreciated.

Post #: 1

RE: Web Report SQL help - 8/27/2008 4:35:57 PM

hcortez463

Posts: 780
Score: 62
Joined: 4/8/2005
Status: offline

u looking for report per collection.. I can post some that i have.

_____________________________

If it Helps, Please rate....

(in reply to gkamenjati)

Post #: 2

RE: Web Report SQL help - 8/27/2008 4:41:12 PM

gkamenjati

Posts: 108
Score: 0
Joined: 6/8/2005
From: San Jose, California
Status: offline

Thank you Mr. Cortez, but I think I want it to be collection independant. My SQL experience is minimal and I do report based on reverse engineering what others has done. in this report I just want to see the last user logged on, which is easy to do, but I need to add the WKS serial number, if active, if client, if obsolete, where in AD.

If you have something similar that would be great.

Otherwise I just need help in fixing the Statement posted earlier.

Thx Again.

(in reply to hcortez463)

Post #: 3

RE: Web Report SQL help - 8/27/2008 5:49:38 PM 1 votes

skissinger

Posts: 2119
Score: 134
Joined: 9/13/2001
From: Sherry Kissinger
Status: offline

Untested, but I saw two things. One was ResourceID was misspelled (oops), and the other was you were trying to join in the pcbios view without first joining it in.

SELECT SYS.User_Name0, SYS.Netbios_Name0, Manufacturer0, Model0, SerialNumber0, SYS.User_Domain0, Obsolete0, Client0, Active0, Creation_Date0, Operating_System_Name_and0, ADSPath0, SYS.Resource_Domain_OR_Workgr0
FROM v_R_System SYS
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
WHERE User_Name0 LIKE @variable
ORDER BY SYS.User_Name0, SYS.Netbios_Name0

_____________________________

mofmaster@smsexpert.com (version 2007) | http://www.smsexpert.com | http://www.sccmexpert.com
My Blog
Microsoft MVP - ConfigMgr

(in reply to gkamenjati)

Post #: 4

RE: Web Report SQL help - 8/27/2008 5:56:23 PM

gkamenjati

Posts: 108
Score: 0
Joined: 6/8/2005
From: San Jose, California
Status: offline

thx Sherry, I'm getting this error now.

The multi-part identifier "v_R_System.ResourceID" could not be bound.

(in reply to skissinger)

Post #: 5

RE: Web Report SQL help - 8/27/2008 6:08:41 PM 1 votes

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

You've got v_R_System aliased as SYS, when you do that, you need to use SYS to reference any v_R_System columns elsewhere in the query. If you use aliases, use them everywhere.

Also, I'm not sure where that ADSPath0 is coming from
SELECT
SYS.User_Name0,
SYS.Netbios_Name0,
cs.Manufacturer0,
cs.Model0,
bios.SerialNumber0,
SYS.User_Domain0,
sys.Obsolete0,
sys.Client0,
sys.Active0,
sys.Creation_Date0,
sys.Operating_System_Name_and0,
-- ADSPath0,
SYS.Resource_Domain_OR_Workgr0
FROM
v_R_System AS SYS
INNER JOIN v_GS_COMPUTER_SYSTEM AS cs
    ON sys.ResourceID = cs.ResourceID
INNER JOIN v_gs_pc_bios AS BIOS
    ON sys.resourceid = bios.resourceID
WHERE    User_Name0 LIKE @variable
ORDER BY SYS.User_Name0,
        SYS.Netbios_Name0

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to gkamenjati)

Post #: 6

RE: Web Report SQL help - 8/28/2008 11:50:51 AM

gkamenjati

Posts: 108
Score: 0
Joined: 6/8/2005
From: San Jose, California
Status: offline

John, your Code worked perfectly and in regards to ADSPath0, it is SYS.ADSPath0,

Sherry, your corrections made a big difference and I thank you for that.

Merci Beaucoup mes amis.

(in reply to jnelson993)

Post #: 7