How to create a new field and assign value to it on the basis of sub query result?

[abhishekroy168]

Hi all,
I am almost near to my requirement and there is just one issue that I am facing.
I am having 2 columns from a query search A and B and I also have a subquery which returns 2 fields C and D. A and C are the same fields but the number of values it holds may vary.
So comparing A with C and assigning a new variable the value D, my query is:

source="***" |where cmdb_ci="FileServerFloor2"|where state="Resolved"|eval sys_updated_on =strptime(sys_updated_on,"%d/%m/%Y")|eval opened_at =strptime(opened_at,"%d/%m/%Y")|eval time_difference=sys_updated_on-opened_at| eval timediff = time_difference/60/60/24|stats count(category) as cat_count , sum(timediff) as total_days_per_cat by category|eval average_request_duration_per_server = total_days_per_cat / cat_count|rename category as category_per_server|fields category_per_server,average_request_duration_per_server| appendcols [search source="****" |where state="Resolved"|eval sys_updated_on =strptime(sys_updated_on,"%d/%m/%Y")|eval opened_at =strptime(opened_at,"%d/%m/%Y")|eval time_difference=sys_updated_on-opened_at| eval timediff = time_difference/60/60/24|stats count(category) as cat_count1 , sum(timediff) as total_days_per_cat1 by category|eval average_request_duration_all_server = total_days_per_cat1 / cat_count1 | rename category as category_all_server |fields category_all_server, average_request_duration_all_server] 
 |fields category_per_server,average_request_duration_per_server,category_all_server,average_request_duration_all_server

Here I have to filter category for category_per_server present in category_all_server and assign a variable D value average_request_duration_all_server for all matches in between category_per_server in category_all_server .
If possible, please help me out.

[abhishekroy168 · Accepted solution]

instead of proceeding in a complex way just went for join.
Worked for me.