To generate two sets of values from one field

[splunk_rookie]

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!

[493669 · Accepted solution]

@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!

[splunk_rookie]

thanks! it works~

[scelikok]

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?

[splunk_rookie]

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.