How to create new field based on table values?

[satish]

Dear Experts..
Looking for help with a Splunk Query...
I was working on a Splunk Query to identify the Frames connection to the HMC.. Im able to find the HMC's the frame is connected.. If a frame is connected with 2 hmc the active_hmc field will contain both hmc's separated by "_ "
Incase the frame is connected with single HMC.. active_hmc contains only one HMC name..
I would like to create a new field that would contain the actual HMC pair name for each frame..
For the single HMC active frames, I would like to generate the HMC pair data by searching inside the entire table to see if there is a match..

For Example:
==============
if the field value active_hmc=hmc50.. The same field also will have some frames connected wirh 2 hmcs like active_hmc=hmc49_hmc50.

Would like to find that pairs and create a new field hmc_pair in the table with values hmc_pair=hmc49_hmc50.

Could you help me with the query.

Splunk query:
==================
index=aix_os source=hmc
| spath path=hmc_info{} output=LIST
| mvexpand LIST
| spath input=LIST
| where category == "power_frame"
| dedup hmc_name frame_name
| stats values(hmc_name) as hmc_names dc(hmc_name) as hmc_count by frame_serial, frame_name, datacenter
| eval active_hmc=mvjoin(mvsort(hmc_names), "_")
| eval hmc_pair=mvjoin(mvsort(hmc_names), "_")
| eval hmc_redundancy=if(hmc_count=2, if(match(active_hmc, "^([^_]+)_([^_]+)$") AND mvcount(mvdedup(hmc_names))=2, "OK", "missing"), "NOT-OK")
| table active_hmc frame_name, frame_serial,hmc_redundancy, datacenter
| sort +hmc_redundancy

Thanks

[ITWhisperer · Accepted solution]

Try something like this

index=aix_os source=hmc
| spath path=hmc_info{} output=LIST
| mvexpand LIST
| spath input=LIST
| where category == "power_frame"
| dedup hmc_name frame_name
| stats values(hmc_name) as hmc_names dc(hmc_name) as hmc_count by frame_serial frame_name datacenter
| eval hmc_name = hmc_names
| mvexpand hmc_name
| eventstats values(hmc_names) as hmc_pairs by hmc_name
| eval active_hmc=mvjoin(mvsort(hmc_names), "_")
| eval hmc_pair=mvjoin(mvsort(hmc_names), "_")
| eval hmc_redundancy=if(hmc_count=2, if(match(active_hmc, "^([^_]+)_([^_]+)$") AND mvcount(mvdedup(hmc_names))=2, "OK", "missing"), "NOT-OK")
| table active_hmc hmc_pairs frame_name, frame_serial,hmc_redundancy, datacenter
| sort +hmc_redundancy

[satish]

@ITWhisperer 

Many thanks for your solution.  The solution worked for me. 

[scelikok]

Hi @satish,

I can see on your query that active_hmc and hmc_pair both have the same values. Could you please show us the current output of your query (anonymized) as a sample and your desired output? 

[satish]

Hi @scelikok 

Below are the example output details.  The solution provided by @ITWhisperer appears to be working. 

Current Output: 

active_hmc   frame_name frame_serial hmc_redundancy datacenter

hcm5   POWER01    123456         NOT-OK        NYC

hcm5_hmc6  POWER02    876344         OK            NYC

Expected Output: 

active_hmc hmc_pair    frame_name frame_serial hmc_redundancy datacenter

hcm5   hcm5_hcm6     POWER01    123456         NOT-OK        NYC

hcm5_hmc6  hcm5_hmc6     POWER02    876344         OK            NYC

Thank you both for your quick help and support.