How to edit my search to use left "join" against mvexpanded stats values() data?

[chrisfankhauser]

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!

[somesoni2 · Accepted solution]

Try like this

sourcetype=event_log earliest=-7d | eval logins=if(action="login",1,0) 
| stats sum(logins) as actions by client

[chrisfankhauser]

Thanks for the attempt somesoni, but unfortunately this answer doesn't seem to include clients with 0 login actions in the last week.

[somesoni2]

In your query, the main search, is running for which time range?? (the one with | stats values(client) )

This one is should be giving list of all clients which have reported to sourcetype=event_log in last 7 days, even if they don't have a login action.

[chrisfankhauser]

The main search is running against "All Time", to get a list of all clients who have ever logged in. The goal of the query is to find clients who haven't logged in within the last seven days. There may be a better way to do this (saved search, look up table or db query?), but that's what I'm attempting currently. 🙂

[somesoni2]

Well Running all times is always expensive. First I will give you the version that you need, without join:-

sourcetype=event_log earliest=0 | eval logins=if(_time>=relative_time(now(),"-7d") AND action="login",1,0) 
 | stats sum(logins) as actions by client

Now instead of running a search for all time, I would suggest to create a lookup table with initial list of hosts and then run a scheduled (hourly/daily based on how updated you want to keep your client list) search which will process the data for a certain period and merges the list of clients if any new one found. Once you have that you can use that lookup table to compare against last 7d records.

[chrisfankhauser]

This one does the trick, thank you very much somesoni2! I'll mark your answer as accepted. 🙂