Find results based on field value of 2 events

[carljohan]

I have a logfile in the following format:

[2014-27-03 20:57:15.875 CST] [receivedSmsFileLogger] - message = "Yes", msisdn = "00000000"
[2014-27-03 21:06:21.787 CST] [receivedSmsFileLogger] - message = "Yes", msisdn = "11111111"
[2014-27-03 21:10:28.529 CST] [receivedSmsFileLogger] - message = "Yes", msisdn = "22222222"
[2014-27-03 21:30:51.651 CST] [receivedSmsFileLogger] - message = "No", msisdn = "11111111"
[2014-27-03 21:47:39.900 CST] [receivedSmsFileLogger] - message = "Yes", msisdn = "33333333"

I would like to find any msisdn which sent both yes and no in the same day and display them like:

2014-27-03 21:06:21.787 CST] [receivedSmsFileLogger] - message = "Yes", msisdn = "11111111"
2014-27-03 21:30:51.651 CST] [receivedSmsFileLogger] - message = "No", msisdn = "11111111"

Any suggestions on how that search query would be written?

UPDATE: Solution as per MuS answer:

index=_internal message ="yes" OR message ="no"| eval message=lower(message) | eventstats count(eval(message ="yes")) as yes_count count(eval(message ="no")) as no_count by msisdn  | where yes_count>0 AND no_count>0 | table _time, msisdn, message

[MuS · Accepted solution]

Hi carljohan,

try something like this, it is a run everywhere example and you must adapt it to your needs:

index=_internal method="GET" OR method="POST" | eventstats count(clientip) as ip_count by method  | where ip_count>2 | dedup clientip  | table clientip

this will search for all GET and POST, count IP's for each method and show only IP's which count is more then 2 because only those will have both GET and POST.

hope this helps ...

cheers, MuS

[MuS]

you're welcome 🙂

[carljohan]

Thanks MuS!

With some modifications of your search the solution is:

index=_internal message ="yes" OR message ="no"| eval message=lower(message) | eventstats count(eval(message ="yes")) as yes_count count(eval(message ="no")) as no_count by msisdn | where yes_count>0 AND no_count>0 | table _time, msisdn, message

[MuS]

index=_internal method="GET" OR method="POST" | eventstats count(eval(method="GET")) as GET_count count(eval(method="POST")) as POST_count by clientip | where GET_count>1 AND POST_count>1 | dedup clientip | table clientip, GET_count, POST_count

[carljohan]

Hi MuS

This gave me all msisdn that sent either:
yes
no
yes or no

I only want msisdn that send yes and no.