Jump to content


xerxes2985

Tweaking an query and creating a report from that query.

Recommended Posts

Hi all,

I am using the Dell Command Monitor system to gather warranty dates, etc. for all my Dell brand systems. I have a working query, but however the data is gathered, the Dell clients have "extra / duplicate (not exactly)" information that I cannot figure out how to filter out. Additionally, I am trying to take the query and create a report from it so I can export the information. 

Here's the query:

select distinct SMS_G_System_COMPUTER_SYSTEM.Name, SMS_G_System_PC_BIOS.SerialNumber, SMS_G_System_DELL_ASSETWARRANTYINFORMATION_1_0.WarrantyStartDate, SMS_G_System_DELL_ASSETWARRANTYINFORMATION_1_0.WarrantyEndDate, SMS_G_System_DELL_ASSETWARRANTYINFORMATION_1_0.WarrantyDuration, SMS_G_System_DELL_ASSETWARRANTYINFORMATION_1_0.Name from  SMS_R_System inner join SMS_G_System_DELL_ASSETWARRANTYINFORMATION_1_0 on SMS_G_System_DELL_ASSETWARRANTYINFORMATION_1_0.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId

 

Here's the results - as you can see from the screenshot, there are multiple entries for the same system.

image.thumb.png.2e36cccbf962b140f4e919a42b4fee7c.png

I have worked through this somewhat and can reduce the output to only show those with "Complete Care / Accidental Damage" however, that term wasn't always used by Dell, and some of the computers that are under warranty but don't have the same coverage. This removes the "duplicate" data.

where SMS_G_System_DELL_ASSETWARRANTYINFORMATION_1_0.Name = "Complete Care / Accidental Damage"


Lastly,

I built a report using Report Builder, that has the following query - which works through Query Designer (in report builder), but when I try to run the report, I am met with an error.
 

SELECT Name0
      ,[IdentifyingNumber00]
      ,[Name00]
      ,[WarrantyStartDate00]
	  ,[WarrantyEndDate00]
	  ,[WarrantyDuration00]
  FROM [CM_LIB].[dbo].[DELL_ASSETWARRANTYINFORMATION_DATA] join
  dbo.vSMS_R_System on DELL_ASSETWARRANTYINFORMATION_DATA.MachineID = dbo.vSMS_R_System.ItemKey
  WHERE Name00 = 'Complete Care / Accidental Damage'


Error (I have checked through SQL Management Studio and the user account I ran the query as in the designer has the correct permission): 

The SELECT permission was denied on the object 'vSMS_R_System', database 'CM_LIB', schema 'dbo'.
----------------------------
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)


Any help is appreciated,

Jesse



 

Share this post


Link to post
Share on other sites

First off it is NOT support to query the tables directly for you report. Only use the supported SQL views listed on the docs page too.  Hardware inventory views - Configuration Manager | Microsoft Docs This is why you are see a permissions issue. 

Secondly the dups are to be expected, as the dell service give you all of the row that you see within the web page. I wrote a blog on why you need normalized details. You Need Normalized Warranty Results - Recast Software

 

Share this post


Link to post
Share on other sites

19 minutes ago, GarthMJ said:

First off it is NOT support to query the tables directly for you report. Only use the supported SQL views listed on the docs page too.  Hardware inventory views - Configuration Manager | Microsoft Docs This is why you are see a permissions issue. 

Secondly the dups are to be expected, as the dell service give you all of the row that you see within the web page. I wrote a blog on why you need normalized details. You Need Normalized Warranty Results - Recast Software

 

Thanks Garth for pointing me in the right direction! I now have a properly working report using the following query that gives me everything I need.

SELECT v_GS_COMPUTER_SYSTEM.Name0 as "Computer Name"
      ,IdentifyingNumber0
	  ,[WarrantyStartDate0]
      ,[WarrantyEndDate0]
	  ,[WarrantyDuration0]
	  ,v_GS_DELL_ASSETWARRANTYINFORMATION0.Name0
  FROM [CM_LIB].[dbo].[v_GS_DELL_ASSETWARRANTYINFORMATION0] join
  v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_DELL_ASSETWARRANTYINFORMATION0.ResourceID



 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...