Solved

Custom SMS Report

Posted on 2008-06-11
7
296 Views
Last Modified: 2013-11-21
Hello Experts,

I need a custom report for SMS 2003. I know how to creat a new report, but that is where I hit the wall. I have no idea how to write the SQL queries needed.

I need a report that will:

Return system names from a selected Collection.
Return the Model information of each system.

Right now this is all i need, but I will want to add more info to the report in the future. So can you please explain the parts of the query and include the lines to add Total HD Space, Used HD Space, Total RAM, and Processor Name.

Thanks.
0
Comment
Question by:james-stuart
  • 4
  • 3
7 Comments
 
LVL 29

Expert Comment

by:matrixnz
ID: 21774967
First you need to specify what you wish to query for, in your case you wish to query Computers "Netbios Name" and "Model".  Lets refer to these as "Criteria"

We know that the "Criteria" is also based on computer resources or system resources.

So question is where do you get the Netbios Name and Model from within inside the Report View:
 - The Netbios Name can be found inside v_R_System under the Column Netbios_Name0
 - The Model Name can be found inside v_GS_COMPUTER_SYSTEM under the Column Model0

So we start the SQL Query,
Specify the Criteria (Note: values should be separated by a comma)
SELECT
v_R_System.Netbios_Name0, v_GS_COMPUTER_SYSTEM.Model0
 
Specify the "Object Type"
FROM v_R_System
 
Join the v_GS_COMPUTER_SYSTEM to the Object Type "v_R_System"
JOIN v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID

Now we need to select what systems are we to query, you specified you'd like to query a Collection, Collections can be found under v_FullCollectionMembership, so we need to join the v_FullCollectionMembership to the Object Type "v_R_System" also.
JOIN v_FullCollectionMembership ON v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
 
Now there are two methods to specify the Collection, we can hard code it (if you only want to run this against one particular collection) or you can have it prompt for the name.

If you wish to hardcode it then you could use the following for example to specify "All Systems" Collection:
WHERE v_FullCollectionMembership.CollectionID = 'SMS00001'

Alternatively if you wish to prompt for a collection then follow these steps:
Click Prompts... button
Click * to create a New Prompt
Under the name field type: ID
Under the Prompt text field type: Please specify a collection ID
Check the box "Provide SQL Statement"
Click Edit SQL Statement... button
Copy and paste the following:
begin
 if (@__filterwildcard = '')
  SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
 else
  SELECT DISTINCT CollectionID, Name FROM v_Collection
  WHERE CollectionID like @__filterwildcard
  ORDER BY Name
end
Click OK x 2

Now back in your Report SQL Statement add the line
WHERE v_FullCollectionMembership.CollectionID = @ID

Hopefully that's helped

Below is the full SQL Statement including Total HD Space, Used HD Space, Total Ram and Processor Name.  You'll notice that it's slightly different to how I mentioned it above however, all that's happened is I've used Alias's rather than the full names for example
v_FullCollectionMembership.CollectionID = FCM.CollectionID

You can do the same if you wish, just follow the structure below.

Cheers

SELECT

   Distinct

      SYS.Netbios_Name0,

         MOD.Model0,

         PRC.Name0,

         LOG.DeviceID0,

         LOG.Size0,

         LOG.FreeSpace0,

         LOG.FreeSpace0*100/LOG.Size0  as C074,

         MEM.TotalPhysicalMemory0/1024 As C083

      FROM v_R_System SYS

         JOIN v_GS_COMPUTER_SYSTEM MOD on SYS.ResourceID = MOD.ResourceID

         JOIN v_GS_PROCESSOR PRC ON SYS.ResourceID = PRC.ResourceID

         JOIN v_GS_LOGICAL_DISK LOG on SYS.ResourceID = LOG.ResourceID

         JOIN v_GS_X86_PC_MEMORY MEM  ON SYS.ResourceID = MEM.ResourceID

         JOIN v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID

      WHERE FCM.CollectionID='SMS00001' AND

         LOG.DriveType0 =3  AND

         LOG.Size0 > 0 

      ORDER BY SYS.Netbios_Name0

Open in new window

0
 
LVL 1

Author Comment

by:james-stuart
ID: 21804788
matrixnz,

Thanks for your help. I am still a little lost when it comes to actually writting the queries, but I was able to build the report I need with your help.

I deleted lines lines 17-20 from your code and created the prompt you supplied above it and it works great.

The report lists out each system muliple times for each hard drive, is there any way to have only one line for each machine?
0
 
LVL 29

Expert Comment

by:matrixnz
ID: 21807367
Hi James

The issue is with the logical disk information(required for free space information), what we need to do is select logical disks that are greater than 0 bytes (example A: <CD Drive>:) but leave other drives like C:, D: (if theres a second partition.)  So just add 18-20 back into the SQL Query and that should give you what you need.  Full query below.

NB: This is for when you're using the prompt otherwise you'd just use the query above.

Cheers
SELECT

   Distinct

      SYS.Netbios_Name0,

         MOD.Model0,

         PRC.Name0,

         LOG.DeviceID0,

         LOG.Size0,

         LOG.FreeSpace0,

         LOG.FreeSpace0*100/LOG.Size0  as C074,

         MEM.TotalPhysicalMemory0/1024 As C083

      FROM v_R_System SYS

         JOIN v_GS_COMPUTER_SYSTEM MOD on SYS.ResourceID = MOD.ResourceID

         JOIN v_GS_PROCESSOR PRC ON SYS.ResourceID = PRC.ResourceID

         JOIN v_GS_LOGICAL_DISK LOG on SYS.ResourceID = LOG.ResourceID

         JOIN v_GS_X86_PC_MEMORY MEM  ON SYS.ResourceID = MEM.ResourceID

         JOIN v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID

      WHERE

         LOG.DriveType0 =3  AND

         LOG.Size0 > 0 

      ORDER BY SYS.Netbios_Name0

Open in new window

0
Integrate social media with email signatures

Is your company active on social media? Do you also use email signatures? Including social media icons in your email signature is a great way to get fans for free. Let all your email users know you’re on social media quickly and easily, in a single click.

 
LVL 1

Author Comment

by:james-stuart
ID: 21807500
That worked great, thanks. I have another issue though.

When I run the report it correctly asks me to select a collection, however no matter what collection I select all systems are returned. The reprort shows that it querying the right collection though. Any thoughts?

Here is what I have.

SELECT
   Distinct
      SYS.Netbios_Name0,
         MOD.Model0,
         PRC.Name0,
         LOG.DeviceID0,
         LOG.Size0,
         LOG.FreeSpace0,
         LOG.FreeSpace0*100/LOG.Size0  as C074,
         MEM.TotalPhysicalMemory0/1024 As C083
      FROM v_R_System SYS
         JOIN v_GS_COMPUTER_SYSTEM MOD on SYS.ResourceID = MOD.ResourceID
         JOIN v_GS_PROCESSOR PRC ON SYS.ResourceID = PRC.ResourceID
         JOIN v_GS_LOGICAL_DISK LOG on SYS.ResourceID = LOG.ResourceID
         JOIN v_GS_X86_PC_MEMORY MEM  ON SYS.ResourceID = MEM.ResourceID
         JOIN v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
WHERE
         LOG.DriveType0 =3  AND
         LOG.Size0 > 0
      ORDER BY SYS.Netbios_Name0

and for the prompt

if (@__filterwildcard = '')
  SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
 else
  SELECT DISTINCT CollectionID, Name FROM v_Collection
  WHERE CollectionID like @__filterwildcard
  ORDER BY Name



0
 
LVL 29

Accepted Solution

by:
matrixnz earned 500 total points
ID: 21808087
Apologise my mind was in the clouds, we need to specify the collection ID by telling it to use the Prompt Information i.e. "ID" we do that by using the following:
FCM.CollectionID=@ID

Otherwise it's just looking at all systems with Logical Disk = 3 and Logical Disk Size greater than 0

Below is the complete SQL Query.

Cheers
SELECT

   Distinct

      SYS.Netbios_Name0,

         MOD.Model0,

         PRC.Name0,

         LOG.DeviceID0,

         LOG.Size0,

         LOG.FreeSpace0,

         LOG.FreeSpace0*100/LOG.Size0  as C074,

         MEM.TotalPhysicalMemory0/1024 As C083

      FROM v_R_System SYS

         JOIN v_GS_COMPUTER_SYSTEM MOD on SYS.ResourceID = MOD.ResourceID

         JOIN v_GS_PROCESSOR PRC ON SYS.ResourceID = PRC.ResourceID

         JOIN v_GS_LOGICAL_DISK LOG on SYS.ResourceID = LOG.ResourceID

         JOIN v_GS_X86_PC_MEMORY MEM  ON SYS.ResourceID = MEM.ResourceID

         JOIN v_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID

      WHERE

         FCM.CollectionID=@ID AND

         LOG.DriveType0 =3  AND

         LOG.Size0 > 0 

      ORDER BY SYS.Netbios_Name0

Open in new window

0
 
LVL 1

Author Comment

by:james-stuart
ID: 21823129
Works perfect now. Thank you.
0
 
LVL 1

Author Closing Comment

by:james-stuart
ID: 31466355
Thank you for the help.
0

Featured Post

Are your corporate email signatures appalling?

Is it scary how unprofessional your email signatures look? Do users create their own terrible designs and give themselves stupid job titles? You can make this a lot easier for yourself by choosing an email signature management solution from Exclaimer today.

Join & Write a Comment

Every system administrator encounters once in while in a problem where the solution seems to be a needle in haystack.  My needle was an anti-virus version causing problems with my Exchange server. I have an HP DL350 with Windows Server 2008 Stand…
Have you considered what group policies are backwards and forwards compatible? Windows Active Directory servers and clients use group policy templates to deploy sets of policies within your domain. But, there is a catch to deploying policies. The…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now