How to drop a message

Elastic Stack Logstash
1

the logstash give me three message:

"message" => "# administrator command: Prepare;"

"message" => "# User@Host: gcenter[gcenter] @ [192.168.168.100] Id: 4882345700\n# Schema: gcenter Last_errno: 0 Killed: 0\n# Query_time: 5.883876 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0\n# Bytes_sent: 435\nuse gcenter;\nSET timestamp=1481691552;\n# administrator command: Prepare;",

"message" => "# User@Host: acenter[acenter] @ [192.168.168.106] Id: 4882345703\n# Schema: appcenter Last_errno: 0 Killed: 0\n# Query_time: 5.879482 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0\n# Bytes_sent: 1421\nuse appcenter;\nSET timestamp=1481691552;",

how to configure the logstash.conf to drop the first message???

if [message] =~ "^# Time:" {
drop {}
}
this above didn't work as expect. Is there anyway to fix this problem?
really appreciate your help :slight_smile:

2

The correct syntax is

if [message] =~ /^# Time:/ {

but otherwise it should work. Of course, none of the messages in your example begin with "# Time:".

3

I modified the logstash.conf like this:

if [message] =~ /^# Time:/ {
drop {}
}

and this is mysql-slow.log:

# Time: 161214  4:07:56
# User@Host: root[root] @  [192.168.168.10]  Id: 4866759372
# Schema: dcenter  Last_errno: 0  Killed: 0
# Query_time: 128.778717  Lock_time: 0.000000  Rows_sent: 44812656  Rows_examined: 44812656  Rows_affected: 0
# Bytes_sent: 5333525169
SET timestamp=1481659676;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `game_action_logs`;
# Time: 161214  4:08:07
# User@Host: root[root] @  [192.168.168.10]  Id: 4866759372
# Schema: dcenter  Last_errno: 0  Killed: 0
# Query_time: 11.018205  Lock_time: 0.000000  Rows_sent: 4164122  Rows_examined: 4164122  Rows_affected: 0
# Bytes_sent: 416694449
SET timestamp=1481659687;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `games_summary_d`;

logstash give me one result:

> "message" => "# User@Host: root[root] @ [192.168.168.10] Id: 4866759372\n# Schema: dcenter Last_errno: 0 Killed: 0\n# Query_time: 11.018205 Lock_time: 0.000000 Rows_sent: 4164122 Rows_examined: 4164122 Rows_affected: 0\n# Bytes_sent: 416694449\nSET timestamp=1481659687;\nSELECT /*!40001 SQL_NO_CACHE */ * FROM games_summary_d;"

cos I add this part at the end of grok pattern:(?:# Time:.*\n)?
this is the whole grok pattern:

> ^# User@Host: %{USER:User}\[[^\]]+\] @ (?:(?<clienthost>\S*) )?\[(?:%{IP:Client_IP})?\]\s+Id:\s+%{NUMBER:row_id:int}\n# Schema: %{WORD:Schema}\s+Last_errno: %{NUMBER}\s+Killed: %{NUMBER}\n# Query_time: %{NUMBER:Query_Time:float}\s+Lock_time: %{NUMBER:Lock_Time:float}\s+Rows_sent: %{NUMBER:Rows_Sent:int}\s+Rows_examined: %{NUMBER:Rows_Examined:int}\s+Rows_affected: %{NUMBER:Rows_affected:int}\n(?:# Bytes_sent: %{NUMBER:Byte_sent:int}\n)+(?:use %{DATA:database};\s*\n)?SET\s+timestamp=%{NUMBER:timestamp};(?:\n)?(?<sql>(?<action>\w+)\b.*;)?(?:\n)?(?:# Time:.*\n)?

after I have tried many times, I found out that it would drop not only the first line start with "# Time",and also the whole message that the grok pattern matched next!!!

4

Well, yes. It seems you're using a multiline codec to join the physical lines of a logical event. If you do that you can't use the drop filter.

5

eeeeee.......really thans for your reply
cos this is mysql-slow.log.so I have to use the multiline codec .
now ,how can I drop this line: :sob: ....."# Time: 161214 1:31:33"

I tried this:

grok {
match => { "message" => "^# Time:\s+\d{6}\s+\d{1,2}:\d{2}:\d{2}\n" }
add_tag => [ "time" ]
tag_on_failure =>
}
if "time" in [tags] {
drop {}
}

but it didn't work. and I tried another way like this:

if "_grokparsefailure" in [tags] {
drop {}
}

this did work,but I think it is not a proper way to drop this message.

6

Why do you need to drop that line? Just ignore the timestamp in your grok filter if you don't care about it.

7

cos mysql-slow.log has two different heads. one is :

# Time: 161214 10:21:28
# User@Host: alltechremotecon[alltechremotecon] @  [218.17.162.125]  Id: 4876018124
# Schema: gcenter3x  Last_errno: 0  Killed: 0
# Query_time: 194.159645  Lock_time: 0.000040  Rows_sent: 50810  Rows_examined: 50810  Rows_affected: 0
# Bytes_sent: 8707551
SET timestamp=1481682088;
SELECT * FROM `december_data_upload`;

another one is this:

# administrator command: Prepare;
# User@Host: gcenter[gcenter] @  [192.168.168.100]  Id: 4882345700
# Schema: gcenter  Last_errno: 0  Killed: 0
# Query_time: 5.883876  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 435
use gcenter;
SET timestamp=1481691552;

and I think I can only set the pattern in multiline codec like this:

pattern: ^# User@Host

I add this part at the end of my grok pattern:

(?:# Time:.*\n)?(?:#\s+%{WORD}\s+%{WORD}:\s+\w+;\n)?

it will ignore these two messages "# Time: 161214 10:21:28 " "# administrator command: Prepare;" only if they are not at the first line.
now I am considering the situation when the log rotate,and one of these two messages comes to the first line.....how to solve this :sob:
and really thank you

8

I still don't understand. If you look for the User@Host line in the multiline pattern and don't care about the Time value, why does it matter where the Time value ends up? Just adjust your grok expression to handle all cases. You can also use a mutate filter's gsub option to remove the Time value regardless of where in the string it occurs.

9

...........I am just a rookie here. I still don't know how to modify the logstash.conf . Could you give me a template or example...:sob:
really really thanks :grinning:

I bought a book about ELK...wrote by a chinese author.and it didn't help me a lot. so far ,I could only get help from the internet ,especially from this forum,from you...it's a sad story :sob:

10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.