Thursday 21 December 2023

Adding Timestamp upon Cell Modification | Excel

Enabling a Timestamp in Excel Upon Cell Modification without Overwriting Existing Timestamps

To achieve this, follow these steps:

Step 1: Enable Iteration in Excel Options

Navigate to Options -> Formula -> Iteration and ensure the option is enabled.

Step 2: Adding Timestamps to Column B on Change in Column C

Suppose your values are in Column C, and you want to track timestamps in Column B. Apply the following formula in Column B:

=IF(C2<>"", IF(B2="", CONCAT(MINUTE(NOW()), ":", SECOND(NOW())), B2), "")


This formula will only update the timestamp in Column B if a change is made in the corresponding cell of Column C, ensuring it doesn't overwrite existing timestamps.


Tuesday 15 August 2023

A Guide to Installing and Configuring SSH Tunneling | Windows

Introduction:

Secure communication and data protection are paramount in today's digital landscape. One effective way to ensure this is through the use of SSH tunneling. In this guide, we'll walk you through the process of installing, setting up, and utilizing SSH tunneling on a Windows operating system. This technique will provide you with a secure and encrypted channel for your data transmission needs.

Step 1: Install OpenSSH as a Windows Optional Feature
  1. Begin by installing OpenSSH as an optional feature on your Windows machine.
  2. Windows will automatically create a new firewall rule to permit inbound SSH access over port 22.
Note that, if you manually download and install OpenSSH, you'll need to create an inbound rule to allow SSH port access.

Step 2: Start and Enable the SSH Service

1. Open PowerShell as an administrator
2. Start the SSH service using the following command 
Start-Service ssh

Tuesday 4 July 2023

How to Download Videos Shared on Microsoft SharePoint and 365 | Microsoft

Is it possible to download a video shared over Microsoft SharePoint or 365 when we can watch it but are unable to download it?

Introduction:

Have you ever encountered a situation where you're able to watch a video shared on Microsoft SharePoint or 365, but you can't seem to find a way to download it? In this guide, we'll walk you through a simple solution that empowers you to download these videos using the versatile media converter, ffmpeg.

Solution: Using FFmpeg Video Converter

FFmpeg is a powerful and universal media converter that can handle a wide range of media tasks, including converting and transcoding videos. Follow these steps to download videos that you can watch but can't download directly from Microsoft SharePoint or 365:

ffmpeg is a universal media converter. It can read a wide variety of inputs - including live grabbing/recording devices - filter, and transcode them into a plethora of output formats.

Saturday 11 February 2023

Search and Replace function DB2 SQL

A function to replace all occurrences of a string in a source string with a new string.

  • Using Db2 REPLACE() function with literal string example
REPLACE(source_string, search_string, replace_string )

SELECT 

    REPLACE('IBM DB2','DB2','Db2') result

FROM 

    sysibm.sysdummy1;

RESULT :  IBM Db2 

Wednesday 18 January 2023

Excel Formulas FILTER, SEQUENCE, TEXTJOIN, TRANSPOSE | Excel

 


Filter using the formula, instead of lookup.

=FILTER(OUTPUT, CONDITION)

EXAMPLE: 

FILTER(A1:A10, B1:B10=C1)


OUTPUT WILL BE SPILLED DOWN. 



TRANSPOSE to spill this output in columns, instead of down in the rows.

=TRANSPOSE( FILTER(OUTPUT, CONDITION) )

EXAMPLE: 

=TRANSPOSE( FILTER(A1:A10, B1:B10=C1) )




TEXTJOIN for a spill in one cell with a separator like a comma.

=TEXTJOIN(",", FILTER(OUTPUT, CONDITION) )


EXAMPLE: 

=TEXTJOIN(",", FILTER(A1:A10, B1:B10=C1) )


SEQUENCE:

to have a list of numbers 

=SEQUENCE(row, column, start, interval)

example: 

1 row and 10 columns, left to right

=SEQUENCE(1,10)


1 column and ten rows, top to down, 

=SEQUENCE(10,1)


1 column and ten rows, top to down, starting from 5 and the interval is 2

=SEQUENCE(10,1, 5,2)



Wednesday 4 January 2023

To extract data for the last month (day 1st to day 30th) from a database in DB2 || IBM DB2

To extract data for the last month (day 1st to day 30th) from a database in DB2, 

Use the below condition in WHERE clause :

TRANSDATE >= THIS_MONTH(CURRENT DATE - 1 MONTH)

AND TRANSDATE < THIS_MONTH(CURRENT DATE)


Umar Adil

Tuesday 1 November 2022

Special Automation Scripts NEW, SAVE,DUPLICATE,AFTERDUPLICATE | Maximo


 <MBONAME>.NEW

 <MBONAME>.DUPLICATE

 <MBONAME>.SAVE


Introduced in 7613.

 <MBONAME>.AFTERDUPLICATE

IBM Maximo Autoscripting Guide – OBJECTNAME.DUPLICATE Script (ibm-maximo-dev.github.io)

Restrict a User to Delete an Attachment from an Approved PO | Maximo

The requirement is to lock the record so that nobody can delete a record if the purchase order is approved.

And the solution is with a small automation script on DOCLINKS object:


object: DOCLINKS

event: BEFORE SAVE

Type: Object Launch Point

Script: Jython


owner=mbo.getOwner()

if owner and owner.getName()=="PO" and owner.getInternalStatus()=="APPR" 

    service.error("","Purchase Order is approved. ")



Also ideal to control it via the signature option, create a new signature option from the application designer and grant access using the relevant security groups then just use it in your code. thanks to Steven Shull.

owner.sigopGranted("DELETEATTACH"): 



Wednesday 14 September 2022

Add plus Sign and inverted comma (+") in each line in variable | BIRT

 for beginning of the line, Find and Replace as below:

^p

^p+"    



 for End of the line, Find and Replace as below:

^p

    "^p

Sunday 12 June 2022

Standalone Lookup Using Relationship - Birt Report Parameter (Without New Attribute) || Maximo

There was a requirement of Month Parameter to be added on report runtime. The Month was already handled in the report query but we need standalone lookup just for month selection. But Maximo didn't allowed standalone lookup without linking attribute to the parameter. 

So we ended up with the below solution to add add Standalone Lookup Using Relationship - Birt Report Parameter (Without New Attribute).

Steps:

  • Add new ALN Domain with Month Values e.g. JAN, FEB, MAR....

  • Add following new Lookup changes in LOOKUPS.XML file with where clause = "DOMAINID = 'ZZMONTH' "

Wednesday 1 June 2022

Extract First or Last Date of the Week | Date functions in DAX | Power BI

 Extract the First Day of this Week

1. Go to DATA, Add new columns, 

Get Current Date Time

= TODAY()                        // current datetime

Get Current Day, Month, MonthName, Quarter, Year etc

= TODAY() // current datetime

= mytable[datecolumn].[Quarter]

= mytable[datecolumn].[Day]

= mytable[datecolumn].[Month]

= mytable[datecolumn].[Year]


Format a date using formula
todayname = format(today(),"dddd")            //format to extract the Name of the date.

Get a Week Number of a Date

weekDay= WEEKDAY(TODAY()) //return the day number, it can take 1,2,3 as parameter to start the count from saturday, sunday or monday.

Get the First Day of the Week
firstDayofThisWeek = today() - WEEKDAY(TODAY()) +1 //today - number of day +1

Get the Last Day of the Week

lastDayofThisWeek = today() - WEEKDAY(TODAY()) +7 //today - number of day +7


Get the First Day of the Month
firstDayofThisMonth = STARTOFMONTH(TODAY()) //START OF MONTH

Get the Last Day of the Month

lastDayofThisMonth = ENDOFMONTH(TODAY()) //END OF MONTH


Populate a table/column with dates in a range

ourdates = calendar(date(2022,01,01) , date(2022-01-31) ) //first and last day as parameter


Thursday 13 January 2022

Delete Outbound Error Messages from Message Reprocessing Application | IBM Maximo


Deleting error messages requires two actions. 

First the DELETEFLAG must be set to 1, then the status changed to RETRY. 

Both of these are done transparently when deleting a message in the Message Reprocessing application. In an escalation they must be done in the correct order.

Warning: Be very careful when using this escalation to delete held messages. If valid transactions are sent in while this escalation is active, and they fail due to a correctable error, they can be deleted.

In the Escalations application, create a new escalation.

Applies To: MAXINTERROR

Condition: status=status='HOLD' and queuename = 'jms/maximo/int/queues/sqout'

Add an escalation point and check the Repeat box.

Add an action. Use the lookup arrow to Go To the Actions app to create the Action.

Create a new Action with the following values:

Object: MAXINTERROR

Type: Set Value

Value: 1

Parameter/Attribute: DELETEFLAG

Save and Return with Value to the Escalations application.

Monday 15 November 2021

Bulk Update any Attribute from List View | Maximo

Update multiple records or even the child records from the list view.
for instance, if you want to update any record in PR table, just write the attribute name
but for Child records like PRLINE; use the standard relationship like PRLINE.orderqty



1.  Create a non-persistent object NP_BULKUPDATE with 2 fields ATTRIBUTEVALUE, ATTRIBUTENAME.

<dialog id="NP_BULKUPDATEDIALOG" label="NP_BULKUPDATEDIALOG" mboname="NP_BULKUPDATE">
    <section id="NP_BULKUPDATEDIALOG_110">
        <sectionrow id="NP_BULKUPDATEDIALOG_111">
            <sectioncol id="NP_BULKUPDATEDIALOG_112">
                <textbox dataattribute="ATTRIBUTENAME" id="NP_BULKUPDATEDIALOG_114" />
                <textbox dataattribute="ATTRIBUTEVALUE" id="NP_BULKUPDATEDIALOG_115" inputmode="required" />
            </sectioncol>
        </sectionrow>
    </section>
    <buttongroup id="NP_BULKUPDATEDIALOG_120">
        <pushbutton default="true" id="NP_BULKUPDATEDIALOG_121" label="OK" mxevent="dialogok" />
        <pushbutton id="NP_BULKUPDATEDIALOG_122" label="Cancel" mxevent="dialogcancel" />
    </buttongroup>
</dialog>

2. create a dialog in application designer and add the above 2 fields into any application, let's say in PR application.



3. Develop an OLP script on NP_BULKUPDATE before Add and good to go. 

from psdi.mbo import MboConstants
from psdi.webclient.system.beans import ResultsBean

# to get the non persistent object
mbo = mboset.getMbo(0)

vATTRIBUTENAME = mbo.getString("ATTRIBUTENAME")
vATTRIBUTEVALUE = mbo.getString("ATTRIBUTEVALUE")

# get AppInstance object to retrieve UI properties list view
app = service.webclientsession().getCurrentApp()
# get the MboSet from the app
parentSet = app.getResultsBean().getMboSet()
#service.error('',str(app))
# this is True if the Select Records check boxes are displayed
isTableSelect = app.getResultsBean().getTableStateFlags().isFlagSet(ResultsBean.TABLE_SUBSELECT_ON)

vParent = parentSet.moveFirst()
while (vParent):
    # if Select Records is displayed we have to take action on selected records only
    # if Select Records is NOT displayed we have to take action on all records
    if vParent.isSelected() or not isTableSelect:
        vParent.setValue(vATTRIBUTENAME, vATTRIBUTEVALUE)
    vParent = parentSet.moveNext()
parentSet.save()


Extension to my confirmation dialog upon deletion post but got the idea from Bruno's article which is here:

Sunday 14 November 2021

Confirmation dialog before Deleting PR Item, and Save It In Worklog | Maximo

Add a confirmation dialog before deleting any line in PR application. So that user will have to enter the reason of deletion and it will be saved in worklog with all the related details like who deleted, reason of deletion, what was the item number and quantity at the time of deletion.

1. add one non-persistent attribute let's say NP_REASON in the PR object.

2. Make/add a dialog in PR.XML, add NP_REASON attribute 

<dialog id="deletedialog" label="deletedialog">
<section id="1567883350896">
<sectionrow id="1567883359616">
<sectioncol id="1567883366027">
<statictext align="left" id="1597323707588" label="Are you sure, you want to delete this line?"/>
<textbox dataattribute="PRNUM" id="1567883419910" inputmode="readonly"/>
<textbox dataattribute="iTEMNUM" id="1597321688221" inputmode="readonly"/>
<textbox dataattribute="NP_REASON" id="1567892172806" inputmode="required"/>
</sectioncol>
<sectioncol id="1567883367821"/>
<sectioncol id="15678833596162"/>
</sectionrow>
</section>
<buttongroup id="mydialog_2">
<pushbutton default="true" id="mydialog_2_1" label="OK" mxevent="dialogok"/>
<pushbutton id="mydialog_2_2" label="Cancel" mxevent="dialogcancel"/>
</buttongroup>
</dialog>

3. In PRLINE tab using application designer, edit the delete button properties and modify the event property to the newly created dialog (deletedialog) as below:

event: deletedialog

4. Write an ALP automation script on PR.NP_REASON attribute, the body is as below:


vWORKLOG = mbo.getMboSet("PR.WORKLOG")
line = vWORKLOG.add()
line.setValue("LOGTYPE","CLIENTNOTE")
line.setValue("DESCRIPTION",str("Item:")+mbo.getString("itemnum")+str(" Qty:") + mbo.getString("ORDERQTY") +str(" UnitCost:") + str(mbo.getDouble("unitcost"))+str(" Reason:") + mbo.getString("NP_REASON"))
line.setValue("SITEID","KAUST")
line.setValue("RECORDKEY",mbo.getString("prnum"),2L)
line.setValue("CLASS","PR",2L)
mbo.delete()
mbo.save()



  

Monday 5 July 2021

SQL to find the System Properties | Maximo

Some times we restore our production database to other development environment, but its a best practice to disable few things first before we run the application. one of the examples is to stop the email/communication templates. 

2 tables are handling System Properties in Maximo:


1. SELECT * FROM MAXPROP;

2. SELECT * FROM MAXPROPVALUE;


Below example to find the SMTP host which is used to send emails from Maximo. We can update this property to disable the emails. 


SELECT MAXPROP.PROPNAME, MAXPROP.DESCRIPTION, MAXPROPVALUE.PROPVALUE

FROM MAXPROP join MAXPROPVALUE ON MAXPROP.PROPNAME = MAXPROPVALUE.PROPNAME

WHERE MAXPROP.PROPNAME = 'mail.smtp.host'

Tuesday 22 June 2021

Enable Maximo Activity Dashboard for Maximo 7.6 | Maximo

There is one system property as below, just set it to True

mxe.webclient.activitydashboard = true


And use this URL to access Activity Dashboard:

http://<hostname>:<port>/maximo/webclient/utility/profiler/PerfMon.jsp

 

 Enabling PerfMon will significantly degrade server performance and so it should not be left enabled any longer then necessary to troubleshoot performance problems, especially on a production server.

Reference: https://www.ibm.com/support/pages/node/1133601

Tuesday 15 June 2021

Inventory Issued Year to Date (YTD) and Reset Annually | Maximo

 

This Year to Date (YTD) attribute in Inventory application shows us the count of issuance of the item during the whole year. 

It's one of the task of Store Manager; to reset it and roll the counter one year down. 

Maximo gives us 3 counters, Last year , 2 Years ago and 3 years ago as depicts in the right image.


There is an option is the Select Action menu as below:


Thursday 3 June 2021

Implicit Variable of Domain Synonym List in Relationship Where Clause - SQL | Maximo

Another way in the SQL condition while creating Relationships in Maximo:


get all synonym values of CLOSE,CAN from work order status


 :&synonymlist&_wostatus[CLOSE,CAN]

is equivalent to

SELECT VALUE FROM SYNONYMDOMAIN WHERE DOMAINID = 'WOSTATUS' AND MAXVALUE IN ( 'APPR')


Usage:

Let's make a relationship and enter this condition within a where clause;


Other useful bind variables are here:

&USERNAME&, &APPNAME& and other special bind variables you can use (ibm.com)

Tuesday 25 May 2021

Add a Hyperlink in Help Menu | Maximo

To add an external Hyperlink to the Help Menu in Maximo.



1. Export MENUS.xml from Application Designer application. 

2.Open it in any text editor and search for <menu id="HELP">

3. Now add this line at your choice as below: 

    <menu id="HELP">
        <menuitem event="maximohelp" id="tophelp" image="menu_icon_apphelp.gif" label="IBM Knowledge Center" licensekey="EAMTOPHELPMENU" value="mxe.help.maximohelplink"/>
        <menuitem event="apphelp" id="help1" image="menu_icon_apphelp.gif" label="Help"/>
        <menuitem id="IBMesupport" label="IBM Electronic Support">
            <menu id="IBMesupportsub">
                <menuitem event="loadlink" id="esupportmam" label="Asset Management Online Support" link="http://www.ibm.com/support/docview.wss?uid=swg21418666&amp;ibmprd=tivmx7"/>
                <menuitem event="loadlink" id="esupport3" label="IBM Support Assistant" link="http://www.ibm.com/software/support/isa?ibmprd=tivmx7"/>
                <menuitem event="loadlink" id="esupport4" label="Support Portal" link="http://www.ibm.com/support/entry/portal?tivmx7"/>
                <menuitem event="loadlink" id="esupport5" label="My Notifications" link="http://www.ibm.com/support/mynotifications?ibmprd=tivmx7"/>
                <menuitem event="loadlink" id="esupport6" label="Cloud and Smarter Infrastructure Software Training and Certification" link="http://www.ibm.com/software/tivoli/education/index.html?ibmprd=tivmx7"/>
                <menuitem event="loadlink" id="IBMcorp" label="IBM Corporation" link="http://www.ibm.com"/>
                <menuitem event="loadlink" id="Google" label="Google Search" link="http://www.google.com"/>
            </menu>
4. Import again into Application designer, and you are done. 





Reference: https://www.ibm.com/support/pages/how-hyperlink-new-browser-window-maximo-title-bar

Monday 24 May 2021

Call Maximo Script from Rest API (http call) (Create SR Example) | Maximo

 Create a simple script in Maximo and Call the Maximo Rest API request to post/get the data.

Let's create a simple automation script, it will create an SR with minimal information. 

1. create a script in automation script application with below code:

from psdi.server import MXServer;
srSet = MXServer.getMXServer().getMboSet("SR", MXServer.getMXServer().getSystemUserInfo());
newSR = srSet.add();
newSR.setValue("TICKETID", request.getQueryParam("ticketid"));
newSR.setValue("DESCRIPTION", request.getQueryParam("description"));
srSet.save();


2. Now send below  request from Postman or any other tool:

http://localhost/maximo/oslc/script/createjsonsr?_lid=maxadmin&_lpwd=maxadmin&ticketid=123&description=test ticket


or We also can send the data in payload

POST http://localhost/maximo/oslc/script/createjsonsr

MAXAUTH <encoded 64bit>

{
    "ticketid" : "123456",
    "siteid" : "BEDFORD",
    "description" : "test SR from http call"
}


 

parameter appended in query string after (?)

request.getQueryParam("variable")

To get the user info from the request

request.getUserInfo()

read and parse the request to get the payload. 

res = JSONObject.parse(output)
vId = res.get("id")
mbo.setValue("description",vId)


Sunday 23 May 2021

SurveyMonkey Integration – Call External API from Automation Script | Maximo

In the continuation of my last post "integration with Survey Monkey through Zapier". 

I had another requirement to implement a customization to directly call the external API of Survey Monkey from Maximo.

Below are the 2 steps procedure, first is the list of APIs we are going to call in a sequence from Maximo Automation Script, and then the detail code I used.

Prerequisites: 

  • Survey Moneky Account, Token
  • Design Survey and enable "Embed First Question" in the Email
  • Create an Email collector 
  • Get the id of the newly created Email Collector


A. Three APIs we are going to call for this project from Automation Script: 

  1. https://api.surveymonkey.net/v3/collectors/{{COLLECTOR_ID}}/messages
    1. {
        "type""invite",
        "subject""Survey for the Ticket {{myextrafield}} {{CustomData1}}",
        "is_branding_enabled"true,
        "embed_first_question"true
      }
  2. https://api.surveymonkey.net/v3/collectors/{{COLLECTOR_ID}}/messages/{{MESSAGE_ID}}/recipients
    1. {     "email""abdulqadeer.el@gmail.com",     "custom_fields": {    "1""c1"},     "extra_fields": {     "myextrafield""1234"   }    }
  3. https://api.surveymonkey.net/v3/collectors/{{COLLECTOR_ID}}/messages/{{MESSAGE_ID}}/send
    1. { }

B. Jython code can be implemented on any object, for testing I am using SR object before-save trigger. 

Monday 17 May 2021

Invoke End Point from Automation Script | Maximo

 To read from any webservice/rest as a client, just use below code to call any existing endpoint with http handler. 


from java.util import HashMap,Calendar

from com.ibm.json.java import JSONObject

response=service.invokeEndpoint("SURVEY-endpoint name",map,body1)

body = JSONObject.parse(response)

mbo.setValue("description",response[:200])

#mbo.setValue("description",body.get("data")[1].get("email"))

Tuesday 11 May 2021

Send SurveyMonkey from Maximo using Zapier (Call a query string from Automation Script)

Calling an external URL from Maximo Automation scirpt to achieve this requirement. 

Requirement was; To send SurveyMonkey's survey to the EndUser/Customer when the  status of Service Request is Resolved.

3 things we needs for this excerise:

  1. Maximo Automation 
  2. Zapier Webhook
  3. Survey Email in Survey Monkey

1. Design a survey in Survey Monkey, Create Collector as Email. 

2. In Zapier make a Catch Hook, get the API  and append any parameter as query string (&ownerid) etc.

3. In Zapier make an Action to connect with Survey Monkey and trigger Contact Send.

Thursday 11 February 2021

Overdue Duration Calculation Between Two Dates (Excluding Weekends) using Automation Scripts | Maximo

We have two dates, the status date: rcareviewdate and Etc date:rcainprgdate. The script below calculates the duration in Hours excluding weekends between these two dates based on attribute status date change. 

When we change the status it will calculate overdue for previous status ETC date and current status change date.

Launch Point Type:     Attribute Launch Point

Attribute:                     CHANGEDATE

  #Imports


from java.util import Date 

from java.text import SimpleDateFormat

from psdi.mbo import MboConstants 

from psdi.server import MXServer

from java.util import Calendar

from psdi.app.common import DateUtility



#Milliseconds Constants

MILLISECONDS_DAY= 86400000 

MILLISECONDS_HOUR = 3600000

MILLISECONDS_MINUTE = 60000 


#Default hours and minutes values

days = 0

hours = 0

minutes = 0 

Tuesday 2 February 2021

Rest / OSLC API Authentications Native or LDAP (Basic/Form) | Maximo

The most common forms of authentication to use with REST API are:

  • Native - (MaxUser Tables)
  • LDAP - (Directory service authentication)

Update: In Mas Manage, The former methods of utilizing maxauth (native authentication) or basic auth (LDAP) are no longer supported because MAS provides its own OIDC identity provider OOB. So we need to utilize API key and utilize the /maximo/api route. 

Native Authentication
   is configured to manage users' credentials within Maximo MaxUsers tables. Maximo is responsible for authenticating all the incoming REST calls. 

The Below System property tells us the application security is off. Also, the HTTP request header and return are shown below:

System Property

 

mxe.useAppSecurity

0

URL call

 

http://localhost/maximo/oslc/login

Request Headers

 

MAXAUTH

<BASE64encoded user:password>

Response in Return

 

JSESSIONID

As cookie to maintain the same session



NOTE: Postman automatically save these cookies and utilize them for subsequent calls, but it can be handled programmatically. 

LDAP
The Below System property tells us the application security is ON.

System Property

 

mxe.useAppSecurity

1

in LDAP Maximo uses directory authentication and validates users' credentials from the directory configured in WebSphere.

In this case, security settings are defined in WEB.XML file in below mentioned 4 files

  • maximo/application/maximoweb.xml
  • maximo/application/maxrestweb.xml
  • maximo/application/mboweb.xml
  • maximo/application/meaweb.xml
  •     in these files <login-config> section needs to be uncommented to use either FORM or BASIC authentication.