Splunk Search
Highlighted

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

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!

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

Try like this

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

View solution in original post

Highlighted

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

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

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

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. 🙂

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

Highlighted

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

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

0 Karma