How to randomly join two sets of data?

[POR160893]

Hi,

I have a query where I am first getting 3 fields from an index ("A", "B", "C") describing tasks to be completed and then adjoining a separate lookup containing employee names. I need to use a random function to randomly assign a task to random employees.
So, if there are 10 tasks a 5 employees, each employees would randomly get 2 tasks OR say you have 2 tasks and 5 employees, 2 of these 5 employees would randomly get one of these 2 tasks
Here is my current query:
index="XYZ"
| table "A", "B", "C"
| appendcols
[| inputlookup "DEF"]
| eval rnd = random()

Can you please help?

[PickleRick]

OK. You have two separate problems here.

One is when there is at least as many employers as there are tasks - which means that more than one employer can get the same task

Another one is when there is more tasks than employers which means that employers get multiple tasks.

Each of those situations has some border cases which should be taken into account (e.g. what if the number of tasks isn't divisible by number of employers?) before devising a solution.

[ITWhisperer]

try something like this (assuming the field in your lookup is called name

index="XYZ"
| table "A", "B", "C"
| appendcols
[| inputlookup "DEF"]
| eventstats values(name) as name
| eval name = mvindex(random()%mvcount(name))

Note that if your lookup is longer that your results, you may have events without values in A, B and C

[POR160893]

Hey,

At the moment, my query is not outputting any employee names after using the random function:

What am I doing wrong?

[ITWhisperer]

Where does businessemail come from?

[POR160893]

"businessemail" is the name of the field with the employee names nd comes from the inputlookup. If there are more employees than tasks only a random selection of employees will get a task each. If there are more tasks than employees, each employee will get an even amount of random tasks. Like, there should not be the scenerio that if there are say 10 tasks and all get assigned to 1 random employee. That is the logic I need.

[ITWhisperer]

OK, so why haven't you adopted the approach I suggested? Your current method, assuming it worked, would not assign a person to a task if there were more tasks than people!

[POR160893]

I am currently receiving this where there are some tasks that have not been assigned any employee:

The query for this screenshot is as follows:
index=servicenow sourcetype="snow:change_request" assignment_group_name="SECURITY-NETWORK-L3" description="Security: Firewall Policy Push - Non-Critical" OR description="Security: Firewall Policy Push - Critical firewalls like Ecomm/Extranet/L EMC internet DMZs" status="Open"
| table _time, description, number, status
| dedup number
| appendcols [| inputlookup user_identities.csv | where L7MgrName="ABC" | eval rnd=random() | sort rnd]
| sort rnd
| eval employee_name=businessemail
| where NOT employee_name="*dellteam*"
| table _time, description, number, status, employee_name

Also, no email should contain "delteam", regardless of capilitalization of any letter in this string.

[ITWhisperer]

Again, why are you not adopting the approach I suggested? Try something like this

index=servicenow sourcetype="snow:change_request" assignment_group_name="SECURITY-NETWORK-L3" description="Security: Firewall Policy Push - Non-Critical" OR description="Security: Firewall Policy Push - Critical firewalls like Ecomm/Extranet/L EMC internet DMZs" status="Open"
| table _time, description, number, status
| dedup number
| appendcols [| inputlookup user_identities.csv | where L7MgrName="ABC" | where NOT match(businessemail,"(?i)dellteam") | eventstats values(businessemail) as businessemail]
| eval employee_name=mvindex(businessemail,random()%mvcount(businessemail))
| table _time, description, number, status, employee_name

[POR160893]

Your query solves the issue 99% .... the only issue is some tasks have no employee assigned to them. An employee can be assigned a maximum of 3 tasks. Otherwise, the remainder tasks can be unassigned.
This is what your previous query gave me:

Can you help? Also, thank you so so much for your help so far on this btw, greatly appreciated and karma has been given to all your replies 🙂

[ITWhisperer]

Can you show the search that gave you that result?

Also, now you are changing the goalposts? What is with maximum of 3 tasks? You didn't mention that before!

[POR160893]

Here is the query:
index=servicenow sourcetype="snow:change_request" assignment_group_name="SECURITY-NETWORK-L3" description="Security: Firewall Policy Push - Non-Critical" OR description="Security: Firewall Policy Push - Critical firewalls like Ecomm/Extranet/L EMC internet DMZs" status="Open"
| table _time, description, number, status
| dedup number
| appendcols [| inputlookup user_identities.csv | where L7MgrName="ABC" | where NOT match(businessemail,"(?i)dellteam") | eventstats values(businessemail) as businessemail]
| eval employee_name=mvindex(businessemail,random()%mvcount(businessemail))
| table _time, description, number, status, employee_name


As for the requirement of the 3 tasks, the stakeholder only provided me with that specification within the last 10 minutes. No employee email should appear more than 3 times beside different tasks.

[ITWhisperer]

That search should not have given you empty values for businessemail as every event would have an entry from the businessemail multivalue field.

Anyway, moving on (before the requirement changes again!), try this

index=servicenow sourcetype="snow:change_request" assignment_group_name="SECURITY-NETWORK-L3" description="Security: Firewall Policy Push - Non-Critical" OR description="Security: Firewall Policy Push - Critical firewalls like Ecomm/Extranet/L EMC internet DMZs" status="Open"
| table _time, description, number, status
| dedup number
| appendcols [| inputlookup user_identities.csv 
    | where L7MgrName="ABC" 
    | where NOT match(businessemail,"(?i)dellteam") 
    | eval copy=mvrange(0,3) 
    | mvexpand copy
    | eval rnd=random()
    | sort 0 rnd 
    | fields - copy rnd]
| eval employee_name=businessemail
| table _time, description, number, status, employee_name

[POR160893]

Hi,

This works somewhat but if the number of tasks is less than the number of employeees (like in the screenshot below), the tasks in question should just be assigned different employees without listing employees without any tasks associated with them.

[ITWhisperer]

I said that at the beginning - simply filter them out

| where isnotnull(_time)

[POR160893]

I just added the WHERE conditon you suggested just now but now getting no employee_name:

The query I just ran:
index=servicenow sourcetype="snow:change_request" assignment_group_name="SECURITY-NETWORK-L3" description="Security: Firewall Policy Push - Non-Critical" OR description="Security: Firewall Policy Push - Critical firewalls like Ecomm/Extranet/L EMC internet DMZs" status="Open"
| where isnotnull(_time)
| table _time, description, number, status
| dedup number
| appendcols [| inputlookup user_identities.csv
| where L7MgrName="ABC"
| where NOT match(businessemail,"(?i)dellteam")
| eval copy=mvrange(0,3)
| mvexpand copy
| eval rnd=random()
| sort 0 rnd
| fields - copy rnd]
| eval employee_name=businessemail
| table _time, description, number, status, employee_name

[ITWhisperer · Accepted solution]

It needs to go at the end

[POR160893]

Finally, that worked. Here is the final query that works:
index=servicenow sourcetype="snow:change_request" assignment_group_name="SECURITY-NETWORK-L3" description="Security: Firewall Policy Push - Non-Critical" OR description="Security: Firewall Policy Push - Critical firewalls like Ecomm/Extranet/L EMC internet DMZs" status="Open"
| table _time, description, number, status
| dedup number
| appendcols [| inputlookup user_identities.csv
| where L7MgrName="ABC"
| where NOT match(businessemail,"(?i)dellteam")
| eval copy=mvrange(0,3)
| mvexpand copy
| eval rnd=random()
| sort 0 rnd
| fields - copy rnd]
| eval employee_name=businessemail
| where isnotnull(_time)
| table _time, description, number, status, employee_name

Thank you for all your help and Karma will be granted :)))