1 Reply Latest reply on Nov 15, 2017 12:31 PM by mjperkins

    NCM failed jobs and device identification using SQL

    csowerby

      Hi there -- we run a simple SQL query to identify failed NCM config download jobs:

       

      SELECT *

        FROM NCM_Audit

      WHERE Type = 'Failed' AND

        (NCM_Audit.DateTime > DATEADD(day,-1,getdate()))

      Order By NCM_Audit.DateTime DESC

       

      In most cases the details field identifies the device by IP and we can follow up:

       

      IDUserNameModuleNameTypeActionDetailsDateTime
      09724A50-66C5-401A-BA84-2E8C44BD11DBNightlyBackupFHAv1JobsAuditFailedDownload Config requested by jobConnection Refused by x.x.x.x2016-11-23 02:53:41.847
      9B794802-BFE4-4264-871D-BEB7E77889EFNightlyBackupFHAv1JobsAuditFailedDownload Config requested by jobConnection Refused by x.x.x.x2016-11-23 02:46:40.030

       

      When we get an "Unable to Log into Router : Timeout" message there is no IP and it makes tracking down the device challenging:

       

      35EEB4A3-7426-43A4-A511-484CEB203AA2NightlyBackupFHAv1JobsAuditFailedDownload Config requested by jobUnable to Log into Router : Timeout2016-11-23 02:32:37.310
      78253405-5B7A-4062-A74D-315D007C1506NightlyBackupFHAv1JobsAuditFailedDownload Config requested by jobUnable to Log into Router : Timeout2016-11-23 02:32:37.283

       

      We could modify the job to email results but I was wondering if anyone had an idea(s) on how to improve the query to include the machine name and IP? I have tried to join the NCM_Audit.ID field to other tables but have not had success getting the information needed (I believe the query is fine but don't know enough about the db schema)

       

      Thanks all