I want to understand what is the role of 'Most recent discovery' in cmdb_ci table. What are the scenarios when this field got updated.
This field is updated from integrations, either by ones you make for yourself or ones that come from ServiceNow.
The general intent of this field is to indicate the last time it was known to exist on your network. This allows you to do something such as retire a record in your CMDB for a computer, printer, server, etc after it's not been seen for a period of time.
This is what the Discovery Dashboard uses for Unrefreshed Devices (Beyond Last 30 Days) and so on.
An example from my side is that we had been populating our CMDB from Lansweeper as a custom integration, we populated the Most recent discovery field with tblAssets.LastSeen from Lansweeper. This was the last time Lansweeper saw the device on our network.
These are generally up to you to determine what you want to do with CMDB records not seen for a period of time.