Skip to main content

ServiceNow

Installing and upgrading

How to set up SGC for 1E.

SGC for 1E is a ServiceNow application that can be purchased and installed from ServiceNow App Store. This section provides details on how to install SGC for 1E and how to configure it post-installation for use.

Setup for ReportingTachyon Database

This setup demonstrates a step-by-step procedure to capture data using 1E instructions, and establish a process to retain the most recent response data irrespective of the device not being connected to 1E switch. The need for this procedure has arisen in order to gather additional data required for ServiceNow CMDB.

This process uses two different SQL scripts to identify a device. One of the script uses Computer Manufacturer, Model and Serial Number to identify the device, whereas the other uses FQDN. When either of the scripts is executed on SQL Server, it creates a new database with two stored procedures and a new table. Users now can add the instructions for which they want to retain data persistently. Upon execution of the first stored procedure, it enumerates all the instructions into a table. For each of these instructions, it will run the second stored procedure with instruction name as parameter that generates the most recent response data for the instructions. Finally, it merges the most recent response data with the persistent data into the table.

If available data for any device is older than 99 days, then that data is removed from the table.

There are few limitations to this process, as mentioned below:

  1. As per the current process, there is only one response table in use. However, if the other switches use other databases, then there might be the need of adding more response tables to it.

  2. The SQL script provide the latest responses for the instruction name given. This means that if there is a scheduled run for an instruction with a defined collection time and a 1E user also runs the same instruction for a different collection time during the same period, then either this script will return data for the least collection time given among the two runs or might throw an error. To avoid this conflict, any instruction that is part of this automation process is to be noted so that any 1E user in Explorer will be able to identify that the same instruction shouldn't be run manually during the defined collection time.

Create a database

In SGC for 1E, certain data related to device details, disk, and network adapter is required from 1E that cannot be returned through Rest APIs. This data can be retrieved using certain 1E instructions. To keep responses from these instructions, you need to create a new database called ReportingTachyon.

Check if the ReportingTachyon database exists, else follow the below procedure to create this new database:

  1. Setup a stored procedure that pulls data from the response tables for specified instructions and places the most recent data for each device into a table named like Response_<Instruction Name>.

  2. Note that the devices for which there is no new data is provided in last 99 days, records for such devices have to be removed.

  3. Create a stored procedure that is run regularly to populate data into ReportingTachyon database.

  4. This triggers the instructions that are listed in ReportingTachyon.dbo.InstructionsToRun table and call the stored procedure GetTachyonTableData with the instruction name.

  5. Finally, create a table where the data is populated from the response table by executing the instructions from the ReportingTachyon database. The most recent data is made available here. It is recommended that specialized instructions with known identifier are executed, so they are not executed interactively by other users of 1E.

Upon execution of the script on SQL Server, it results into the following:

  1. Creates a ReportingTachyon database.

  2. Creates two stored procedure in the ReportingTachyon database, they are:

    1. Usp_GetTachyonTableData

    2. Usp_RunGetTachyonTableData

  3. Creates InstructionsToRun table in the ReportingTachyon database.

Create User Accounts

To initiate the process, the following is the prerequisite. Create user accounts in 1E box where SQL server is hosted as listed below-

  1. SQL Service account:

    1. The user with this role has permission to create database and SQL Agent jobs along with an appropriate SQL login account.

    2. The server authentication mode is set to "SQL Server Authentication Mode and Windows Authentication".

    3. Password is set to "never expires".

    4. The user has Read Only access to SLA-Data, TachyonMaster, TachyonResponses and SLA-Shared databases.

    5. The user has Read/Write access to ReportingTachyon database and is able to create a table.

    6. The user should be the owner of SQL Agent job.

    7. SQL agent job owner has permission to execute Stored Procedures on ReportingTachyon database.

  2. 1E Service account

    1. Password is set to "never expires".

    2. The user has actioner role and sufficient rights to execute the below instructions-

      Instruction

      Purpose

      1E-Explorer-TachyonCore-GetIpAddresses

      To get IP address

      1E-Explorer-TachyonCore-LogicalDiskDetails

      To get file system details

      1E-Explorer-TachyonCore-AllInstalledSoftware

      To get installed software

      1E-Explorer-Examples-GetMemoryFromSysInfo

      To get RAM usage

      1E-Explorer-TachyonCore-NetworkListeningProcessesAndPorts

      To get running processes

      1E-Explorer-Examples-GetConnections

      To get TCP connections

      1E-ITSM-GetDeviceDetails

      To get device details

      1E-ITSM-GetDiskDetails

      To get disk usage data

      1E-ITSM-GetNetworkAdapters

      To get network adapter data

Install MID Server

Install MID Server in 1E box with the line of sight to the 1E SQL database. Follow this ServiceNow document How to install MID Server to install MID Server.

Post script execution

  1. Add the name of the instruction to the ReportingTachyon.dbo.InstructionsToRun table. Here in this case add 1E-ITSM-GetDeviceDetails, 1E-ITSM-GetDiskDetails, and 1E-ITSM-GetNetworkAdapters to the ReportingTachyon.dbo.InstructionsToRun table.

  2. Execute the instructions based on the data you want to capture and schedule the job as per the requirement.

  3. Once the instruction is executed, schedule GetRunTachyonTableData to do the following:

    1. It finds when was the last time the instruction was executed and pulls that data from the ResponseTable.

    2. In case this is the first time the data is collected, then it creates a table called Response_<instruction_name> in the ReportingTachyon database and places the data in the table with a time stamp.

    3. If this is not the first time the data is collected, then it updates this new collected data into Response_<instruction_name> table. This allows to update the data for the devices that may not have responded in the earlier occasions.

    4. Finally, it removes any data that is older than 99 days.

    5. It enumerates the instruction in the InstructionsToRun table and calls GetRunTachyonTableData for each instruction you have listed.

In this section: