Ks Tan (Asia Vad) asked a question.

Edited July 1, 2024 at 9:13 AM
To have more interfaces details in Active Monitor Interface Status Comment Field

Currently, the interface status comment field can only be automatically added from one of the 3 of ifAlias, ifname, or ifDesc according to General settings, not all or any combination of 2. In some project, the details are needed from both ifAlias + ifName, or different combination of 2 or all 3.

For example both ifAlias : Connection to Singtel ISP, ifName : Gi 1/0/1, are needed in the alert details

 

The script below retrieves both ifAlias + ifName record of the test Device, from another db table stored by Discovery Scan asset details, and update it to comment field.

 

Note: It's to be test run from Active Monitor > Test button, to update the interface comment for selected Device, but not to be assigned to actual device as monitor.

  1. // Script developed by Kstan, Asia Vad.
  2. // To add both ifAlias AND ifName to the interface status montior Comment field
  3. // For more complete network interface comment, based on ifAlias, ifDesc, ifName captured
  4. // during Discovery Scanning of network devices, and stored at AssetToken table
  5.  
  6. var oDb = Context.GetDB;
  7. if (null == oDb)
  8. {
  9. Context.SetResult( 1, " Problem creating the DB object");
  10. }
  11. else
  12. {
  13.  
  14. // Get the device ID
  15. var sDeviceID = Context.GetProperty("DeviceID");
  16.  
  17. // Retrieve interface status monitor
  18.  
  19. var sSql = "SELECT nPivotActiveMonitorTypeToDeviceID, Device.nDeviceID, sArgument, sComment, sNetworkAddress FROM PivotActiveMonitorTypeToDevice JOIN Device ON Device.nDeviceID = PivotActiveMonitorTypeToDevice.nDeviceID JOIN ActiveMonitorType ON ActiveMonitorType.nActiveMonitorTypeID = PivotActiveMonitorTypeToDevice.nActiveMonitorTypeID JOIN NetworkInterface ON NetworkInterface.nNetworkInterfaceID = Device.nDefaultNetworkInterfaceID WHERE PivotActiveMonitorTypeToDevice.bRemoved = 0 AND Device.bRemoved = 0 AND ActiveMonitorType.bRemoved = 0 AND ActiveMonitorType.sMonitorTypeName = 'Interface' AND Device.nDeviceID = " + sDeviceID;
  20. var oRs = oDb.Execute(sSql);
  21.  
  22. while ( !oRs.EOF )
  23. {
  24. var nPivotActiveMonitorTypeToDeviceID = oRs("nPivotActiveMonitorTypeToDeviceID");
  25. var nDeviceID = oRs("nDeviceID");
  26. var ifIndex = oRs("sArgument");
  27. var ifComment = oRs("sComment");
  28. var networkAddress = oRs("sNetworkAddress");
  29. // Context.LogMessage("Inf Monitor:" + nDeviceID + "," + ifIndex + "," + ifComment + "," + networkAddress);
  30.  
  31. // Retrieve ifName and ifDescription based on nDeviceID and ifIndex
  32. // The script will fail, if there is apostrophe in the text returned.
  33. var sSql2 = "SELECT nDeviceID ,IfIndex ,IfNameID ,(select Value from AssetToken where AssetToken.TokenID = AssetDeviceInterface.IfNameID) as ifName ,IfDescriptionID ,(select Value from AssetToken where AssetToken.TokenID = AssetDeviceInterface.IfDescriptionID) as IfDesc ,IfAliasID ,(select Value from AssetToken where AssetToken.TokenID = AssetDeviceInterface.IfAliasID) as IfAlias ,IfOperStatus FROM AssetDeviceInterface where nDeviceID =" + nDeviceID + " AND ifIndex = " + ifIndex;
  34. var oRs2 = oDb.Execute(sSql2);
  35. if ( !oRs2.EOF )
  36. {
  37. var nDeviceID2 = oRs2("nDeviceID");
  38. var IfIndex2 = oRs2("IfIndex");
  39. var ifName = oRs2("ifName");
  40. var IfAlias = oRs2("IfAlias");
  41. var newComment = IfAlias + " (" + ifName + ")";
  42. var sSql3 = "UPDATE PivotActiveMonitorTypeToDevice SET sComment = '" + newComment + "' where nPivotActiveMonitorTypeToDeviceID = " + nPivotActiveMonitorTypeToDeviceID;
  43.  
  44. // Context.LogMessage("Token Result:" + nDeviceID2 + "," + IfIndex2 + "," + ifName + "," + IfAlias);
  45. Context.LogMessage("SQL Cmd Executed:" + sSql3);
  46. var oRs3 = oDb.Execute(sSql3);
  47. } else
  48. {
  49. Context.LogMessage("No result, no change");
  50. }
  51.  
  52. oRs.moveNext();
  53. }
  54.  
  55. }
  56.  
  57. oDb.Close(); //Close database connection
  58.  
  59. Context.SetResult(0, "Result OK");

 

 

  • 1 answer
  • 250 views
GianMaria Calzolari likes this.

  • GianMaria Calzolari (Mead Informatica)

    Thanks for the script!!

    I have solved the "fail if there is apostrophe in the text returned" and changed "ifName" with "ifDesc" if the first is blank (it should't happen, but I found this in a few old devices!)

     

    Here you are my version

    1. // Script developed by Kstan, Asia Vad.
    2. // To add both ifAlias AND ifName to the interface status montior Comment field
    3. // For more complete network interface comment, based on ifAlias, ifDesc, ifName captured
    4. // during Discovery Scanning of network devices, and stored at AssetToken table
    5.  
    6. // https://community.progress.com/s/question/0D5Pb00000uW1tkKAC/to-have-more-interfaces-details-in-active-monitor-interface-status-comment-field
    7. // 01/07/2024
    8. //
    9. // 29/11/2024 - Mead Informatica - GMC - Added ifDesc into Comments if ifName is blank
    10. // 05/12/2024 - Mead Informatica - GMC - Added proper apostrophe escape in comment
    11. //
    12.  
    13. // apostrophe escaping
    14. function escapeSql(value) {
    15. return value.replace(/'/g, "''");
    16. }
    17.  
    18. var oDb = Context.GetDB;
    19.  
    20. if (null == oDb) {
    21. Context.SetResult( 1, " Problem creating the DB object");
    22. } else {
    23. // Get the device ID
    24. var sDeviceID = Context.GetProperty("DeviceID");
    26. // Retrieve interface status monitor
    27. var sSql = "SELECT nPivotActiveMonitorTypeToDeviceID, Device.nDeviceID, sArgument, sComment, sNetworkAddress FROM PivotActiveMonitorTypeToDevice JOIN Device ON Device.nDeviceID = PivotActiveMonitorTypeToDevice.nDeviceID JOIN ActiveMonitorType ON ActiveMonitorType.nActiveMonitorTypeID = PivotActiveMonitorTypeToDevice.nActiveMonitorTypeID JOIN NetworkInterface ON NetworkInterface.nNetworkInterfaceID = Device.nDefaultNetworkInterfaceID WHERE PivotActiveMonitorTypeToDevice.bRemoved = 0 AND Device.bRemoved = 0 AND ActiveMonitorType.bRemoved = 0 AND ActiveMonitorType.sMonitorTypeName = 'Interface' AND Device.nDeviceID = " + sDeviceID;
    28. var oRs = oDb.Execute(sSql);
    30. while ( !oRs.EOF ) {
    31. var nPivotActiveMonitorTypeToDeviceID = oRs("nPivotActiveMonitorTypeToDeviceID");
    32. var nDeviceID = oRs("nDeviceID");
    33. var ifIndex = oRs("sArgument");
    34. var ifComment = oRs("sComment");
    35. var networkAddress = oRs("sNetworkAddress");
    36. // Context.LogMessage("Inf Monitor:" + nDeviceID + "," + ifIndex + "," + ifComment + "," + networkAddress);
    37.  
    38. // Retrieve ifName and ifDescription based on nDeviceID and ifIndex
    39. // The script will fail, if there is apostrophe in the text returned. (Solved by Mead Informatica - GMC - 05/12/2024)
    40. var sSql2 = "SELECT nDeviceID ,IfIndex ,IfNameID ,(select Value from AssetToken where AssetToken.TokenID = AssetDeviceInterface.IfNameID) as ifName ,IfDescriptionID ,(select Value from AssetToken where AssetToken.TokenID = AssetDeviceInterface.IfDescriptionID) as IfDesc ,IfAliasID ,(select Value from AssetToken where AssetToken.TokenID = AssetDeviceInterface.IfAliasID) as IfAlias ,IfOperStatus FROM AssetDeviceInterface where nDeviceID =" + nDeviceID + " AND ifIndex = " + ifIndex;
    41. var oRs2 = oDb.Execute(sSql2);
    42.  
    43. if ( !oRs2.EOF ) {
    44. var nDeviceID2 = oRs2("nDeviceID");
    45. var IfIndex2 = oRs2("IfIndex");
    46. var ifName = oRs2("ifName");
    47. var IfAlias = oRs2("IfAlias");
    48. var IfDesc = oRs2("IfDesc");
    49. // Added by Mead Informatica - GMC - 29/11/2024)
    50. if (ifName == "") {
    51. var newComment = IfAlias + " (" + IfDesc + ")";
    52. } else {
    53. var newComment = IfAlias + " (" + ifName + ")";
    54. }
    55. var sSql3 = "UPDATE PivotActiveMonitorTypeToDevice SET sComment = '" + escapeSql(newComment) + "' where nPivotActiveMonitorTypeToDeviceID = " + nPivotActiveMonitorTypeToDeviceID;
    56. // Context.LogMessage("Token Result:" + nDeviceID2 + "," + IfIndex2 + "," + ifName + "," + IfAlias + "," + IfDesc);
    57. Context.LogMessage("SQL Cmd Executed:" + sSql3);
    58. var oRs3 = oDb.Execute(sSql3);
    59. } else {
    60. Context.LogMessage("No result, no change");
    61. }
    62. oRs.moveNext();
    63. }
    64. }
    65.  
    66. //Close database connection
    67. oDb.Close();
    68. Context.SetResult(0, "Result OK");