myITforum.com : Designing SMS Web Reports with SQL Query Builder

Okay, first off, I'm not a SQL guru. If you can read the below SQL statement and understand what's going on enough to do it on your own with a piece of paper and a pencil this article is not for you. For those who, like me, would rather watch paint dry than do the preceding read on!

SELECT DISTINCT
TOP 100 PERCENT SYS.Name0, IP.IP_Addresses0, NETW.MACAddress0, IPSUB.IP_Subnets0, NETW.IPSubnet0, NETW.DefaultIPGateway0, NETW.DHCPEnabled0, NETW.DHCPServer0
FROM dbo.v_RA_System_IPSubnets IPSUB INNER JOIN dbo.v_R_System SYS ON IPSUB.ResourceID = SYS.ResourceID INNER JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NETW ON SYS.ResourceID = NETW.ResourceID INNER JOIN dbo.v_RA_System_IPAddresses IP ON IPSUB.ResourceID = IP.ResourceID INNER JOIN dbo.v_RA_System_SystemOUName ON IP.ResourceID = dbo.v_RA_System_SystemOUName.ResourceID
WHERE (SYS.Client0 = 1) AND (NOT (NETW.DefaultIPGateway0 = 'NULL')) AND (NOT (IP.IP_Addresses0 = 'NULL')) AND (NOT (IP.IP_Addresses0 = '0.0.0.0')) AND (System_OU_Name0 = @OU)ORDER BY SYS.Name0

SMS web reports in general are extremely powerful, but the SQL statements required for customizing the reports or creating new reports from scratch to cater to individual needs can be a daunting task for those of us who are not "SQL smart".

So what's a fella who didn't make it through the 3rd Chapter of "SQL for Dummies" to do?! SQL Query Builder to the rescue!

Alright, that may be an oversimplification of learning SQL and I did make it through the entire book, but still, Query Builder makes life a lot easier than memorizing key words and fighting with SQL all day to get the report your manager wants to see.

I use this technique basically because due to my lack of in depth SQL knowledge it's the best way for me to do it. Be warned though that as Rick Jones (SQL guru-type guy) says sometimes this method will work with simple SQL statements "until you try to do something fancy and it puketh". Fearing missed deadlines and SQL itself more than the "puketh factor" I'm content to take the risk this way.

To get into Query Builder (or Query Designer) just open up SQL Enterprise Manager, expand your SQL Server instance until you get down to your SMS database name. Expand your SMS database name, right click on Views, then select new view from the context menu.

You are rewarded with a blank new view. Click on the add table button (top right of the toolbar menu) then click on the Views tab. This is what you want to query--the views not the tables. There are complications when using tables compared to views for permissions issues, for now just take my word for it.

Scrolling through the views you can pretty much guess what is in them from their names. For this article I'm going to explain the SQL query that started it all above. Scroll down the Views list and select V_RA_System_IPSubnets, click add to get the view on the playing field. Repeat this action for the v_R_System, v_GS_NETWORK_ADAPTER_CONFIGUR, v_R_System_IPAddresses, V_RA_SystemOUName and then drag the resource ID property from one table to the next to connect each table so we get related information for each system--or just cheat and C&P the SQL statement above into the query builder frame over the SELECT and FROM keywords. If you try to execute this query now you'll get an error because of the @OU prompt, just delete this part of the WHERE clause for now: AND (System_OU_Name0 = @OU).

Now go ahead and run the query--hit the ! button on the top menu bar. Viola! You should see a ton of system names, MAC, and TCP/IP Information. Since we've taken out the part about the OU name we won't see that here. If you check the box next to System_OU_Name0 in the v_RA_System_SystemOUName table and run it again you get all the other stuff plus an OU name for each system.

Now you've got the basic SQL query ready for some customization--you really didn't want to see all that information. For example, if a system has multiple network cards, you only need to see the ones that have IPs right? This report was created as part of a DHCP migration so I don't need to worry about NICs that aren't on the network right now.

It's time to add some criteria to the query. Pick the field that you want to sort your output by and click on the right side of the "Sort Type" column. I chose name0 and sorted the information Ascending. This will give me a resulting output of all the information I'm after sorted A-Z by computername. Next, lets get rid of those 0.0.0.0 IP addresses for systems with NICs that aren't on the network. Click on the "Criteria" column of IP_Addresses0 and DefaultIPGateway0 and type in NOT = 'NULL'. Hit ! again to run the query and you should notice a difference from the first run.

Now either save this View or cut and paste everything into an instance of notepad and exit query builder. We're almost done, wake up in the back!

Time to make the report. Right click on reports and select new report. Give it a name, a category, and then hit the Edit SQL Statement... button. This is where we get even with the SQL gurus!!! Just copy and paste the SQL statement we made in Query Builder into this box. Now hit the Prompt button. Type in OU for the name and prompt text to your liking. Now hit the provide a SQL statement button and type this in: SELECT DISTINCT System_OU_Name0 FROM v_RA_System_SystemOUName. You should recognize SystemOUName0 as the field in the V_RA_System_SystemOUName table from earlier--if not you can always go back and play with Query Builder to double check it. Click OK and OK.

So now you've got the query and the prompt created. Next, we have to tell this report to use the prompted value right?! Remember the part we cut out of the original statement? Time to put it back. At the end of the WHERE clause add in an AND keyword and follow it with System_OU_Name0=@OU. Remember we named our prompt OU and the @ symbol is what is used to identify prompts to SQL in web reports. So in other words, show me this report where someone has picked a prompted OU out of all the possible OUs.

Click OK to exit out of the report creation process and then run the report from your reports folder in the SMS admin console. You should be given a prompt for an OU with whatever prompt text you put in for the prompt properties. Clicking on values in the web report should reward you with a list of OU names to choose to run your new report on. Just click on display to see the fruits of your SQL labor come to life.

There! All done. Once you start playing with Query Builder you'll be able to do custom web reports with prompts in less time than it took for me to write this article!


		myITforum.com ©2010 \| Legal \| Privacy