Kaseya Community

Warranty Master (VSA 9.5 integration) and reporting

  • I used to download the spreadsheet from Warranty Master, run a VB script to import the data to Custom Fields, then report on that.  Now with the integration I have an automatic sync from Warranty Master to the VSA to the new Audit Fields of "System Purchase Date" and "System Warranty Expire Date" - Great!

    The issue is that those fields are NOT Custom Fields and are NOT available in the Report Parts for an Audit report.

    The data is viewable under Audit > View Individual Data > Machine Summary > under the Hardware tab, Summary tab, then in the System Information section.  Not to be confused with Audit > View Individual Data > System Information....as it is not available there.

    In the Agent module you can configure a view to include these columns....but not report on them.

    Curious what you guys are doing to report on the Warranty information in an Audit report. (Or if I should just continue my old process)



    -
    [edited by: Tim Varvais at 9:10 AM (GMT -7) on May 30, 2018]
  • I output the results from the agent menu for the time being using the export function on the far right corner (gear button).

  • I've had a support ticket open for 3 months on this after 9.5 release. Support informed me that:

    - both fields would be added to Info Center reporting in a future patch release

    - neither field is planned to be added to Machine View advanced filtering

    If you do any work in the KSubscribers SQL database, both fields are available in the auditRsltSmbios table:

    sysPurchaseDate

    sysWarrantyExpireDate

  • Should be able to make your own custom dataset in the form of a name-value part, to achieve this. You might need to be an on-premise customer though to do it.

  • September rolls around and this is still not fixed.. huge surprise.

    I've modified this file "D:\Kaseya\xml\Reporting\DataSetRegistration\1\Audit\vAuditMachineSummary.xml" to add the warranty expiry and purchase date columns and update the query as follows. If your Column RegistrationId values seem different adjust the values to fit your setup (they should be unique and sequential).

         <Column RegistrationId="2019094" fullyQualifiedName="w.sysPurchaseDate" datatype="STRING" length="" caption="System Purchase Date" description="System Purchase Date" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" />

         <Column RegistrationId="2019095" fullyQualifiedName="w.sysWarrantyExpireDate" datatype="STRING" length="" caption="System Warranty Expiry Date" description="System Warranty Expiry Date" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" />

       </Columns>

       <Query> FROM vAuditMachineSummary v JOIN vAgentSummaryGridContents w ON w.AgentGuid = v.AgentGuid JOIN ReportCenter.MachineFilterList mfl ON mfl.AgentGuid = v.agentGuid </Query>

    I then ran the report registration process (System > Server Management > Configure > Change Reporting Configuration > Run Registration button to register the new or modified data part XML with your VSA).

    Once that's done the warranty expiry and purchase dates are available under the Audit > Machine Summary report part. It's possible Kaseya will break this with subsequent patches but it's easy enough to reapply the changes until they fix it properly.

  • Hello Everyone,

    If you are on Premises you could do something like below.

    This would basically copy over the from those columns into into your custom fields every time they are updated.

    Name the custom fields as follows

    customSysPurchaseDate

    customSysWarrantyExpireDate

    The way I drafted the below procedure (that can be certainly improved) saves the date in yyyy-mm-dd format.

    It should be accepted by both Custom Field of type Date or String.

    You can also change the custom fields names by changing the search description inside the trigger.

    Once you created the custom scripts you would run this in your ksubscriber database.

    USE ksubscribers

    GO

    IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[triud_z_copywarrantymasterfields]'))

    DROP TRIGGER [dbo].[triud_z_copywarrantymasterfields]

    GO

    CREATE TRIGGER [dbo].[triud_z_copywarrantymasterfields] ON [dbo].[auditRsltSmbios]

    FOR INSERT, UPDATE

    AS

    BEGIN

     declare @l_agentGuid NUMERIC(26)

     declare @l_fieldPKsysPurchaseDate NUMERIC(26)

     declare @l_fieldPKsysWarrantyExpireDate NUMERIC(26)

     declare @l_sysPurchaseDate DATETIME

     declare @l_sysWarrantyExpireDate DATETIME

     SELECT @l_fieldPKsysPurchaseDate = id

     FROM [ksubscribers].[dbo].[auditRsltManualFields]

     WHERE fieldName = 'customSysPurchaseDate'

     SELECT @l_fieldPKsysWarrantyExpireDate = id

     FROM [ksubscribers].[dbo].[auditRsltManualFields]

     WHERE fieldName = 'customSysWarrantyExpireDate'

     declare inscur cursor local for select [sysPurchaseDate], [sysWarrantyExpireDate], [agentGuid]

    from inserted

     if (@l_fieldPKsysPurchaseDate IS NOT NULL AND @l_fieldPKsysWarrantyExpireDate IS NOT NULL)

     BEGIN

    IF update ([sysPurchaseDate]) or update([sysWarrantyExpireDate])

    BEGIN

    open inscur

      fetch next from inscur into @l_sysPurchaseDate, @l_sysWarrantyExpireDate, @l_agentGuid

      while @@FETCH_STATUS = 0 begin

      UPDATE [dbo].[auditRsltManualFieldValues] SET fieldValue = (SELECT CONVERT(char(10), @l_sysPurchaseDate, 126)) WHERE agentGuid = @l_agentGuid and fieldNameFK = @l_fieldPKsysPurchaseDate

      IF @@ROWCOUNT = 0

      BEGIN

      INSERT INTO [dbo].[auditRsltManualFieldValues](agentGuid, fieldNameFK, fieldValue) VALUES (@l_agentGuid,  @l_fieldPKsysPurchaseDate, (SELECT CONVERT(char(10), @l_sysPurchaseDate, 126)))

      END

      UPDATE [dbo].[auditRsltManualFieldValues] SET fieldValue = (SELECT CONVERT(char(10), @l_sysWarrantyExpireDate, 126)) WHERE agentGuid = @l_agentGuid and fieldNameFK = @l_fieldPKsysWarrantyExpireDate

      IF @@ROWCOUNT = 0

      BEGIN

      INSERT INTO [dbo].[auditRsltManualFieldValues](agentGuid, fieldNameFK, fieldValue) VALUES (@l_agentGuid, @l_fieldPKsysWarrantyExpireDate, (SELECT CONVERT(char(10), @l_sysWarrantyExpireDate, 126)))

      END

      fetch next from inscur into @l_sysPurchaseDate, @l_sysWarrantyExpireDate, @l_agentGuid

      end

      close inscur

        deallocate inscur  

    END

     END

    END