Hi folks,
I have log data which looks something like this (essentially, it's a historical log of client events):
2016-12-15T11:22:17+00:00 clientip="192.168.0.6", action="login", username="testuser6", client="Foxtrot Enterprises"
2016-12-19T09:18:53+00:00 clientip="192.168.0.7", action="login", username="testuser7", client="Golf Corporation"
2017-01-01T17:49:05+00:00 clientip="192.168.0.1", action="login", username="testuser1", client="Alpha Incorporated"
2017-01-02T11:27:12+00:00 clientip="192.168.0.2", action="login", username="testuser2", client="Bravo Limited"
2017-01-04T15:55:16+00:00 clientip="192.168.0.3", action="login", username="testuser3", client="Charlie LLC"
2017-01-06T14:41:27+00:00 clientip="192.168.0.1", action="login", username="testuser1", client="Alpha Incorporated"
-
The result I'm after is a table of all clients, along with the number of actions they've performed within the last week. For example:
Alpha Incorporated 2
Bravo Limited 1
Charlie LLC 1
Foxtrot Enterprises 0
Golf Corporation 0
I'm approaching this by searching against all data in that log to gather a list of clients who have appeared in at least one event, and then performing a left-join with a subsearch of activity within the last week. Unfortunately, while the "left"-side query and the subsearch seem to work independently, the result with the join is identical to the results of the "left"-side query alone... So, clearly I'm doing something incorrectly.
Here's the query I'm using:
sourcetype=event_log
| stats values(client) as client | mvexpand client
| eval actions=0
| join type=left actions
[ search sourcetype=event_log earliest=-7d | stats count(eval(action="login")) as actions by client ]
It's possible (and/or likely) that I'm approaching this from the wrong direction, so any feedback or assistance is appreciated. Thanks!
... View more