Milliseconds and microseconds are set to zero when uploading a CSV using the CLI client or importer client - Web UI unaffected

[J-A-Sec]

Describe the bug
When uploading a Hayabusa-generated CSV timeline via the CLI client or importer client, milliseconds and microseconds are set to zero in the datetime and timestamp fields. Timestamps tested have been in RFC-3339 and ISO-8601 format.

When uploading the same CSV using the web UI, the timestamps retain their microsecond precision.

To Reproduce
Steps to reproduce the behaviour:

1. Generate a Hayabusa CSV timeline using the Timesketch-verbose profile and with the flag --ISO8601
2. Import the CSV into Timesketch using the web UI and note the datetime & timestamp of the first entry
3. Import the same CSV into Timesketch using the CLI client or importer script, and note the datetime & timestamp of the first entry on each timeline.
4. Compare the datetime and timestamps of the timelines. Microsecond precision is replaced with zeros on the timelines not uploaded via the web UI.

Expected behaviour
Microsecond precision in timestamps are preserved when uploading CSV timelines using the CLI client or importer script.

Screenshots
Snippet of timestamp in Hayabusa CSV timeline:

Timesketch fields of CSV timeline uploaded using the web UI:

Timesketch fields of CSV timeline uploaded using the CLI client:

Desktop (please complete the following information):

• OS: Windows
• Browser: Edge
• Version 120.0.2210.144

[jaegeral]

can you provide a csv sample with all values including the headers?

[J-A-Sec]

Sure, I've attached a small sample. I can confirm that the same behaviour is observed with this file.

Versions:
Timesketch release: 20240717
API Client Version: 20240215
Importer Client Version: 20230721

Sample.csv

[jaegeral]

Thx.

There was some previous art: 30da8aa

and https://github.com/google/timesketch/blob/master/test_tools/test_events/validate_timestamp_conversion.csv might be the right place to add more variation to it.

[jaegeral]

So can confirm, the following:

string = (
                '"total precision","123456789",'
                '"2024-07-24T10:57:02.877297Z","Write time","timestamptest"\n'
            )
            file_object.write(string)

results in:

+-----------+---------------------------+-----------------+------------------+------------------+
| comment   | datetime                  | message         |        timestamp | timestamp_desc   |
|-----------+---------------------------+-----------------+------------------+------------------|
| []        | 2015-07-24 19:01:01+00:00 | ISO8601         |      1.3317e+15  | Write time       |
| []        | 2015-07-24 19:01:01+00:00 | Wrong epoch     | 123456           | Write time       |
| []        | 2015-07-24 19:01:01+00:00 | Notimestamp     |    nan           | Write time       |
| []        | 2024-07-24 10:57:02+00:00 | total precision |      1.23457e+08 | Write time       |
| []        | NaT                       | no_datetime     | 123456           | Write time       |
+-----------+---------------------------+-----------------+------------------+------------------+

[jaegeral]

Based on:

timesketch/timesketch/lib/utils.py

Line 47 in 53b0080

TIMESKETCH_FIELDS = frozenset({"message", "datetime", "timestamp_desc"})

datetime is the leading field, and timestamp is not mandatory.

[jaegeral]

assuming it is here:

timesketch/importer_client/python/timesketch_import_client/importer.py

Line 201 in 9988b1c

data_frame["datetime"] = date.dt.strftime("%Y-%m-%dT%H:%M:%S%z")

[jaegeral]

So we now have unittests to reproduce the problem, stay tuned for hopefully fixing it, but we need to discuss what our expectation here is.

[J-A-Sec]

Thanks @jaegeral.

I have another timestamp issue which may be related - if not I can raise this separately.

Importing a CSV using timesketch_importer from a different data set to the original issue results in a malformed datetime, but only with how it's displayed.

Snippet of the CSV (headers/columns truncated):

RecordNumber,EventRecordId,TimeCreated,EventId
33,33,2023-05-03T07:36:43.9116468+00:00,21
40,40,2023-05-03T07:54:40.1398633+00:00,21
47,47,2023-05-03T08:22:34.8842158+00:00,21

The timestamp format makes no difference to the result; I've tried cutting off the offset, precision below 1 second, removing the "T".

Once imported to the timeline, it appears in the correct place chronologically. The image below shows the same events, the top 3 of each section are from CLI import, the bottom 3 are from web upload.

Strangely, the UNIX timestamp is correct for the CLI upload and incorrect for the web upload:
CLI upload: 1683099403.9116466
Web upload: 1683099403911646

Note: These were uploaded using a formatter.yaml file to define message, datetime, timestamp_desc etc., but the importer fairs no better when excluding a formatter file.

[J-A-Sec]

This latest issue has been referenced here: #3084 (comment)