Rules (XQL)
[INGEST:vendor="cisco", product="isr", target_dataset="cisco_isr_raw", no_hit = keep]
// Support only date time of format: MMM dd YYYY hh:mm:ss.nnn or format: MMM dd hh:mm:ss.nnn. For example: "Jan 01 10:00:00.123" or "Jan 01 2021 10:00:00.123"
filter _raw_log ~= "\:?\s+\*?\w{3}\s+\d{1,2}\s+\d{2}:\d{2}:\d{2}\.\d+:?\s+" or _raw_log ~= "\:\s+\*?\w{3}\s+\d{2}\s+\d{4}\s+\d{2}\:\d{2}\:\d{2}\.\d+\s+" or _raw_log ~= "\:\s+\w{3}\s+\d{1}\s+\d{4}\s+\d+\:\d+\:\d+\.\d+\s?"
| alter
tmp_time_without_year = arrayindex(regextract(_raw_log, "\:?\s+\*?(\w{3}\s+\d{1,2}\s+\d{2}:\d{2}:\d{2}\.\d+):?\s+"),0),
tmp_year = format_timestamp("%Y",_insert_time),
tmp_time_with_year = to_string(arrayindex(regextract(_raw_log, "\:\s+\*?(\w{3}\s+\d{2}\s+\d{4}\s+\d{2}\:\d{2}\:\d{2}\.\d+)\s+"),0)),
tmp_time_with_year2 = to_string(arrayindex(regextract(_raw_log, "\:\s+(\w{3}\s+\d{1}\s+\d{4}\s+\d+\:\d+\:\d+\.\d+)\s?"),0))
| alter
tmp_time_with_year = parse_timestamp("%b %d %Y %H:%M:%E3S", tmp_time_with_year, "+00:00"), // Jan 01 2021 10:00:00.123 UTC (offset 0)
tmp_time_with_year2 = parse_timestamp("%b %e %Y %H:%M:%E3S", tmp_time_with_year2, "+00:00") // Feb 3 2025 22:04:01.776 UTC (offset 0)
| alter
tmp_time_format2 = if(tmp_time_without_year != null and tmp_time_without_year != "", concat(tmp_year, " ", tmp_time_without_year), null)
| alter
tmp_time1_1 = parse_timestamp("%Y %b %e %H:%M:%E*S", tmp_time_format2, "+00:00") // Jan 01 10:00:00.123 UTC (offset 0)
| alter
tmp_timeDiff = timestamp_diff(tmp_time1_1, current_time(), "MILLISECOND")
// Check if the date is a future date
| alter
tmp_year2 = if(tmp_timeDiff > 0, to_string(subtract(to_integer(tmp_year),1)), null)
// Create timestamp minus 1 year if the timestamp is a future one
| alter
tmp_time1_2 = if(tmp_year2 != null, concat(tmp_year2, " ", tmp_time_without_year), null)
| alter
tmp_time1_2 = if(tmp_time1_2 != null, parse_timestamp("%Y %b %e %H:%M:%E*S", tmp_time1_2, "+00:00"), null) // Jan 01 10:00:00.123 UTC (offset 0)
| alter
tmp_time_without_year = coalesce(tmp_time1_2, tmp_time1_1)
| alter
_time = coalesce(tmp_time_with_year, tmp_time_with_year2, tmp_time_without_year)
| fields -tmp_time_without_year, tmp_year, tmp_time_with_year, tmp_time_with_year2, tmp_time_format2, tmp_time1_1, tmp_timeDiff, tmp_year2, tmp_time1_2;