Search lookup for user in specific group and sub search if user is in other groups from same lookup and set value

[ramuzzini]

I have a system user lookup where all users are at least assigned to the GU group but can also be assigned to other groups.  Example is below.  

GU = General User          SA = System Admin          DBA = DBA Admin

group     username       fname        lname       init   
GU           jadams            John           Adams      M
GU           cnorris             Chris          Norris       P
GU           smills                Sara           Mills          T
GU           smills2             Sara           Mills          A
SA            SA123             John           Adams     M
DBA         DBA123          Sara             Mills          T    

All GU are assigned by first name initial and last name value.  Non-GU groups are given an alpha-numeric value.  Table shows that John Adams and Sara Mills are assigned to more than just the GU group.  I am trying to create a table output to display all GU by last name, first name and initial but identify if user is also assigned to another group.  I don't need to know which group, only that they are assigned to an additional group other than GU. 

My initial research on this shows that I can use a map search or innerjoin but I tried using an inputlookup with a sub search for simplicity.  I have created an eval argument combining the users fname, lname and initial to search against since we have users with at least the same first and last name and username varies between groups.

Here is my search:

| inputlookup users.csv WHERE group = "GU"
| eval name = lname.", ".fname." ".init 

| eval duplicate = if (name IN [
          | inputlookup users.csv WHERE group != "GU"
          | eval name = lname.", ".fname." ".init 
          | fields name ], "Yes",  "No")

| table name, duplicate
|


name                        duplicate
Adams, John M        Yes
Norris, Chris P           No
Mills, Sara T               Yes
Mills, Sara A               No


The search runs without error, but my table shows all users having a "No" value in the duplicate column.  Any help would be greatly appreciated.



 

[yuanliu]

As a fun exercise, yes, you can still reuse this users.csv to hunt for duplicate.  Just use lookup command, not inputlookup. (Generally speaking, inputlookup in the middle is not often useful.) And directly count groups instead of trying to hunt for a different group.

| inputlookup users.csv WHERE group = "GU"
| dedup fname lname init
| eval name = lname.", ".fname." ".init
| lookup users.csv output group
| eval duplicate = if(mvcount(group) > 1, "Yes", "No")
| fields name, duplicate

You can achieve the final desired output using this technique as well.

| inputlookup users.csv WHERE group = "GU"
| dedup fname lname init
| eval name = lname.", ".fname." ".init
| lookup users.csv output group
| eval training=if(mvcount(group)>1, "GU/PU", "GU")
| table  lname  fname  init  training  dep  phone  email

Performance-wise, I suspect stats is better.

[ramuzzini]

This works and is a lot cleaner.  Thank you.

So to modify this a little, I was initially creating a separate dashboard for this but now thinking I can maybe put this all into one table to show the following:

lname     fname     _init   training    dep     phone     email     
Adams    John           M     GU/PU     xx/xx   xx/xx      xx/xx     
Norris      Chris           P     GU             xx/xx   xx/xx      xx/xx    
Mills         Sara            T      GU/PU     xx/xx   xx/xx      xx/xx    
Mills         Sara            A      GU              xx/xx   xx/xx      xx/xx   

I ran the below query and appears to work.  

| inputlookup users.csv
| stats dc(group) AS groups values (dep) AS dep, values(phone) AS phone, values(email) AS email BY fname lname init 
| eval training=if(group>1, "GU/PU", "GU")
| table  lname  fname  init  training  dep  phone  email

Was wonder if there is a better way to add additional values without having to call out the values argument each time.  I tried calling the lookup table again after the eval argument but no luck

Either way, I got what I was looking for so thanks again.

[PickleRick · Accepted solution]

You can use wildcards

| stats dc(group) as groups values(*) as * by fname lname init

[ramuzzini]

The simple things... Thank you.

[PickleRick]

You're overthinking it.

It seems that you need something like 

| inputlookup users.csv
| stats dc(group) as groups by fname lname init

Whis will give you 1 in groups field if the person is only in GU and more than 1 if there are additional groups (assuming your assumption that each user _is_ a member of GU and might be a member of more groups).

Additional remark - rethink your use case. In a sufficiently big organization (and in a small one as well if youre lucky) you'll hit collisions on first/last/middle name.