Query to Web Reporting

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:

Query to Web Reporting

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

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> Web Reporting >> Query to Web Reporting

Page: [1]

Message

<< Older Topic Newer Topic >>

Query to Web Reporting - 5/6/2008 12:55:32 PM

mcharves

Posts: 10
Score: 0
Joined: 5/6/2002
From: Pasco, WA State, USA
Status: offline

I'm trying to convert some of our Queries into Web Reports, and having a devil of a time figuring out exactly how to convert some of the SQL code to work in Web Reporting. I've done some searching on the forums, but am not really sure I understand how to resolve my issue after sorting through some of the resulting posts of my search.

I've basically built (or am trying to build) a report that allows a Web Reporting user to select a computer model and then display the number of computers on the network that match that model number. My problem is the "System_DISC" table issue, and I'm not sure I'm grasping how to alter that from the table to the views.

My SQL code is :

select v_R_System.ItemKey,
v_R_System.Netbios_Name0,
v_R_System.User_Name0,
v_G_System_COMPUTER_SYSTEM.Manufacturer00,
v_G_System_COMPUTER_SYSTEM.Model0,
v_G_System_X86_PC_MEMORY.TotalPhysicalMemory0,
v_G_System_PROCESSOR.Name0,
v_G_System_OPERATING_SYSTEM.Caption0,
v_R_System.AD_Site_Name0
from System_DISC AS v_R_System
INNER JOIN Computer_System_DATA AS v_G_System_COMPUTER_SYSTEM ON v_G_System_COMPUTER_SYSTEM.MachineID = v_R_System.ItemKey
INNER JOIN PC_Memory_DATA AS v_G_System_X86_PC_MEMORY ON v_G_System_X86_PC_MEMORY.MachineID = v_R_System.ItemKey
INNER JOIN Operating_System_DATA AS v_G_System_OPERATING_SYSTEM ON v_G_System_OPERATING_SYSTEM.MachineID = v_R_System.ItemKey
INNER JOIN Processor_DATA AS v_G_System_PROCESSOR ON v_G_System_PROCESSOR.MachineID = v_R_System.ItemKey
where v_G_System_COMPUTER_SYSTEM.Model0 like '@variable'

I can put that into the SQL Query Analyzer and it will run fine (provided of course that I change the '@variable' to an actual model number). Can someone explain what I need to do to change the System_DISC to a view format?

Thanks a lot in advance...

_____________________________

Mike Charves
SMS Administration
Bechtel National, Inc.

Post #: 1

RE: Query to Web Reporting - 5/6/2008 1:03:32 PM

jmlewis

Posts: 39
Score: 2
Joined: 7/28/2007
Status: offline

first you dont want to alter the table. you want to keep the integrity of your table data intact. you can however create or modify the view. are you creating the prompt to prompt you for your variable in your sms report?

(in reply to mcharves)

Post #: 2

RE: Query to Web Reporting - 5/6/2008 1:23:04 PM

mcharves

Posts: 10
Score: 0
Joined: 5/6/2002
From: Pasco, WA State, USA
Status: offline

Yes, and I got that section to work great... with and without wildcards.

And no, I definately don't want to alter my table... am I risking doing that with the above query?

_____________________________

Mike Charves
SMS Administration
Bechtel National, Inc.

(in reply to jmlewis)

Post #: 3

RE: Query to Web Reporting - 5/6/2008 1:29:00 PM

jmlewis

Posts: 39
Score: 2
Joined: 7/28/2007
Status: offline

no not at all. i just thought you mentioned that in the previous post. what exactly is the problem you are having? i was able to take this query, and it run fine in a report

(in reply to mcharves)

Post #: 4

RE: Query to Web Reporting - 5/6/2008 1:48:55 PM

mcharves

Posts: 10
Score: 0
Joined: 5/6/2002
From: Pasco, WA State, USA
Status: offline

If I take the above query and add it to a Web Report, when I try to run that report I get the following error message :

SELECT permission denied on object 'System_DISC', database '[db name]', owner '[db owner]'.

< Message edited by mcharves -- 5/6/2008 1:49:20 PM >

_____________________________

Mike Charves
SMS Administration
Bechtel National, Inc.

(in reply to jmlewis)

Post #: 5

RE: Query to Web Reporting - 5/6/2008 1:50:44 PM

jmlewis

Posts: 39
Score: 2
Joined: 7/28/2007
Status: offline

you need to add permissions for the webreport_approle the database so that when you run these reports you can view them

(in reply to mcharves)

Post #: 6

RE: Query to Web Reporting - 5/6/2008 2:14:03 PM

jnelson993

Posts: 794
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

NO NO NO!!! DANGER!!! DON'T LISTEN TO JMLEWIS!!!

I'm sorry, this is just the wrong answer! I'm forming a response, I'll be done in a minute...

_____________________________

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

(in reply to jmlewis)

Post #: 7

RE: Query to Web Reporting - 5/6/2008 2:16:18 PM

jschoone

Posts: 358
Score: 25
Joined: 4/10/2004
From: Norway
Status: offline

Hi!

The views are are created and optimized for use with Web-reports. The table System_DISC should not be used directly by the reports but the v_R_System view should be queried in stead!

I edited your query to use only the views (OBS I have no server here to try it):

SELECT v_R_System.ResourceID,
v_R_System.Netbios_Name0,
v_R_System.User_Name0,
v_GS_COMPUTER_SYSTEM.Manufacturer00,
v_GS_COMPUTER_SYSTEM.Model0,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,
v_GS_PROCESSOR.Name0,
v_GS_OPERATING_SYSTEM.Caption0,
v_R_System.AD_Site_Name0
FROM v_R_System
INNER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_PROCESSOR ON v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID
where v_GS_COMPUTER_SYSTEM.Model0 like '@variable'

Hope this helps you to get started.

Jan

(in reply to jmlewis)

Post #: 8

RE: Query to Web Reporting - 5/6/2008 2:18:34 PM

jmlewis

Posts: 39
Score: 2
Joined: 7/28/2007
Status: offline

nevermind. i see what you are saying about the tables and views. i assumed they were views because of the way that they were getting renamed in the sql code. my bad.

< Message edited by jmlewis -- 5/6/2008 2:28:24 PM >

(in reply to jnelson993)

Post #: 9

RE: Query to Web Reporting - 5/6/2008 2:33:20 PM

jnelson993

Posts: 794
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

quote:

what is the danger in giving select permissions to the tables as i suggested? please let me know as my environment was setup this way when i came into the sms world and we have had no problems with it since.

The permissions on the SMS views are already set up properly for web reporting so to go adding explicit permissions to the underlying tables is unecessary. It can be an administrative pain to have to manage all of those explicit permissionsIt also gives people the impression that selcting from the tables directly is OK. It's not. MS does not support direct querying of the tables as they reserve the right to change around tables/columns at will and if your reports rely on a table/column name you might find things not working right after a patch or service pack. MS people have told me that that SQL is more likely to run into locking/contention problems when selecting from tables directly as opposed to selecting from views. I don't claim to understand the factors involved in that, I just know the SQL purists always tell you to select from views, not tables and I don't like messing with the SQL purists cuz they'll kick you in the junk if you don't follow the rules.

Now that being said, it technically WILL work to do what you've done, but there's just no reason why and it's less than ideal. The last thing the support community needs are a bunch of query examples out there that go directly against the tables, so let's all just use the supported views...that's my opinion anyway.

So if it were me writing a report like you're asking for, I'd do it a little something like this:
(no special permissions needed, no direct tables used, and I'm assuming you have a prompt in your report called MODEL)

SELECT DISTINCT
sys.resourceID,
sys.netbios_name0 AS ComputerName,
sys.user_name0 AS UserName,
cs.Manufacturer0 AS Make,
cs.Model0 AS Model,
cs.TotalPhysicalMemory0 AS Memory,
cpu.Name0 AS Processor,
os.Caption0
FROM
dbo.v_R_System AS sys
INNER JOIN dbo.v_GS_Computer_System AS cs
     ON sys.ResourceID = cs.ResourceID
LEFT JOIN dbo.v_GS_Operating_System AS os
     ON sys.ResourceID = os.ResourceID
LEFT JOIN dbo.v_GS_Processor AS cpu
     ON sys.ResourceID = cpu.ResourceID
WHERE
cs.Model0 LIKE @Model

_____________________________

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

(in reply to jmlewis)

Post #: 10

RE: Query to Web Reporting - 5/6/2008 2:43:01 PM

jnelson993

Posts: 794
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

quote:

SELECT v_R_System.ResourceID,
v_R_System.Netbios_Name0,
v_R_System.User_Name0,
v_GS_COMPUTER_SYSTEM.Manufacturer00,
v_GS_COMPUTER_SYSTEM.Model0,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0,
v_GS_PROCESSOR.Name0,
v_GS_OPERATING_SYSTEM.Caption0,
v_R_System.AD_Site_Name0
FROM v_R_System
INNER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY ON v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_PROCESSOR ON v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID
where v_GS_COMPUTER_SYSTEM.Model0 like '@variable'

The only real problems I see here are

The '@variable' wrapped in single quotes, that should have no quotes
The join to V_GS_X86_PC_MEMORY is unecessary because we've already got that information in v_GS_Computer_System in the TotalPhysicalMemory0 field
There needs to be a GROUP BY or a DISTINCT in order to remove the duplicate records that comes back when joining to the V_GS_PROCESSOR view. For machines with multiple/hyperthreaded cores you will get multiple records per machine. A DISTINCT/GROUP BY would alleviate that problem.

_____________________________

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

(in reply to jschoone)

Post #: 11

RE: Query to Web Reporting - 5/6/2008 3:27:39 PM

mcharves

Posts: 10
Score: 0
Joined: 5/6/2002
From: Pasco, WA State, USA
Status: offline

Thanks a lot for all your help... your responses cleared up a lot of grey area I had regarding database table access and working around WQL and SQL to get what I needed. The example(s) you provided helped immensely and I was able to edit them to suit my needs.

Thanks again!

_____________________________

Mike Charves
SMS Administration
Bechtel National, Inc.

(in reply to jnelson993)

Post #: 12