SXI Forum

Sometimes I need to check a relitively static table (a table that doesn't change too often).  It is not always possible to add a trigger to a table, however this would be an excellent way for the DB to announce that a change was persisted. An easy way to check in MYSQL/MariaDB though, is using the following query:

MYSQL

select table_schema as database_name,
       table_name,
       update_time
from information_schema.tables tab
where update_time > (current_timestamp() - interval 30 day)
      and table_type = 'BASE TABLE'
      and table_schema not in ('information_schema', 'sys',
                               'performance_schema','mysql')
      -- and table_schema = 'sigreg' 
order by update_time desc;

The result will look something like the following:

You can now save the update_time and if, on a later query, the update time is newer than the last update time you know that the table changed somewhere.

NOTE:  The update_time will change if a record is added / deleted or updated.

An explaination of the query can be found here

This is useful for MYSQL and MariaDB. 

1. What other ways have you found to monitor a table in a database for changes?

2. What suggestions are there is we only want to know about new records being added?

On occasion a REST API requires JSON in a certain format and this can be a source of frustration.  Especially if they require an array with only on element.

E.g.

{"SomeArray":["abc", 2]}

The RESTConnector will convert XML to JSON before ending the payload to the API.  Therefore the above example can be easily achieved by configuring a stylesheet and giving the XML elements certain attributes to define the JSON output.

Below is an example XML:

<MyElement class="Object">
    <myName type="string">json</myName >
    <myBool type="boolean">true</myBool >
    <myInt type="number">3</myInt >
    <anArrayElement class="Array">
        <myVal type="number">4</myVal>
        <myVal type="number">5</myVal>
    </anArrayElement>
</MyElement>

This is what the output will look like:

{
    "MyElement": {
        "myName": "json",
        "myBool": true,
        "myInt": 3,
        "anArrayElement": [ 4, 5 ]
    }
}

If you wish to force an array with a single element you can simply make you xml look like this:

    <myElement class="Array"><myVal>SomeValue</myVal></MyElement>

And the following JSON will be produced:

    { "myElement":[ "SomeValue" ] }

The full technical doc can be found here

These steps will outline how to initially request a new certificate from GoDaddy on a windows server.  This will not replace the certificate if a client already has a procedure to obtain certs for us, this is only if we are purchasing the cert ourselves.

If this is the first time you are adding a certificate to the server you will need to provide GoDaddy with a CSR (Certificate Signing Request).  you will need to generate this CSR before requesting the certificate from GoDaddy.

Requirements
KeyStore Explorer

Steps

1. Open KeyStore Explorer and "Create a new Keystore"

2. In the "New KeyStore Type" dialog box that pops up select PKCS #12

3. Select Tools -> Generate Key Pair from the menu or press Ctrl + G

4. In the "Generate Key Pair" dialog box that pops up select RSA with a key size of 2048 and click OK

5. A new dialog box will popup and you can leave most of those values default (This will be valid for 1 year)

6. You need to provide additional details for the certificate before clicking OK.  Just above the "Add Extensions" button there is an Icon that looks like a filofax with an "@" sign on it.  Click That.

7. Populate the fields with valid responses (Replace the values in between <>test:

   • Common Name(CN): <server.name.domain.com>

   • Organization Unit(OU): <Company Name>

   • Organization Name(O): <Company Name>

   • Locality Name(L): <Johannesburg>

   • State Name(ST): <Gauteng>

   • Country(C): <ZA>

8. Click Ok. You will notice that in the "Name" field your values have been populated.

9. After verifying your values Click Ok

10. A "New Key Pair Entry Alias" dialog box will appear. Enter an appropriate alias and Click Ok

11. Enter and Confirm a new password in the "New Key Pair Entry Password" and click Ok

12. After everything has been validated you will get confirmation that the Key Pair Generation was Successful

Generate CSR

1. Right-Click on the alias you created and select Generate CSR

2. In the Generate CSR dialog box youcan select the location of the CSR file and click Ok

Request the New Certificate

1. On the GoDaddy website follow the prompts to create a new certificate.

2. When you get to the step to provide a CSR copy the contents of the file you created and past it into the space provided on the GoDaddy website.

3. You will get an Email when the certificate has been generated.  Go to the relevant page a download the certificate (Server = "Other")  A zip file will be downloaded which contains 3 files:

   • <randomNumber>.crt

   • <randomNumber>.pem

   • gd_bundle-g2-g1.crt

4. When you have this file Right-Click on the Alias you created in the key store

5. On the dropdown menu click on "Import CA Reply -> From File"

6. Select the <randomNumber>.crt file and click Import

7. You can Expect a "CA Reply Import Successful" message

8. You can now save this file as <some name>.p12

Once you have saved this file you will be able to point to it from the XLayer Servers API calls.  Here is an example.

#===============================================================================
# Enable SSL operation
#===============================================================================
server.ssl.enabled=true
server.ssl.key-store-type=PKCS12
server.ssl.key-store=classpath:<some name>.p12
server.ssl.key-store-password=<password used at "New Key Pair Entry Password">
server.ssl.key-alias=<alias used in Key Pair generation>

Below is an example script in python on how to consume the XLayerAPI v3.3+

import requests
from requests.structures import CaseInsensitiveDict

print("\n")
print("About to send REST Package")
print("==========================")
print()
url = "https://10.0.0.1:9788/xlayer-api/rest/async"

headers = CaseInsensitiveDict()
headers["Accept"] = "application/json"
headers["Content-Type"] = "application/json"
headers["Authorization"] = "Basic c3hpX2NsaWVudDpQYXNzQDEyMw=="

data = """
{
    "integrationName": "FromPI-50",
    "actionName": "PI50-Python",
    "sourceId": "INC54321",
    "payload": {
        "test": "test string",
        "id": 12345
    }
}
"""

resp = requests.post(url, headers=headers, data=data, verify=False)

print(resp.status_code)
print(resp.text)

NOTES:

1. Remember to replace the 10.0.0.1 ip with the XLayer servers IP

2. verify=False - has been used as I was testing with a self-signed certificate

3. The authorization header in this example is based on Basic Authentication, however you can replace this with the appropriate JWT header

4. Be careful with the data field as this must be valid JSON.  IF your data string is not well formed you can expect an error along the lines of: {"timestamp":"2021-05-26T12:44:24.148+00:00","status":400,"error":"Bad Request","message":"","path":"/xlayer-api/rest/async"}

To Change the ResolutionState of an Alerts in SCOM create a PowerShell script (e.g. ChangeResolutionState.ps1) with the following code:

$scomHeaders = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$scomHeaders.Add('Content-Type','application/json; charset=utf-8')
$bodyraw = "AuthenticationMode:<DOMAIN>\<USERNAME>:<PASSWORD>"
$Bytes = [System.Text.Encoding]::UTF8.GetBytes($bodyraw)
$EncodedText =[Convert]::ToBase64String($Bytes)
$jsonbody = $EncodedText | ConvertTo-Json

$uriBase = 'http://<SCOM_SERVER>/OperationsManager/authenticate'
$auth = Invoke-RestMethod -Method POST -Uri $uriBase -Headers $scomheaders -body $jsonbody -UseDefaultCredentials -SessionVariable websession

$query = @{
    "alertids" = @("<UUID>");
    "comment" = "<USEFUL_COMMENT>";
    "resolutionState" = <RESOLUTION_STATE>;
}


$jsonquery = $query | ConvertTo-Json
$Response = Invoke-WebRequest -Uri "http://<SCOM_SERVER>/OperationsManager/data/alertResolutionStates" -Method Post -Body $jsonquery -ContentType "application/json" -UseDefaultCredentials -WebSession $websession

NOTES:
1. Replace the <DOMAIN>\<USERNAME>:<PASSWORD> fields with the relevant user with access to query SCOM
2. Replace <SCOM_SERVER> with the hostname/IP Address of the SCOM server.
3. Change the <USEFUL_COMMENT> that means something for your administrators.
4. Replace <RESOLUTION_STATE> with the integer from the table below.

ResolutionState

Acknowledged            = 249
Assigned to Engineering = 248
Awaiting Evidence       = 247
Closed                  = 255
New                     = 0
Resolved                = 254
Scheduled               = 250

This is a great little tool if you are testing your web service at home and need to have it consumed from outside your own network.  This essentially sets up a tunnel to your internal http server/web api that passes traffic to and fro.  Perfect for development testing.

Their description: One command for an instant, secure URL to your localhost server through any NAT or firewall.

https://ngrok.com/

1. Download and run a program (single file no need to install anything) on your machine and provide it the port of a network service, usually a web server.

2. Connect to the ngrok cloud service which accepts traffic on a public address.

3. Traffic is relayed through to the ngrok process running on your machine and then on to the local address you specified.

With the following configuration in a XLayer workflow Connector configuration:

    <Connector id="MySQL_DB_Connector" type="MYSQL">
      <Connection xsi:type="sxi:MYSQL">
        <sxi:Server>localhost</sxi:Server>
        <sxi:User>sxiadmin</sxi:User>
        <sxi:Password>T@gi2b2</sxi:Password>
        <sxi:RetryDelay>30</sxi:RetryDelay>
        <sxi:DBName>xlayerrestapi</sxi:DBName>
        <sxi:Port>3306</sxi:Port>
      </Connection>
      <sxi:ReturnCodes>
        <sxi:Success />
        <sxi:RecoverableError>
          <sxi:Code>I/O Error: Read timed out</sxi:Code>
          <sxi:Code>Invalid state, the Connection object is closed.</sxi:Code>
          <sxi:Code>I/O Error: Connection reset by peer: socket write error</sxi:Code>
        </sxi:RecoverableError>
      </sxi:ReturnCodes>
    </Connector>

if you try to connect to a MYSQL v8 database you will receive a message in the X-ServiceBroker/bin/error.log log file which looks as follows:

 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

You will not be able to connect to the database and will get the following errors in the workflow's logfile:

21:10:45.110 - ERROR - Connection failed with error 'getConnection failed, using driver org.gjt.mm.mysql.Driver. Reason:Communications link failure

The last packet successfully received from the server was 13 milliseconds ago.  The last packet sent successfully to the server was 13 milliseconds ago.' after attempt 2, waiting 30s before connecting again
za.co.sxi.exceptions.DatabaseConnectionException: getConnection failed, using driver org.gjt.mm.mysql.Driver. Reason:Communications link failure

The last packet successfully received from the server was 13 milliseconds ago.  The last packet sent successfully to the server was 13 milliseconds ago.

This is due to a new requirement for SSL when connecting to the database. This can easily be turned off by changing the Connector configuration as follows:

<Connector id="MySQL_DB_Connector" type="MYSQL">
      <Connection xsi:type="sxi:MYSQL">
        <sxi:Server>localhost</sxi:Server>
        <sxi:User>sxiadmin</sxi:User>
        <sxi:Password>T@gi2b2</sxi:Password>
        <sxi:RetryDelay>30</sxi:RetryDelay>
        <sxi:DBName>xlayerrestapi</sxi:DBName>
        <sxi:Properties>
          <sxi:Property name="useSSL" value="false"/>
        </sxi:Properties>
        <sxi:Port>3306</sxi:Port>
      </Connection>
      <sxi:ReturnCodes>
        <sxi:Success />
        <sxi:RecoverableError>
          <sxi:Code>I/O Error: Read timed out</sxi:Code>
          <sxi:Code>Invalid state, the Connection object is closed.</sxi:Code>
          <sxi:Code>I/O Error: Connection reset by peer: socket write error</sxi:Code>
        </sxi:RecoverableError>
      </sxi:ReturnCodes>
    </Connector>

The important piece that was added was the

<sxi:Properties>
  <sxi:Property name="useSSL" value="false"/>
</sxi:Properties>

However, while this will work it may not be the best and most secure solution.  An excellent explanation can be found here

This may need some additional configuration options to be added to XLayer.  These will be discussed in a future version of XLayer.  The above is a working solution in the meantime.