SXI Forum / General Discussion

MSSQL - DATEPART() function to get the integer value of datetime

dummy@example.com (StephanB) — Thu, 02 Sep 2021 18:15:31 +0000

The DATEPART() function returns a specified part of a datetime, this function returns the result as an integer value.
Example will give you only the data entries with the date of 2021-01-01:

SELECT [Flag],
       [Epoc],
       [Date],
       [Action],
       [EID],
       [UID] 
FROM MyTable 
WHERE DATEPART(year,[Date]) = '2021' and  DATEPART(month,[Date]) = '01' and DATEPART(day,[Date]) = '01'

Using the SharePoint API

dummy@example.com (SeanR) — Mon, 19 Jul 2021 15:01:48 +0000

I stumbled upon this really helpful document of how to consume the SharePoint API

https://www.codeproject.com/Articles/99 … t-Rest-API

CA signed Certificate for XLayer server

dummy@example.com (SeanR) — Tue, 01 Jun 2021 17:50:04 +0000

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

Open KeyStore Explorer and "Create a new Keystore"
In the "New KeyStore Type" dialog box that pops up select PKCS #12
Select Tools -> Generate Key Pair from the menu or press Ctrl + G
In the "Generate Key Pair" dialog box that pops up select RSA with a key size of 2048 and click OK
A new dialog box will popup and you can leave most of those values default (This will be valid for 1 year)
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.
Populate the fields with valid responses (Replace the values in between <>test:
- Common Name(CN):
- Organization Unit(OU):
- Organization Name(O):
- Locality Name(L):
- State Name(ST):
- Country(C):
Click Ok. You will notice that in the "Name" field your values have been populated.
After verifying your values Click Ok
A "New Key Pair Entry Alias" dialog box will appear. Enter an appropriate alias and Click Ok
Enter and Confirm a new password in the "New Key Pair Entry Password" and click Ok
After everything has been validated you will get confirmation that the Key Pair Generation was Successful

Generate CSR

Right-Click on the alias you created and select Generate CSR
In the Generate CSR dialog box youcan select the location of the CSR file and click Ok

Request the New Certificate

On the GoDaddy website follow the prompts to create a new certificate.
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.
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:
- .crt
- .pem
- gd_bundle-g2-g1.crt
When you have this file Right-Click on the Alias you created in the key store
On the dropdown menu click on "Import CA Reply -> From File"
Select the .crt file and click Import
You can Expect a "CA Reply Import Successful" message
You can now save this file as .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:.p12
server.ssl.key-store-password=
server.ssl.key-alias=

Transfer files up to 2 GB.

dummy@example.com (SeanR) — Tue, 01 Jun 2021 16:42:27 +0000

You can also use the XLayer hosted option at

XLayer File Drop

Find out SOME OF SCOMs defaults

dummy@example.com (SeanR) — Thu, 22 Apr 2021 16:03:59 +0000

To get a list of all the possible values in SCOM you can call the following ()remember you still need to get the SessionID and authenticate before calling these APIs):

Alert Columns

http:///OperationsManager/resources/alertColumns

Alert Priorities

http:///OperationsManager/resources/alertPriority

Alert Resolution States

http:///OperationsManager/resources/alertResolutionStates

Alert Severity

http:///OperationsManager/resources/alertSeverity

Set the ResolutionState on an Alert in SCOM from PowerShell

dummy@example.com (SeanR) — Thu, 22 Apr 2021 15:21:33 +0000

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:\:"
$Bytes = [System.Text.Encoding]::UTF8.GetBytes($bodyraw)
$EncodedText =[Convert]::ToBase64String($Bytes)
$jsonbody = $EncodedText | ConvertTo-Json

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

$query = @{
    "alertids" = @("");
    "comment" = "";
    "resolutionState" = ;
}


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

NOTES:
1. Replace the \: fields with the relevant user with access to query SCOM
2. Replace with the hostname/IP Address of the SCOM server.
3. Change the that means something for your administrators.
4. Replace 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

Get SCOM Alerts using PowerShell and the REST API

dummy@example.com (SeanR) — Thu, 22 Apr 2021 15:11:37 +0000

To COLLECT Alerts from SCOM create a PowerShell script (e.g. GetAlerts.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:\:"
$Bytes = [System.Text.Encoding]::UTF8.GetBytes($bodyraw)
$EncodedText =[Convert]::ToBase64String($Bytes)
$jsonbody = $EncodedText | ConvertTo-Json

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

$query = @{
"classId" = $null;
"objectIds" = $null;
"criteria"= "(((Severity = '1') or (Severity = '2')) AND (ResolutionState = '0'))" ;
"displayColumns" = "severity","monitoringobjectdisplayname","name","age","repeatcount","lastmodified","resolutionstate","ticketid"
} 

$jsonquery = $query | ConvertTo-Json
$Response = Invoke-WebRequest -Uri "http:///OperationsManager/data/alert" -Method Post -Body $jsonquery -ContentType "application/json" -UseDefaultCredentials -WebSession $websession
$alerts = ConvertFrom-Json -InputObject $Response.Content
$alerts.rows | select id,monitoringobjectdisplayname,name,severity,resolutionstate,lastmodified,age,ticketid

You will receive a Response that will look something like this

id : b506ce29-9bb8-4c93-8be0-6a53dfc68757
monitoringobjectdisplayname : demohost01.sxi.local
name : OleDB: Results Error
severity : Error
resolutionstate : New
lastmodified : 2021-04-22T08:20:41.2000000Z
age : 6 hours, 46 minutes
ticketid :
id : f1400c42-1699-4e00-95fb-9482418be391
monitoringobjectdisplayname : demohost01.sxi.local
name : System Center Management Health Service Unloaded System Rule(s)
severity : Error
resolutionstate : New
lastmodified : 2021-04-22T07:57:31.3270000Z
age : 7 hours, 9 minutes
ticketid :
id : f1f3fd94-0a4d-4adc-bab6-960013d52f30
monitoringobjectdisplayname : demohost02.sxi.local
name : OleDB: Results Error
severity : Error
resolutionstate : New
lastmodified : 2021-04-22T08:20:41.2000000Z
age : 6 hours, 46 minutes
ticketid :

The $query can be changed to fine-tune what you are collecting. Here are some starting parameters

Severity

0 - Information
1 - Warning
2 - Error

Priority

0 - Low
1 - Medium
2 - High

ResolutionState

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

ERROR - Signal Registry Polling disabled

dummy@example.com (StephanB) — Mon, 01 Feb 2021 07:31:54 +0000

This is to prevent the creation of duplicate transaction, the Signal Registry Polling will be disabled.
The specific workflow configuration will stop working when a condition for a non recoverable error was detected in the Signal Registry.

Windows Authentication to SQL server from Java

dummy@example.com (SeanR) — Tue, 12 Jan 2021 13:01:49 +0000

Here is an example:


      
        127.0.0.1
        myuser
        secret
        10
        SomeDB
        
          
          
        
        1433
      
      
        
        
          .+Read timed out.+
          .*Invalid state.+
          .*SHUTDOWN is in progress.+
          I/O Error: Connection reset by peer: socket write error

SXI License Request

dummy@example.com (StephanB) — Wed, 05 Aug 2020 12:15:14 +0000

How many NIC "network interface card" do you have on the machine ?

Using the X-Service Broker configuration to replace X-Notify

dummy@example.com (StephanB) — Mon, 06 Jul 2020 14:30:33 +0000

Using the X-Service Broker configuration to collect X-HeatBeat files for replacing X-Notify.
Setting up the SendXNotifications to consume the X-HeartBeat files "X-Notify~nnnnnnnnnnnn.nnn.xml" in X-ServiceBroker configuration to replace X-Notify going forward.

I want to give credit to Sibu and Zwido for helping me on this.
Thanks you for helping me on this.....

The heartbeat is configured in the X-HBChecker Entities.xml file example below:

  
    
      yes
      
      
        A :[TYPE]: Heartbeat for :[MD]: has :[STATUS]:

When the server "MD" Server does not send a Heart Beat file to the Main XLayer Server that receives the X-HB files.
The X-HBChecker will create X-Notify~nnnnnnnnnnnn.nnn.xml file that is consumed by the X-Notify, going forward you can now disable X-Notify, once this is up and running. This will now be done in X-Service Broke with a Database.
The notification is now configured in the database and can be switched on or off.
You can also choose what type of notification you want to send to the recipients in the database.

The setup is divided into two sections:
Database Configuration.
X-ServiceBroker Configuration “SendXNotifications”.

The setup requirement is that you should have a MSSQL database running.
There are three tables as mentioned below which will be explained in detail in the database setup section steps and in the configuration setup section.

The DB tables will consist of the following tables

XNotification_SendMessageConfigtables.
XNotification_Clientstables.
XNotification_Signaltables.

Database Configuration:

Step.1 - Create a new Database and Call it XLayer using the following:
1. Open Microsoft SQL Management Studio.
2. Connect to the database engine using database administrator credentials.
3. Expand the server node.
4. Right click Database and click on New Database.
5. Enter a database name “XLayer” and click OK to create the database.

Step.2 - Create the XNotification_Signal table.
The XNotification_Signal table is designed with switches that can be turned ON or OFF in the table to send out notifications using SMS, Email and Slack with 1 as on and 0 as off.
The Slack switches is used to send a notification to SXI Slack Notifications #clientnotifications channel.
The XDec switch is used to enable the configuration to generate an Event doc to log a call to ITSM. (You will have to build this config, If you need an example I do have one available that is running at BCX".

The Non-Clustered Index on the table is to prevent any duplicate HBMessages.

The query below will create two examples in the database, one is a FAILED and the other one is REPAIRED for the HBMessage.

When setting up the XNotification_Signal with more HBMessage to the database you will have two HBMessage massages that is created by X-HBChecker manly a FAILED and a REPAIRED HBMessage.

Create the XNotification_Signal table with a Non-Clustered Index by executing the following query:

USE [XLayer]
GO
/****** Object:  Table [dbo].[XNotification_Signal] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[XNotification_Signal](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MD] [nvarchar](max) NULL,
	[Email] [int] NULL,
	[XDec] [int] NULL,
	[SMS] [int] NULL,
	[Slack] [int] NULL,
	[HBMessage] [varchar](320) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[XNotification_Signal] ON 

INSERT [dbo].[XNotification_Signal] ([Id], [MD], [Email], [XDec], [SMS], [Slack], [HBMessage]) VALUES (1, N'ServerName', 1, 1, 0, 0, N'A XPress/Dispatch Heartbeat for ServerName has FAILED')
INSERT [dbo].[XNotification_Signal] ([Id], [MD], [Email], [XDec], [SMS], [Slack], [HBMessage]) VALUES (2, N'ServerName', 1, 1, 0, 0, N'A XPress/Dispatch Heartbeat for ServerName has REPAIRED')
SET IDENTITY_INSERT [dbo].[XNotification_Signal] OFF
SET ANSI_PADDING ON

GO
/****** Object:  Index [NonClusteredIndex] ******/
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[XNotification_Signal]
(
	[HBMessage] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Step.3 - Create the XNotification_SendMessageConfig table.

The table is designed to have the details of the Recipients that will get the Notifications. Some of the fields that we collect recipients to send mails to is Full Name, CellNumber and email address.

The CellNumber is used to send SMS to Orion web service for SMS's (currently used at Gijima)
The Subject and Body are configurable and used as part of sending the email notification.
When sending an email notification, the ~#~ is used to make line break on the Body of the email.

Create the XNotification_SendMessageConfig by executing the following query:

GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[XNotification_SendMessageConfig](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MD] [nvarchar](max) NULL,
	[FullName] [nvarchar](50) NULL,
	[CellNumber] [nvarchar](50) NULL,
	[Recipient] [nvarchar](max) NULL,
	[HBMessage] [nvarchar](max) NULL,
	[Subject] [nvarchar](max) NULL,
	[Body] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[XNotification_SendMessageConfig] ON 

INSERT [dbo].[XNotification_SendMessageConfig] ([Id], [MD], [FullName], [CellNumber], [Recipient], [HBMessage], [Subject], [Body]) VALUES (1, N'ServerName', N'Stephan Booyzen', N'08212312345', N'stephan.booyzen@sxi.co.za', N'A XPress/Dispatch Heartbeat for ServerName has FAILED', N'XPress/Dispatch Heartbeat has failed for ServerName(Event Server @ The Client)', N'Good day,~#~~#~XPress/Dispatch Heartbeat has failed for ServerName(Event Server @ The Client)~#~~#~Yours in Service,~#~ITSM')
INSERT [dbo].[XNotification_SendMessageConfig] ([Id], [MD], [FullName], [CellNumber], [Recipient], [HBMessage], [Subject], [Body]) VALUES (2, N'ServerName', N'Sibusiso Skosana', N'08212312346', N'sibusiso.skosana@sxi.co.za', N'A XPress/Dispatch Heartbeat for ServerName has FAILED', N'XPress/Dispatch Heartbeat has failed for ServerName(Event Server @ The Client)', N'Good day,~#~~#~XPress/Dispatch Heartbeat has failed for ServerName(Event Server @ The Client)~#~~#~Yours in Service,~#~ITSM')
INSERT [dbo].[XNotification_SendMessageConfig] ([Id], [MD], [FullName], [CellNumber], [Recipient], [HBMessage], [Subject], [Body]) VALUES (3, N'ServerName', N'Zwidofhelangani Gabara', N'08212312347', N'zwido.gabara@sxi.co.za', N'A XPress/Dispatch Heartbeat for ServerName has FAILED', N'XPress/Dispatch Heartbeat has failed for ServerName(Event Server @ The Client)', N'Good day,~#~~#~XPress/Dispatch Heartbeat has failed for ServerName(Event Server @ The Client)~#~~#~Yours in Service,~#~ITSM')
INSERT [dbo].[XNotification_SendMessageConfig] ([Id], [MD], [FullName], [CellNumber], [Recipient], [HBMessage], [Subject], [Body]) VALUES (4, N'ServerName', N'Stephan Booyzen', N'08212312345', N'stephan.booyzen@sxi.co.za', N'A XPress/Dispatch Heartbeat for ServerName has REPAIRED', N'XPress/Dispatch Heartbeat has repaired for ServerName(Event Server @ The Client)', N'Good day,~#~~#~XPress/Dispatch Heartbeat has repaired for ServerName(Event Server @ The Client)~#~~#~Yours in Service,~#~ITSM')
INSERT [dbo].[XNotification_SendMessageConfig] ([Id], [MD], [FullName], [CellNumber], [Recipient], [HBMessage], [Subject], [Body]) VALUES (5, N'ServerName', N'Sibusiso Skosana', N'08212312346', N'sibusiso.skosana@sxi.co.za', N'A XPress/Dispatch Heartbeat for ServerName has REPAIRED', N'XPress/Dispatch Heartbeat has repaired for ServerName(Event Server @ The Client)', N'Good day,~#~~#~XPress/Dispatch Heartbeat has repaired for ServerName(Event Server @ The Client)~#~~#~Yours in Service,~#~ITSM')
INSERT [dbo].[XNotification_SendMessageConfig] ([Id], [MD], [FullName], [CellNumber], [Recipient], [HBMessage], [Subject], [Body]) VALUES (6, N'ServerName', N'Zwidofhelangani Gabara', N'08212312347', N'zwido.gabara@sxi.co.za', N'A XPress/Dispatch Heartbeat for ServerName has REPAIRED', N'XPress/Dispatch Heartbeat has repaired for ServerName(Event Server @ The Client)', N'Good day,~#~~#~XPress/Dispatch Heartbeat has repaired for ServerName(Event Server @ The Client)~#~~#~Yours in Service,~#~ITSM')
SET IDENTITY_INSERT [dbo].[XNotification_SendMessageConfig] OFF

Step.4 - Create the XNotification_Clients table.
The XNotification_Clients table is designed to have the server names and client names. This is to create the X-DEC document when the switch is enabled for XDec in the XNotification_Signal table. This is used to log the Events to the specific Client in ITSM.
The Non-Clustered Index on the table is to prevent any duplicate Server names in the Database.

USE [XLayer]
GO
/****** Object:  Table [dbo].[XNotification_Clients] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[XNotification_Clients](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Server] [nvarchar](100) NULL,
	[Client] [nvarchar](100) NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[XNotification_Clients] ON 

INSERT [dbo].[XNotification_Clients] ([Id], [Server], [Client]) VALUES (1, N'ServerName', N'Client')

SET IDENTITY_INSERT [dbo].[XNotification_Clients] OFF
SET ANSI_PADDING ON

GO
/****** Object:  Index [NonClusteredIndex] ******/
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[XNotification_Clients]
(
	[Server] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

X-ServiceBroker Configuration “SendXNotifications”:




  
    7
    
    TRACE
    10
  

  
    
    
  

  
    localhost
    300
  

  

    
    
      
        1.1
        
          
        
        
          
          
          
        
        http://172.25.82.95/UniserverLiveWeb/WebServices/UniserverLiveWebService.asmx   
        
        
        10
        ../../usr/stylesheets/SMSOut.xsl
        30
        
        
      

      
        
          Success Code
          .*200.*
        
        
          Error Code
          .*502.*
          .*503.*
          .*404*
          .*Connection timed out.*+
          .*404Not Found.*+
          .*Message send failed.*+
          Recoverable Error
          Bad response:
        
      
    

    
      
          
              60
          
          
              
                  Success Code
              
              
                  ^.*FWK005.*$
              
          
      

    
    
      
        
          E:\SXI\XPress\XmlOut
          X-DEC~CSS-XLAYER~%UNIQUE%.xml
          ../../usr/stylesheets/CreateXDECDoc.xsl
        
        60
      
      
        
          Success Code
        
        
          ^.*FWK005.*$
        
      
    
    
    
    
      
    

    
    
      
        https://hooks.slack.com/services/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
        JSON
        60
      
      
        
          Success Code
        
        
          Recoverable Error
        
      
    

    
    
      
        165.233.54.12
        
        
        10
        Basic
        25
        noreply@ourservicedesk.com
      
      
        
        
          ^Could\snot\sconnect\sto\sSMTP\shost.*
          ^.*Exception reading response.*$
        
      
    

     
    
      
        10.220.7.61
        sxi
        sXi_pass
        30
        XLayer
        1433
      
    
      
      
        .+Read timed out.+
        .*Invalid state.+
        .*SHUTDOWN is in progress.+
        I/O Error: Connection reset by peer: socket write error
      
    
    
  

  
    CollectXML
    ../../XPress/XmlOut
    
    Send_X_Notification
    ^X-Notify~.*\.xml$
    1
    no
  

  

    
      
        
        

        
        

              
                

                    

                      

                      
                      

                      
                      

                      
                      

                        
                          

                            
                            

                              

                              
                              

                              
                              

                            

                            
                              
                              
                              

                              
                              

                            

                          
                        

                      
                    

                  
                    
                    
                      
                        

                          
                          

                            
                            
                            
                            
                            

                          

                          
                          
                            

                              
                                

                                  

                                    
                                    

                                    
                                    
                                    

                                  

                                  
                                    
                                  

                                
                              

                            
                          

                                                    
                          

                            
                            
                            

                            
                            

                          

                            
                          

                            
                            

                              
                                

                                   
                                  

                                    
                                    

                                    
                                    
                                    
                                  

                                  
                                    
                                    
                                    
                                  

                                
                              

                            
                          

                        
                      
                    
                  
                
               
        
       
      

  

  

    
    
      

        
          Message
        
        
        
        
          
            
              \sfor\s
              
                TempMD
              
            
          
          Omit
        
        
          
            
              \shas\s| 
              
                mMD
              
            
          
          Omit
        
        
          MD
        

      

    

    
    
      

        
          Message_Count
        

      
    

    
    
      

        
          HBMessage
        

        
        
        
          
            
              \sfor\s
              
                TempMD
                                
              
            
          
          Omit
        
        
          
            
              \shas\s| 
              
                mMD
                
              
            
          
          Omit
        
        
        
          MD
        

        
          
            1
          
          Email
        

      
    

    
    
    
      
        
          
            
              \sfor\s
              
                TempMD
                 
              
            
          
          Omit
        
        
          
            
              \shas\s| 
              
                mMD
                
              
            
          
          Omit
        
                
        
          MD
        
      
    

    
    
      

        
          Recipient_Count
        

      
    

    
    
      

        
          To
        

        
          Subject
        

        
          Body
        

        
          cellNumber
        

        
          FullName
        

      
    

    
    
    
      

        
          
            
            stephan.booyzen@sxi.co.za,sibusiso.skosana@sxi.co.za
          
          To
        

        
          Subject
        

        
          
            
              
              
                Good day,
                Please take note that the Notification is not configured in the Database.
                "
                Message
                "
                
                Please configure the Notification in the XLayer Database.
                
                Kind regards,
                SXI Team
              
            
            
              null
              
            
          
          Body
        

      
    

    
    
      
        
          Id
        

        
          MD
        

        
          HBMessage
        

        
          XDec
        

        
          SMS
        

        
          mEmail
        

        
          Slack
        

      
    

    
    
      
        
          
          MESSAGE
        

        
          
            
              
              
                
                BCX Heart Beat Notification  - 
                MESSAGE
              
            
          
          text
        
      
    

    
    
      

        
          Server
        

        
          Client
        

      
    

    
    
      

        
        
          
            X-HBChecker
          
          Agent_Class
        

        
        
          
            X-HBChecker
          
          ESMTool
        

        
        
          
            CSS-XLAYER
          
          MD
        

        
        
          
            
              \shas\s
              
                Agent_Instance
              
            
          
          Omit
        

        
        
          Server
        

        
          Instance_Detail
        

        
          
            X-CALL
          
          Header
        

        
          
            
            
          
          Date
        

        
          
            
          
          Time
        

        
          
            
              \shas\s
              
                State
              
            
          
          State
        

        
        
          
            127.0.0.1
          
          dest
        

        
        
          
            
              
              
                MD
                Server
                Agent_Class
                Agent_Instance
              
            
          
          tmpUID1
        
        
        
          
            
          
          UID
        

        
          UserData
        

        
        
          
            Create
          
          Action
        

        
          Client
        

        
        
          
            X-HBChecker
          
          Source
        

      
    

    
    
      
        
          
          cellNumber
        
        
          
            160
          
          message
        
      
    

    
    
      
        
          
            xlayer
          
          UserName
        
        
          
            xlayer
          
          Password
        
        
          
          cellNumber
        
        
          
            160
          
          message
        
      
    

    
    
      
        
          MD
        

        
          FullName
        

        
          CellNumber
        

        
          Recipient
        

        
        
        
        
        
        
           
            
              ~#~
              
            
          
          Body
        

        
          Subject

Please feel free to test this, if you have any better suggestion please share it here with us.
Zwido, Sibu and Steph.

Blueprints

dummy@example.com (SeanR) — Thu, 15 Aug 2019 14:50:44 +0000

The blueprint for FreshDesk Service Desk has been created.

You can find the required files in the SXI Blueprints Repo.

This points to a Demo SXI Freshdesh service desk system that you can find here. More details can be found in the Readme.txt file.

XLayer Mappings or Stored Proc?

dummy@example.com (SeanR) — Tue, 26 Feb 2019 21:45:16 +0000

This really only applies if we have access to create a stored proc on a database.

The question si a matter of style and maintainability.

Often times we need to check if a record exists in a table. If it exists we need to use that records ID however if there is no record in the table then we need to insert it and return the ID of the newly inserted record.

Assume we have a table called locations the table is made up of the following columns.

id
location
address

Let's look at 2 ways to do this.

Using XLayer Mappings

We start of by collecting the location and address from the source trigger (not discussed here)

Consider the following Configuration (without extensive exception handling) to check if the record exists and insert it if it doesn't.

This is cumbersome as we need to do the select (Collection of the ID field) twice. If the record does not exist in the table which we test by doing the first Collect we insert the record using a Create and then have to do the same Collect again to get the newly created records ID.
The next issue is that for every mapping we should be doing exception handling.

Using a SQL Stored Procedure

This XLayer configuration calls a stored procedure. As you can see the configuration is far simpler and cleaner.

The stored proc that we create in MYSQL looks as follows:

CREATE DEFINER=`root`@`localhost` PROCEDURE `CheckLocations`(
	in sLocation varchar(100),
    in sAddress varchar(255)
)
BEGIN
	IF (SELECT 1 = 1 FROM locations WHERE location = sLocation) THEN
		BEGIN
			SELECT id FROM locations WHERE location = sLocation;
		END;
	ELSE
		BEGIN
			INSERT INTO locations (location, address) VALUES(sLocation, sAddress);
			SELECT LAST_INSERT_ID() AS id;
		END;
	END IF;    
END

As you can see this looks far more elegant and straight forward to maintain.

Let me know your thoughts on this strategy. As mentioned at the outset this can only be done if we have access to create a stored procedure on the database. However this does offer some interesting options going forward should we need to validate or obtain supporting data.

Unable to substitute values containing \ (backslash) in a custom query

dummy@example.com (RuanO) — Thu, 21 Feb 2019 00:43:01 +0000

Found this very helpful, thanks Kev!

AccessControlException MBeanTrustPermission

dummy@example.com (SeanR) — Wed, 21 Nov 2018 09:41:06 +0000

If you copy the permission line (permission javax.management.MBeanTrustPermission “register”;) from this forum into the java.policy file you will have to change the Unicode quotes represented in this forum, to ASCII quotes after the copy, otherwise the line will be ignored and you will still get mbean registration error.

For additional information on java management beans see https://en.wikipedia.org/wiki/Java_Mana … Extensions