Splunk Search

how to get difference between one value and the other values in a particular column or row?

pramit46
Contributor

my data is like the table below. Column C is what I need to calculate:
A----B----C
10----12----?
25----20----?
23----12----?

What I need in the column C is the difference between the first value and the all the values (including itself) in column A. So the values of the column C should be
10-10=0
10-25=-15
10-23=-13

Any clue, how to do it? Please note that, Column B should not be included in this calculation.

I would also like to know the same can be done row-wise.
Not sure if the question statement clarifies the problem, if not, let me know in the comments, I'll try to clarify it further.

Tags (1)
0 Karma
1 Solution

nryabykh
Path Finder

Hi! Try this:

%your search%
| eventstats first(A) as firstA
| eval C = firstA - A

View solution in original post

nryabykh
Path Finder

Hi! Try this:

%your search%
| eventstats first(A) as firstA
| eval C = firstA - A

pramit46
Contributor

Awesome!!
Thanks @nryabykh, can we also do it row-wise? I doubt, if it is possible.

0 Karma

nryabykh
Path Finder

I'm sorry, i don't fully understand what do you mean as "row-wise". Column C filled per each row, so it could be considered as "row-wise", I believe. Could you clarify your question a little more, maybe with an example?

0 Karma

pramit46
Contributor

okay. let's say, in the following dataset:
A----B----C
10----12----5
25----20----15
23----12----25
?----?----?
if I want to fix the first Row and populate the last row, then can I do it?
so in this case the last row would be:
(10-10=)0----(10-12=)-2----(10-5=)5

Basically the same thing, but row-wise.
I think this can be achieved by doing transpose, but with too many rows, transpose may not work well. Hence, asking.

0 Karma

nryabykh
Path Finder

Well, I managed to do this, but it's a bit tricky 🙂

%your_search%
| eventstats first(A) as firstA
| streamstats count as cnt
| foreach A, B, C
    [eval sub_<<FIELD>>=if(cnt=1, firstA-<<FIELD>>, null)]
| addcoltotals labelfield="cnt" label="newrow"
| foreach A, B, C
    [eval <<FIELD>>=if(cnt="newrow", sub_<<FIELD>>, <<FIELD>>)]
| fields - cnt, firstA, sub*

If your initial search is a quite light, you can use append command. The cons are that you have to duplicate your initial search in append's subsearch:

%your_search%
| append 
    [%your_search%
    | eventstats first(a) as firstA
    | head 1
    | foreach A, B, C
        [eval <<FIELD>>=firstA-<<FIELD>>]
    | fields - firstA]
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...