Smart IT Social Sync Utility

Version 5
    Share This:

    Introduction

     

    Smart IT shows ’Update Feeds’ on the Dashboard. This information comes from Smart IT’S social component. For example, any activity done on tickets (status change, assignment etc) including changes to work notes/activities will be tracked as an event and stored in Mongo DB.

    There are situations, where social sync is impacted. Possible causes would be:

    • Incorrect SEM plugin connection configuration..
    • Network issues due to ports not open for Social-Smart IT communications.
    • Too many records piled up in social bridge form due to running jobs such as Asset reconciliations eventually leading to time outs.

    In such cases Social bridge records are stuck in the “In Progress” status. These events will not appear on the  ‘Update Feeds’ of Smart IT or in tickets Activity notes of Smart IT.

    To remediate this issue,  we recommend the Social Sync utility. This utility will push the “In Progress” records from the Social Bridge form to Mongo DB.

     

    Limitations

    Currently utility support deployments having SQL server ITSM database only.   The CSV file generations is done directly on database and query offered works only for SQL server due to use of server specific functions.

     

     

    How to Run

    This utility needs a  CSV file containing the “In progress” records from the Social Bridge Form.  This CSV is provided as an input to the Sync utility to push the records to Mongo DB.

    • Generate CSV File. The steps to generate CSV are explained below in separate section along with screen shots.
    • Open the CSV file using Notepad++ Editor. Select -> Encoding -> Convert To UTF-8.  Save the file.
    • Extract the utility zip containing executable binaries(attached) to location of Mongo DB machine.
    • Run utility using command->

                   SocialSync.bat -ssdn <SOCIAL_DB_NAME>  -ssdh <SOCIAL_DB_HOST> -ssdp <SOCIAL_DB_PORT> -f <CVS_FILE_FULL_PATH> -T <TENANT_ID>

                 where:

              ssdn:    Social  Dabase name

              ssdh:    Social Database Hot machine name

              ssdp:    Social Db (Mongo DB)  port

              f:           CSV file path containing records to be pushed to social.

              T:          Tenant Id

     

                 For example ->

                        SocialSync.bat -ssdn social -ssdh clm-pun-003257.bmc.com -ssdp 27017 -f “C:\ROD_Demo\Social_DB_sync\SocialBridgeRecords.csv” -T 000000000000001

     

    • On successful completion of Social Sync up using utility you can use following command to delete the records from Social Bridge Form.

         DELETE FROM <BRIDGE_TABLE_NAME> WHERE C1 = 1 AND c3 <= <TIME_STAMP>

              Where:

                   <TIME_STAMP> :Is the time stamp value , you used during CSV creation.  The intent is to delete only those record which you have processed thru utility. Time stamp                will help you select only those.

     

    Chunking Based Social Sync:

     

    If the Bridge record volume is too high (300,000+) then you can process the records in chunk.  This can be easily done by splitting records in chunk. Bridge table column C3 (Create Date) can be used to define Date range for this.

     

    Steps for chunking:

    •      Based on number of records to be processed, come up with chunking count.
    •       Define the time stamps for Date range for each chunk and execute consume following SQLs.
    •      Generate CSV file for each chunk by using chunk date range.

    select C304390133, C304390137, C10001821, C304027501, C304309603, REPLACE(REPLACE(cast(C301398700 as nvarchar(max)),CHAR(13),''),'"','\"')AS C301398700, C2, C302301092, C1000000161, C3, C5, C179, REPLACE(REPLACE(cast(C301398800 as nvarchar(max)),CHAR(13),''),'"','\"') AS C301398800 , C200000020,  C400079600, C240000007, C6,C304390279,C302303492,C302303493,C302300505,C304309600,C304309590,C302258901,C304326750,C302258903,C304326300,C302258902,C304027300,C304027503,C300364522, REPLACE(REPLACE(cast(C302301056 as nvarchar(max)),CHAR(13),''),'"','\"') AS C302301056, REPLACE(REPLACE(cast(C302301057 as nvarchar(max)),CHAR(13),''),'"','\"') AS C302301057 from T<BRIDGE_TABLE_NAME> where C7 = 1 AND C3 <= <Start_Time_Stamp> AND C3 >=<end_Time_Stamp>

    • Once Social sync is completed for this file , use following to clean up records belongs to this chunk.

                   DELETE FROM <BRIDGE_TABLE_NAME> where C1 = 1 AND C3 <= <Start_Time_Stamp> AND C3 >=<end_Time_Stamp>

     

    Steps to generate CSV report file for Social Sync up.

    Please refer the attached document containing  the details of how to generate  CSV file using SQL Server Studio. ( Screen Snapshots for reference.)

     

    We are looking forward for your comments and suggestions about this utility. Feedback will be used to influence future versions of the utility.

     

    Thanking you

    BMC Remedy Smart IT & MyIT Team