Ingesting CSV files with Time Stamps - Aria Operations for Logs

YouTube

If you prefer to watch a quick video on this topic check out my YouTube channel.

Use Case

A customer reached out as their Aria Operations for Logs (AOFL) Timestamp parser was not working correctly. AOFL was not using the timestamp in the CSV as the ingestion time.

Below is a quick visual of what they were trying to achieve

I was not familiar with AOFL timestamp parser so dived into the lab.

Shout out to Matt Goodnow who helped me when I ran into the same issue 😊

Pre-Requisites

I am assuming the following is already configured:

• AOFL Server
• AOFL Windows Agent on a Windows Server
• A sample CSV file, you can download a modified version the customer gave me

Configuring the AOFL Agent

First we need to configure the AOFL Windows Agent to pickup the CSV file. AOFL provides an out-of-the-box (OOTB) CSV file parser.

To configure AOFL Agents:

• Click on Management
• Click on Agents
• Select your AOFL Agent and scroll down to the Agent Configuration section

There are two tabs Build and Edit

• Build provides a GUI to configure agents
• Edit provides text editing to configure agents

Below is a breakdown of AOFL agent configuration:

FileLog Configuration

[filelog|CSV]             # Name of the filelog, CSV
directory=c:\demo\csv     # Location to pickup CSV from
include=*.csv             # Wildcard used to pickup all CSV files
parser=access_log         # Name of Parser for the CSV file

Parser Configuration

Important: I used timestamp instead of AccessDate for fields & field_decoder. The customer had it configured as AccessDate which is the column name in the CSV.

The CSV doesn’t need to be changed, we are telling AOFL to treat the AccessDate column as a Timestamp.

Without this change AOFL will not honour the timestamp in the CSV

[parser|access_log]    # Name of Parser, access_log
base_parser=csv        # OOTB Parser to use, csv
debug=yes              # Debugging helps with troubleshooting

# Fields, tell AOFL what each column in the CSV file is
fields=User,Username,FederatedUserID,timestamp,FromIP,SecurityEventTypeID,SecurityEventType,LoginStatusID,LoginStatusName,SessionID,UserAgent,EventSource

# Tells AOFL what Parser to use to decode specified field
field_decoder={"timestamp":"Timestamp_Parser"}  

Timestamp Parser Configuration

[parser|Timestamp_Parser]       # Name of our Field Decoder, Timestamp_Parser
base_parser=timestamp           # OOTB Parser to use
format=%m/%d/%Y %I:%M:%S %p     # Format of timestamp in CSV
debug=yes                       # Debugging helps with troubleshooting

Note: The AOFL doco provides reference on how to configure timestamp format

Below is the complete configuration without comments:

[filelog|CSV]
directory=c:\demo\csv
include=*.csv
parser=access_log

[parser|access_log]
base_parser=csv
fields=User,Username,FederatedUserID,timestamp,FromIP,SecurityEventTypeID,SecurityEventType,LoginStatusID,LoginStatusName,SessionID,UserAgent,EventSource
field_decoder={"timestamp":"Timestamp_Parser"}
debug=yes

[parser|Timestamp_Parser]
base_parser=timestamp
format=%m/%d/%Y %I:%M:%S %p
debug=yes

The configuration is performing the following:

• Ingesting CSV files from a specified directory
• Parsing the CSV file using the OOTB CSV parser
• Decoding a specific field as a timestamp (in our case the AccessDate column)

Save the configuration and AOFL will push the config down to the AOFL Windows Agent.

Testing the Configuration

I’ve opened the CSV file below and updated the date and time fields within 10 minutes of the current time. Why? By default AOFL will only accept timestamps for the past 10 minutes.

The next section covers how to change this setting from 10 mins

Next I dumped the CSV file into C:\demo\csv.

The AOFL agent will automatically ingest and parse CSV files based on our agent configuration earlier. If you wish to review AOFL Agent logs the default location is C:\ProgramData\VMware\LogInsightAgent\log\liagent\

Logging into the AOFL explore logs we can see the AOFL server uses the timestamp from within the CSV file as the log ingestion time.

Updating the 10 minute setting

Chances are you will want AOFL to process CSV files (or any files) and use timestamps beyond the default 10 minute window.

• Navigate to: https://yourAOFLFQDN/internal/config
• Select Show all Settings on the Server settings Configuration screen

• Search for max-tolerated-client-time-drift default value is 600,000 milliseconds (10mins)

• Change to an appropriate value
• I used 31,536,000,000 milliseconds (1 Year)
• Click Save (no need to restart AOFL)

I edited the sample CSV file AccessDate column to 01/01/2024.

I dropped the CSV file into the directory. Let’s view the results in AOFL explore logs.

The screenshot above shows AOFL honoured the ingestion date & time for 01/01/2024…yay!

Conclusion

Hopefully you learned something new about AOFL and timestamps….I know I did. Using provided timestamps can be useful when ingesting logs from a 3rd party system in a format like CSV.

Again shout out to Matt Goodnow who helped me resolve the issue.