how to combine 2 fields into 1 for visualizing in graph

[HattrickNZ]

Can I combine 2 fields into the 1 using this method:

Combining the 2 fields c84163237 and c84163338 into the 1 field seizureTraffic:

...| timechart  span=1h sum(c84163237) as seizureTraffic,  sum(c84163338) as seizureTraffic by LABEL

Or do I have to do an eval command:

eval field1=c84163237+c84163338

I can't seem to get either working, can anyone advise?

[chimell · Accepted solution]

HattrickNZ

Look at my example search
sourcetype=access_*|eval sr=bytes+other| timechart span=1h sum(bytes) as sb , sum(other) as so , sum(sr) as s by LABEL

and the result

[chimell]

I don't know exactly how are your data

[chimell]

thanks good

[HattrickNZ]

tks chimell, its not something I fully understand myself yet but here is an explanation I have made from above, it is of any help to you, or at least it is hopefully clear:

it has something to do with my search before I do the timechart
I have ...measInfoId=83888114 OR measInfoId=83888115 | timechart... but the thing is to view this field c84163237 I need to select 83888114 and to view this field c84163338 I need to select 83888115.

But I still would have though the method work on this.

So it seems the counter needs to be from the same measInfoId for it to work

E.G. this will work as c84163237 + c84163241 both fall under measInfoId=83888114
... measInfoId=83888114 OR measInfoId=83888115 duration=* LABEL=RNC01SJH | eval test1=c84163237 + c84163241 | timechart span=h sum(c84163237) sum(c84163338) sum(eval(c84163237 + c84163241)) as Sum_Traffic3 sum(test1)
More explanation:
eval test1=c84163237 + c84163241 - this works
sum(eval(c84163237 + c84163241)) as Sum_Traffic3 - this works

[chimell]

Good thanks

[esix_splunk]

I believe what you are running into is the fact that some of these fields do not exist, or are null. You can try piping this in before the stats, the idea would be to fill the null values with numerical values of '0'...

..... | eval c84163237=if(isnull(c84163237),0,c84163237) |eval c84163338=if(isnull(c84163338),0,c84163338) | stats ...

That should ensure all fields are present with numeric values, and stats should work correctly.

Can you post your data set?

[HattrickNZ]

using this seems to make this work sum(eval(c84163237 + c84163241)) as Sum_Traffic3 but still not fully sure why. As the values are genereally 0 or greater by doing .. | stats values(c84163237) values(c84163241)

Sorry, I cannot share my data set.

[HattrickNZ]

esix_splunk comment above works, but still not 100% sure if its the null values or the missing fields. It might be that field1 is associated with group1 and field2 is associated with group2, and I am trying to sum field1 and field2

[chimell]

Hi HattrickNZ
This search code will help you , just run it

...|eval sumTraffic=c84163237+ c84163338| timechart  span=1h sum(c84163237) as sumTraffic1 ,  sum(c84163338) as sumTraffic2 ,sum(sumTraffic) as sumTraffic3 by LABEL

In your result you will have 4 fields : _time , sumTraffic1 , sumTraffic2 ,sumTraffic3

[HattrickNZ]

Sorry but that does not work(the sumTraffic3 column does not appear) and i have many LABELs so there is in fact many columns

[HattrickNZ]

Going back to the start I have found a somewhat alternative solution taht I touched on earlier:

| timechart span=h sum(c84163237) as sT_IC sum(c84163338) as sT_OG by LABEL | addtotals fieldname=NE1 "*NE1" | addtotals fieldname=NE2 "*NE2" |

explanation:
"*NE1" - search for any columns with this name and add them together and store them in a new column NE1
NE1 - this is the name of the new column you created

This will leave me with alot of columns taht I want and alot that I do not want in the stats tab view. Now I just need to work out how to drop the first N columns.

[stephane_cyrill]

try this:

.....|eval seizureTraffic = coalesce(c84163237 , c84163338)|timechart span =1 h sum( seizureTraffic ) by LABEL

[HattrickNZ]

tks but don't think i need coalesce as I am just trying to do a basic sum of 2 fields

E.G. I want Sum_Traffic3 to be equla to 14295.97

_time   Sum_Traffic1    Sum_Traffic2    Sum_Traffic3
2015-04-22  8002.73     6293.24      

Really not sure why this does not work eval seizureTraffic = (c84163237 + c84163338) or any variants of how this is written?

[esix_splunk]

You can test with similar fields..

index=_internal component=Metrics | timechart  sum(eval(kbps+eps)) as sum  by host

[esix_splunk]

You should be able to do something along the lines of...

...|  timechart span=1h sum(eval(c84163237 + c84163338)) as seizureTraffic by LABEL

That should work...

[HattrickNZ]

tks, tried this
timechart span=1d sum(c84163237) as Sum_Traffic1 sum(c84163338) as Sum_Traffic2 sum(eval(c84163237 + c84163338)) as Sum_Traffic3 but does not work, Sum_Traffic1 and 2 are showing values but Sum_Traffic3 is not. Any ideas?

I also tried adding by LABEL at the end but that just removed the Sum_Traffic3 colum completely.

I would rather get this method working but an alterenative would be to just sum the solumns of Sum_Traffic1 and 2 and then drop any fields, but I will only do this if I can't get the other way to work. tks

[esix_splunk]

This works..

| localop | stats count | eval val1=1 | eval val2=2 | eval _time=now() | timechart  sum(eval(val1+val2)) as val3

A resulting val3 of "3". Should work for your dataset also. Note that timechart is dependent on the _time field being in the data.

[HattrickNZ]

tks indeed it does, but for some very frustrating this does not, Sum_Traffic3 & 4 are showing blank
... | eval val1=1 | eval val2=2 | eval f1=c84163237 | eval f2=c84163338 | timechart sum(eval(c84163237 + c84163338)) as Sum_Traffic3, sum(eval(f1+f2)) as Sum_Traffic4 sum(eval(val1+val2)) as val3

I can see all fields that is (c84163237, c84163338,f1,f2,val1 and val2) in the events tab under Interesting Fields. However if I select all fields I can only see val1 and val2 but not the other 4, maybe it has to do with the way the data is fed into splunk. will need to check this...

[esix_splunk]

Breaking apart what you have, your eval's of f1=c.... an f2=... Those c* fields have to have numeric values for the stats to be applied to them properly ( you cannot summate a text value..) Additionally, if you run that stats, it does create the fields, but with no values. And that is what you are seeing...

| localop | stats count | eval c84163237=1 | eval _time=now() | eval c84163338=2 | eval f1=c84163237 | eval f2=c84163338 | timechart sum(eval(c84163237 + c84163338)) as Sum_Traffic3, sum(eval(f1+f2)) as Sum_Traffic4 sum(eval(val1+val2)) as val3

Notice there are no values for val1 and val2, so it returns a null field...

[HattrickNZ]

tks...
can i convert them to numeric?
why can do a summate like this stats sum(c84163237) as "seizureTraffic" by userLabel this would sum them all for yesterday?
Why is the problem when I want to sum the 2 c* fields together?
Am a bit confused.