Splunk Search

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

abhishekroy168
Path Finder

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.

alt text

0 Karma
1 Solution

abhishekroy168
Path Finder

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

View solution in original post

0 Karma

abhishekroy168
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...