Tweaking an query and creating a report from that query.

[xerxes2985]

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.

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



 

[GarthMJ]

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

 

[xerxes2985]

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