Repository: google/timesketch

Issue:
Frontend-ng csv importer parses time wrong
**Describe the bug**
Importing a csv seems to miss a lot of accuracy of datetime.
E.g. this line:
```
2022-11-10T05-07-28+00:00,78Vh3h7,1668056848119860,Time Logged
```

Becomes: 

```
{
   "__ts_timeline_id": 17,
   "datetime": "2022-11-10T05:00:00+00:00",
   "message": "78Vh3h7",
   "timestamp": 1668056400000000,
   "timestamp_desc": "Time Logged"
}
```

Which clearly is wrong

**Expected behavior**
Datetime should be correct



Discussion:
berggren: Take a look at the failing e2e test

Patch:
diff --git a/timesketch/lib/utils.py b/timesketch/lib/utils.py
index 6b6d006d..6493c552 100644
--- a/timesketch/lib/utils.py
+++ b/timesketch/lib/utils.py
@@ -205,12 +205,12 @@ def check_mapping_errors(headers, headers_mapping):
                     f"When create a new column, a default value must be assigned"
                 )
     # 4. check if two or more mandatory headers are mapped
-    #    to the same exisiting header
+    #    to the same existing header
     if len(candidate_headers) != len(set(candidate_headers)):
         raise RuntimeError(
             "Headers mapping is wrong.\n"
             "2 or more mandatory headers are "
-            "mapped to the same exisiting CSV headers"
+            "mapped to the same existing CSV headers"
         )
 
 
@@ -284,7 +284,7 @@ def read_and_validate_csv(
         )
         for idx, chunk in enumerate(reader):
             if headers_mapping:
-                # rename colunms according to the mapping
+                # rename columns according to the mapping
                 chunk = rename_csv_headers(chunk, headers_mapping)
 
             skipped_rows = chunk[chunk["datetime"].isnull()]
@@ -313,15 +313,17 @@ def read_and_validate_csv(
                             idx * reader.chunksize + num_chunk_rows,
                         )
                     )
-                chunk["timestamp"] = chunk["datetime"].dt.strftime("%s%f").astype(int)
+
                 chunk["datetime"] = (
                     chunk["datetime"].apply(Timestamp.isoformat).astype(str)
                 )
+
             except ValueError:
                 logger.warning(
                     "Rows {0} to {1} skipped due to malformed "
                     "datetime values ".format(
-                        idx * reader.chunksize, idx * reader.chunksize + chunk.shape[0]
+                        idx * reader.chunksize,
+                        idx * reader.chunksize + chunk.shape[0],
                     )
                 )
                 continue
@@ -332,6 +334,28 @@ def read_and_validate_csv(
                 _scrub_special_tags(row)
                 # Remove all NAN values from the pandas.Series.
                 row.dropna(inplace=True)
+                MAX_TIMESTAMP_DIFFERENCE = 100000  # 100 seconds
+                # check datetime plausibility
+                if "timestamp" in row and "datetime" in row:
+                    timestamp_calculated = int(
+                        pandas.Timestamp(row["datetime"]).value / 1000
+                    )
+                    if (
+                        abs(row["timestamp"] - timestamp_calculated)
+                        > MAX_TIMESTAMP_DIFFERENCE
+                    ):
+                        error_string = (
+                            "Timestamp difference between {0!s} "
+                            "and {1!s} is too big {2!s}, "
+                            "aborting ingestion."
+                        ).format(
+                            row["timestamp"],
+                            timestamp_calculated,
+                            abs(row["timestamp"] - timestamp_calculated),
+                        )
+                        logger.error(error_string)
+                        raise errors.DataIngestionError(error_string)
+
                 yield row.to_dict()
     except (pandas.errors.EmptyDataError, pandas.errors.ParserError) as e:
         error_string = "Unable to read file, with error: {0!s}".format(e)
@@ -350,7 +374,10 @@ def read_and_validate_redline(file_handle):
     """
 
     csv.register_dialect(
-        "redlineDialect", delimiter=",", quoting=csv.QUOTE_ALL, skipinitialspace=True
+        "redlineDialect",
+        delimiter=",",
+        quoting=csv.QUOTE_ALL,
+        skipinitialspace=True,
     )
     reader = pandas.read_csv(file_handle, delimiter=",", dialect="redlineDialect")
 


Test patch:
diff --git a/test_tools/test_events/sigma_events.csv b/test_tools/test_events/sigma_events.csv
index 430ea89b..6d55714b 100644
--- a/test_tools/test_events/sigma_events.csv
+++ b/test_tools/test_events/sigma_events.csv
@@ -1,5 +1,5 @@
 "message","timestamp","datetime","timestamp_desc","extra_field_1","command","data_type","display_name","filename","packages","parser"
-"A message","123456789","2015-07-24T19:01:01+00:00","Write time","foo","","","","","",""
-"Another message","123456790","2015-07-24T19:01:02+00:00","Write time","bar","","","","","",""
-"Yet more messages","123456791","2015-07-24T19:01:03+00:00","Write time","baz","","","","","",""
-"Install: zmap:amd64 (1.1.0-1) [Commandline: apt-get install zmap]","123456791","2015-07-24T19:01:03+00:00","foo","","Commandline: apt-get install zmap","apt:history:line","GZIP:/var/log/apt/history.log.1.gz","/var/log/apt/history.log.1.gz","Install: zmap:amd64 (1.1.0-1)","apt_history"
+"A message","1437764461000000","2015-07-24T19:01:01+00:00","Write time","foo","","","","","",""
+"Another message","1437764462000000","2015-07-24T19:01:02+00:00","Write time","bar","","","","","",""
+"Yet more messages","1437764463000000","2015-07-24T19:01:03+00:00","Write time","baz","","","","","",""
+"Install: zmap:amd64 (1.1.0-1) [Commandline: apt-get install zmap]","1437764463000000","2015-07-24T19:01:03+00:00","foo","","Commandline: apt-get install zmap","apt:history:line","GZIP:/var/log/apt/history.log.1.gz","/var/log/apt/history.log.1.gz","Install: zmap:amd64 (1.1.0-1)","apt_history"
diff --git a/test_tools/test_events/validate_date_events.csv b/test_tools/test_events/validate_date_events.csv
new file mode 100644
index 00000000..afb919ce
--- /dev/null
+++ b/test_tools/test_events/validate_date_events.csv
@@ -0,0 +1,3 @@
+"message","datetime","timestamp","timestamp_desc","data_type"
+78Vh3h7,2022-11-10T05-07-28+00:00,1668056848119860,Time Logged,This event has a wrong formatted datetime notice the - instead of : in the datetime
+Time offset large,2022-11-09T05-07-28+00:00,1668056848119860,Time Logged,This event has a to large time offset betweeen datetime and timestamp
\ No newline at end of file
diff --git a/timesketch/lib/utils_test.py b/timesketch/lib/utils_test.py
index 0c9654ee..83733e34 100644
--- a/timesketch/lib/utils_test.py
+++ b/timesketch/lib/utils_test.py
@@ -25,6 +25,7 @@ from timesketch.lib.utils import read_and_validate_csv
 from timesketch.lib.utils import check_mapping_errors
 from timesketch.lib.utils import _validate_csv_fields
 from timesketch.lib.utils import rename_jsonl_headers
+from timesketch.lib.errors import DataIngestionError
 
 
 TEST_CSV = "test_tools/test_events/sigma_events.csv"
@@ -75,7 +76,11 @@ class TestUtils(BaseTest):
 
         invalid_mapping_1 = [
             {"target": "datetime", "source": ["DT"], "default_value": None},
-            {"target": "timestamp_desc", "source": ["No."], "default_value": None},
+            {
+                "target": "timestamp_desc",
+                "source": ["No."],
+                "default_value": None,
+            },
             {"target": "message", "source": ["Source"], "default_value": None},
         ]
         # column message already exists
@@ -98,7 +103,11 @@ class TestUtils(BaseTest):
 
         invalid_mapping_3 = [
             {"target": "datetime", "source": ["DT"], "default_value": None},
-            {"target": "timestamp_desc", "source": ["DT"], "default_value": None},
+            {
+                "target": "timestamp_desc",
+                "source": ["DT"],
+                "default_value": None,
+            },
         ]
         # 2 mandatory headers point to the same existing one
         with self.assertRaises(RuntimeError):
@@ -111,7 +120,11 @@ class TestUtils(BaseTest):
 
         valid_mapping_1 = [
             {"target": "datetime", "source": ["DT"], "default_value": None},
-            {"target": "timestamp_desc", "source": ["TD"], "default_value": None},
+            {
+                "target": "timestamp_desc",
+                "source": ["TD"],
+                "default_value": None,
+            },
         ]
         self.assertIs(check_mapping_errors(current_headers, valid_mapping_1), None)
 
@@ -125,14 +138,22 @@ class TestUtils(BaseTest):
         valid_mapping_3 = [
             {"target": "datetime", "source": ["DT"], "default_value": None},
             {"target": "timestamp_desc", "source": None, "default_value": "a"},
-            {"target": "message", "source": ["TD", "file_path"], "default_value": None},
+            {
+                "target": "message",
+                "source": ["TD", "file_path"],
+                "default_value": None,
+            },
         ]
         self.assertIs(check_mapping_errors(current_headers, valid_mapping_3), None)
 
         current_headers = ["DT", "last_access", "TD", "file_path", "T_desc"]
         valid_mapping_4 = [
             {"target": "datetime", "source": ["DT"], "default_value": None},
-            {"target": "timestamp_desc", "source": ["T_desc"], "default_value": None},
+            {
+                "target": "timestamp_desc",
+                "source": ["T_desc"],
+                "default_value": None,
+            },
             {
                 "target": "message",
                 "source": ["T_desc", "file_path"],
@@ -156,12 +177,25 @@ class TestUtils(BaseTest):
             # Call next to work around lazy generators.
             next(_validate_csv_fields(mandatory_fields, df_02))
 
+    def test_datetime_parsing_csv_file(self):
+        """Test for parsing datetime values in CSV file"""
+
+        with self.assertRaises(DataIngestionError):
+            # Call next to work around lazy generators.
+            next(
+                read_and_validate_csv("test_tools/test_events/validate_date_events.csv")
+            )
+
     def test_invalid_JSONL_file(self):
         """Test for JSONL with missing keys in the dictionary wrt headers mapping"""
         linedict = {"DT": "2011-11-11", "MSG": "this is a test"}
         headers_mapping = [
             {"target": "datetime", "source": ["DT"], "default_value": None},
-            {"target": "timestamp_desc", "source": None, "default_value": "test time"},
+            {
+                "target": "timestamp_desc",
+                "source": None,
+                "default_value": "test time",
+            },
             {"target": "message", "source": ["msg"], "default_value": None},
         ]
         lineno = 0
@@ -169,10 +203,18 @@ class TestUtils(BaseTest):
             # Call next to work around lazy generators.
             next(rename_jsonl_headers(linedict, headers_mapping, lineno))
 
-        linedict = {"DT": "2011-11-11", "MSG": "this is a test", "ANOTHERMSG": "test2"}
+        linedict = {
+            "DT": "2011-11-11",
+            "MSG": "this is a test",
+            "ANOTHERMSG": "test2",
+        }
         headers_mapping = [
             {"target": "datetime", "source": ["DT"], "default_value": None},
-            {"target": "timestamp_desc", "source": None, "default_value": "test time"},
+            {
+                "target": "timestamp_desc",
+                "source": None,
+                "default_value": "test time",
+            },
             {
                 "target": "message",
                 "source": ["MSG", "anothermsg"],
@@ -186,11 +228,19 @@ class TestUtils(BaseTest):
 
     def test_valid_JSONL_file(self):
         """Test valid JSONL with valid headers mapping"""
-        linedict = {"DT": "2011-11-11", "MSG": "this is a test", "ANOTHERMSG": "test2"}
+        linedict = {
+            "DT": "2011-11-11",
+            "MSG": "this is a test",
+            "ANOTHERMSG": "test2",
+        }
         lineno = 0
         headers_mapping = [
             {"target": "datetime", "source": ["DT"], "default_value": None},
-            {"target": "timestamp_desc", "source": None, "default_value": "test time"},
+            {
+                "target": "timestamp_desc",
+                "source": None,
+                "default_value": "test time",
+            },
             {
                 "target": "message",
                 "source": ["MSG", "ANOTHERMSG"],