Using a heartbeat to find time of death on constantly changing databases

Another unusual request I had one day that I enjoyed writing.  The team I worked on employed up to a dozen copies of the same database, some of them in different stages of testing like Prod and UAT and Stage and Stage2, and some of them purposed for data analysis or short investigative projects. 

Only some of these were active, in that they were running our continuous batch processing procedures, which continually imported data from upstream systems and processed it.  At times we wanted to know, in the cases of the inactive systems, when they were taken offline, and made inactive.  When they stopped receiving upstream data. 

So I set up a console application that had two functions.  The first was to update a particular database with a timestamp, running every five minutes or so.  I slipped the command for this into the same scheduler that ran the batch processing, so it would only run on Active databases.  Also, the timestamp was to be encrypted. It was the case sometimes that we absolutely needed to know that our timestamp wasn’t modified by outside sources.  It might be a little overboard but it was an interesting project.

The second part was a method to find this out from any database.  So this same console app, dropped in a NAS script location, allowed anybody on the network to find on their command line the actual last timestamp, decrypted, of a given database.

Here are the two routines:

static private int updateDB()
        {
 byte[] key = {};
 byte[] IV = {  0x49, 0xCD, 0x24, 0x37, 0x95, 0xDB, 0xFE, 0xBD };
            String systimestamp;
            String timecode;
 
            OracleCommand myCmd = new OracleCommand("",Conn);
 try
            {
 
                key = System.Text.Encoding.UTF8.GetBytes("pacmanlx");
 
 
                DESCryptoServiceProvider myDES = new DESCryptoServiceProvider();
 //Rijndael myR = Rijndael.Create();
 
 byte[] inputString = Encoding.UTF8.GetBytes(DateTime.Now.ToString( "MMM-dd" ));
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                CryptoStream cs = new CryptoStream(ms, myDES.CreateEncryptor(key, IV), CryptoStreamMode.Write);
                cs.Write(inputString, 0, inputString.Length);
                cs.FlushFinalBlock();
 //encrypt this baby
                timecode = Convert.ToBase64String(ms.ToArray());
 
 //not this one
                systimestamp = DateTime.Now.ToString();
                myCmd.CommandText = "update some.valid_general_cd v set v.decode = '" +
                systimestamp + "', column_cat_cd = '" + timecode + "' where v.code_id = 4444";
                Conn.Open();
 int myReturn = myCmd.ExecuteNonQuery();
                myCmd.Dispose();
                Conn.Close();
 return myReturn;
 
            }
 catch (Exception ex) {
 if (Conn.State == System.Data.ConnectionState.Open)
                {
                    Conn.Close();
                }
 
 throw new Exception(ex.ToString());
            }
        }
static private void decryptTime(string db)
        {
 byte[] key = { };
 byte[] IV = {  0x49, 0xCD, 0x24, 0x37, 0x95, 0xDB, 0xFE, 0xBD };
 byte[] inputByteArray = { };
            OracleConnection Conn = new OracleConnection("Data Source=" + db + ".somecorp.net;User Id=somedbuser;Password=password;");
            OracleCommand myCmd = new OracleCommand("",Conn);
            String outputString;
 try
            {
                key = System.Text.Encoding.UTF8.GetBytes("pacmanlx");
                DESCryptoServiceProvider myDES = new DESCryptoServiceProvider();
 //Rijndael myR = Rijndael.Create();
                myCmd.CommandText = "select v.column_cat_cd from some.valid_general_cd v where v.code_id = 4444";
                Conn.Open();
 
                OracleDataReader myReader = myCmd.ExecuteReader();
                myReader.Read();
 //object = myReader.getsomething
                outputString = myReader.GetString(0);
 
 //decrypt object
                inputByteArray = Convert.FromBase64String(outputString);
 
 //now decrypt the regular string
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                CryptoStream cs = new CryptoStream(ms, myDES.CreateDecryptor(key, IV),
                                               CryptoStreamMode.Write);
                cs.Write(inputByteArray, 0, inputByteArray.Length);
                cs.FlushFinalBlock();
 
                System.Text.Encoding encoding  = System.Text.Encoding.UTF8;
 //write date to console
                Console.WriteLine(encoding.GetString(ms.ToArray()));
                myReader.Close();
 
            }
 catch (Exception ex)
            {
 if (Conn.State == System.Data.ConnectionState.Open)
                {
                    Conn.Close();
                }
                Console.WriteLine(ex.ToString());
            }