topic Re: To generate two sets of values from one field in Splunk Search

To generate two sets of values from one field

splunk_rookie — Tue, 23 Mar 2021 02:43:41 GMT

Hi all,

I am new to Splunk and I would like to seek help from the Splunk Community to generate the net power consumption with the following conditions:

1. I have two sets of assets namely A and B, which generate a power consumption value. To get the net power consumption (NPC), I will need to subtract the power value of A from B. (NPC=powerB-powerA)

2. The power consumption values are accumulated. To obtain the power consumed by each asset, I subtracted the earliest power value from the latest value. (power=latest-earliest)

The problem which I'm facing now is I can't use the same field (power) to generate the power consumption values for asset A and B. I attempted to do a multisearch because I want both my search to run at the same time but the error which I got was "subsearch contains a non-streaming command".

Below is my search query:

| multisearch

[ | stats latest(Power) as latest_A earliest(Power) as earliest_A by A]

[| stats latest(Power) as latest_B earliest(Power) as earliest_B by B]

| eval powerA = latestA - earliestA

| eval powerB = latestB - earliestB

| eval NPC = powerB - powerA

What are the alternatives way or commands which will make my query work? Please help!

Re: To generate two sets of values from one field

scelikok — Tue, 23 Mar 2021 04:11:19 GMT

Hi @splunk_rookie,

Are the assets A and B different fields in the log file ? Or field values? Can you please post a sample data for us to help?

Re: To generate two sets of values from one field

splunk_rookie — Tue, 23 Mar 2021 06:14:13 GMT

Hi @scelikok ,

Assets A and B are from the same field. Sorry, I can't provide the sample data but let me list out the fields which I used.

1. Asset

2. Power

3. _time

Given that the power value generated by Asset A is a regenerative energy and Asset B is consumption energy, I had to split the latest(power) and earliest(power) by Asset A and Asset B before subtracting them to obtain the net power consumption.

Re: To generate two sets of values from one field

493669 — Tue, 23 Mar 2021 06:49:02 GMT

@splunk_rookie Try below-

| stats latest(Power) as latest_power earliest(Power) as earliest_power by Asset | eval powers = latest_power - earliest_power | stats sum(eval(if(Asset=="A",powers,0))) as A sum(eval(if(Asset=="B",powers,0))) as B | eval NPC = B- A

If this helps an upvote will be appreciated!

Re: To generate two sets of values from one field

splunk_rookie — Tue, 23 Mar 2021 08:37:35 GMT

thanks! it works~