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
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
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
you're welcome 🙂
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
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
Hi MuS
This gave me all msisdn that sent either:
yes
no
yes or no
I only want msisdn that send yes and no.