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