topic Re: Splunk create new field based on table values in Splunk Search

How to create new field based on table values?

satish — Wed, 12 Apr 2023 16:55:03 GMT

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

Re: Splunk create new field based on table values

scelikok — Wed, 12 Apr 2023 09:43:43 GMT

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?

Re: Splunk create new field based on table values

ITWhisperer — Wed, 12 Apr 2023 09:45:46 GMT

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

Re: Splunk create new field based on table values

satish — Wed, 12 Apr 2023 10:11:47 GMT

@ITWhisperer

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

Re: Splunk create new field based on table values

satish — Wed, 12 Apr 2023 10:17:39 GMT

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.