Purge log records–excluding audit level logging

 

Some time back, I wrote blog to delete log records older than 15 days from Genesys log database (here) From our support team, we had request to delete log records older than 15 days but want to retain audit level logging for 90 days.

To achieve this, add category filter in the purge query as below

-- To delete attribute records which doesn't have parent record

delete from G_LOG_ATTRS 

where LRID not in (select G_LOG_MESSAGES.ID from G_LOG_MESSAGES)

-- To delete log attribute records older than 90 days excluding audit level logging

delete from G_LOG_ATTRS where LRID in 

(select G_LOG_MESSAGES.ID from G_LOG_MESSAGES where TIMEGENERATED < DATEADD(DAY, -90, GETDATE())and CATEGORY = 0)

-- To delete log records older than 90 days excluding 90 days

delete from G_LOG_MESSAGES 

where TIMEGENERATED < DATEADD(DAY, -90, GETDATE())

and CATEGORY = 0

Calculate MD5 hash from a string

It is a common practice to store passwords in databases using a hash. MD5 (defined in RFC 1321) is a common hash algorithm, and using it from C# is easy.

Here’s an implementation of a method that converts a string to an MD5 hash, which is a 32-character string of hexadecimal numbers.

public string CalculateMD5Hash(string input)
{
    // step 1, calculate MD5 hash from input
    MD5 md5 = System.Security.Cryptography.MD5.Create();
    byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
    byte[] hash = md5.ComputeHash(inputBytes);
 
    // step 2, convert byte array to hex string
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < hash.Length; i++)
    {
        sb.Append(hash[i].ToString("X2"));
    }
    return sb.ToString();
}

An example call:

string hash = CalculateMD5Hash("abcde123");

…returns a string like this:

C3FCD3D76192E4007DFB496CCA67E13B

To make the hex string use lower-case letters instead of upper-case, replace the single line inside the for loop with this line:

sb.Append(hash[i].ToString("x2"));

The difference is the ToString method parameter.