Community discussions

MikroTik App
 
Sajad2010
just joined
Topic Author
Posts: 8
Joined: Wed Oct 14, 2020 8:33 am

time in where clause

Fri Jun 24, 2022 3:04 am

Hi
I want use a command same as this:
log print where time > [ 1 hour ago ]
Show logs generated 1 hour ago... how can use 1 hour ago in time clause
Thanks.
 
User avatar
nichky
Forum Guru
Forum Guru
Posts: 1275
Joined: Tue Jun 23, 2015 2:35 pm

Re: time in where clause

Fri Jun 24, 2022 4:29 am

that would be good question. Let me check i'll get back to u
 
User avatar
Jotne
Forum Guru
Forum Guru
Posts: 3279
Joined: Sat Dec 24, 2016 11:17 am
Location: Magrathean

Re: time in where clause

Fri Jun 24, 2022 9:21 am

A quick search:
viewtopic.php?t=77628
 
User avatar
rextended
Forum Guru
Forum Guru
Posts: 11967
Joined: Tue Feb 25, 2014 12:49 pm
Location: Italy
Contact:

Re: time in where clause

Fri Jun 24, 2022 5:29 pm

Use Splunk

I just discovered that are 4 different type of string date/time inside log, not only 3 as I suppose....
when exactly midnight and zero seconds, the time is completly omitted...
 
fragtion
Member Candidate
Member Candidate
Posts: 257
Joined: Fri Nov 13, 2009 10:08 pm
Location: Johannesburg, South Africa

Re: time in where clause

Fri Jun 24, 2022 5:37 pm

Here's a snippet of what I'm using in my fail2ban script, which runs once a minute and checks logs for failed attempts in last minute. All of this extra logic seems necessary because otherwise logs could also return events which took place before ntp sync took place on a previous day.. Maybe something in here proves to be helpful for / can be adapted accordingly to your needs (or maybe someone can improve the script even more, lol)
#define How many failed login attempts from same address triggers temporary ban
:local maxtried "10"

#define How long ban should last (1d: one day, 30m: 30 minutes)
:local bantime "15m"

#define Excluded IP
:local excludeip "1.2.3.4"

#default Rules check and build
:if ([:len [/ip fi fi find comment=fail2ban]]=0) do={/ip fi fi add chain=input src-address-list=ban action=drop comment=fail2ban}

#program Routine
:local timenow [/system clock get time]
:local timeoffset ($timenow - 1m)
/log
:foreach i in=[find message~"login failure"] do={
  :local msgtime [get $i time]
  :if (([:len $msgtime] = 8) && ($msgtime > $timeoffset)) do={
    :local msg [get $i message]
    :local theip [:pick $msg ([:find $msg "from "]+5) [:find $msg " via"]]
    :if ($theip != $excludeip) do={
    :local timesban [:len [/ip fi addr find list=ban address=$theip]]
    :if ($timesban = 0) do={
      :local times [:len [/ip fi addr find list=login_failure address=$theip]]
      :if ($times = 0) do={/ip fi addr add list=login_failure address=$theip timeout=30s comment=1} else={:local thecm [:tonum ([/ip fi addr get [find list=login_failure address=$theip] comment] + 1)];:if ($thecm > $maxtried) do={[/ip fi addr add list=ban timeout=$bantime address=$theip]} else={/ip fi addr set [find list=login_failure address=$theip] comment=$thecm}}
      }
    }
  }
}
 
User avatar
Jotne
Forum Guru
Forum Guru
Posts: 3279
Joined: Sat Dec 24, 2016 11:17 am
Location: Magrathean

Re: time in where clause

Sat Jun 25, 2022 10:44 am

The logging format problem hs been an issue for many many years and Mirkotik are not willing to fix it.
Look at this thread form 2014, nothing has change for 7 years??????
viewtopic.php?t=85015
MT do recommend store the logs on to an syslog server. I now this worsk, since I do have made the Splunk app for Mikrotik (that uses Syslog)
But MT should log data in a standard format, not the 4 different format it does today, that makes it hard to make script based on log time.

Who is online

Users browsing this forum: ko00000000001, own3r1138 and 13 guests