Is it possible to use stats inside a join command?

[buttsurfer]

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

index=...
| join type=left user
    [| inputlookup lookup 
    | rename cn as user | stats count(user) as headcount by department]
| table logon_time user department headcount 

[bowesmana]

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.

[gcusello]

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

[kamlesh_vaghela]

@buttsurfer 

Can you please try this?

index=...
| lookup sims_gds_lookup cn as user OUTPUT department
| join type=left department
[
    | inputlookup sims_gds_lookup 
    | stats count(user) as headcount by department
]
| table logon_time user department headcount 

KV