topic Re: Is it possible to use stats inside a join command? in Splunk Search

Is it possible to use stats inside a join command?

buttsurfer — Fri, 10 Feb 2023 10:35:18 GMT

I have a user table which shows which department each user belongs to. I want to join this with another table on User so i can get the respective department for each user. However, I would like to have the headcount of each department showing as well. The below code doesn't work but if it makes sense, i would like to achieve something like that

Re: Is it possible to use stats inside a join command?

kamlesh_vaghela — Fri, 10 Feb 2023 10:30:41 GMT

@buttsurfer

Can you please try this?

Re: Is it possible to use stats inside a join command?

gcusello — Fri, 10 Feb 2023 13:13:16 GMT

Hi @buttsurfer,

yes it's possible, putting attention that in the output of the subsearch there's also the field used as key in the join (as @kamlesh_vaghela suggested!)

Anyway, I don't like join because it's a very slow command to use only when there isn't any other solution (in other words in the 0.01% of the use cases!).

So try to execute your search in this way:

index=... | lookup sims_gds_lookup cn as user OUTPUT department | stats earliest(_time) AS logon_time count(user) as headcount BY department | eval logon_time=strftime(logon_time,"%Y-%m-%d %H:%M:%S") | table logon_time user department headcount

Ciao.

Giuseppe

Re: Is it possible to use stats inside a join command?

bowesmana — Sun, 12 Feb 2023 22:49:52 GMT

As @gcusello says, join + inputlookup is not the way to do lookups. Use lookup directly.

Here is an example that either uses _time as logon_time (Example A) or uses the existing logon_time field (Example B)

index=... ``` Get the logon time for each user/department - use ONE of these two stats commands ``` ``` Example A: Use _time as logon_time and take the earliest time ``` | stats earliest(_time) AS logon_time BY user | eval logon_time=strftime(logon_time,"%Y-%m-%d %H:%M:%S") ``` ------- ``` ``` Example B: Record every logon time for each user ``` | stats count BY user logon_time | fields - count ``` ------- ``` ``` Now get the department for each user ``` | lookup sims_gds_lookup cn as user OUTPUT department ``` Now calculate headcount ``` | eventstats dc(user) as headcount by department | table logon_time user department headcount

This does the aggregations at the start, to minimise the amount of data you need in the lookup, then at the end it finds the department and calculates headcount.