Help with code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with code

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2011-05-25 : 12:25:34

Hi, I am not a SQL tech at all but I've been asked to write some code to extract information out of a SMS database.

I have this code which does work.

SELECT DISTINCT a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [Office Version],
d.Operating_System_Name_and0,
c.FilePath
FROM v_GS_SoftwareFile c INNER JOIN
v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN
v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN
v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE (c.FileName = 'excel.exe') AND (c.FileVersion like '14.%' )
ORDER BY a.Name0

I need to add "Last Logged On User" to this code and I don't know where to begin. Can someone modify this code and add the Last Logged On User so I can see how it's done?

Thank you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 12:38:44

You will need the table that e.User_Name0 is referring to
in your JOINs

Jim

Everyday I learn something that somebody else already knew

mqh7
Yak Posting Veteran

58 Posts

Posted - 2011-05-25 : 12:40:15

Thanks but since I don't know SQL at all your information does not help me. What does the code/syntax look like?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-25 : 12:59:38

WHere is the user held?
WHere is the logon information held?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mqh7
Yak Posting Veteran

58 Posts

Posted - 2011-05-25 : 13:02:02

Within SMS/SCCM you have a report writing tool and in that tool there are many, many things to choose from. I did find this query syntax.

Views: Columns:
v_GS_COMPUTER_SYSTEM UserName0

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-25 : 13:07:14

In that query it looks like everything is linked by the same ResourceID (a bit odd).
Does v_GS_COMPUTER_SYSTEM have a ResourceID - and does it have a logon time?
It would be a guess whether that is connected to the given query though.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mqh7
Yak Posting Veteran

58 Posts

Posted - 2011-05-25 : 15:53:26

I don't know. I am not a SQL tech. I am only trying to write a report in SMS :-)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-26 : 04:59:09

You need to know something about the database structure to do that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-26 : 06:55:05

Have you changed the original question? If it makes a difference you should post a new one or add a new post to the thread - people aren't going to go back and anyone new might get confused reading the thread.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources