Splunk Search

Arithmatic operations based on selected rows

deepa_purushoth
Engager

Hi,
I am a beginer
My data something like this
CategoryGroup | Category |Price
A|A1|1
B|B1|2
B|B2|3
C|C1|1
C|C2|2
C|C3|4
D|D1|3
D|D2|2

I want to add selective rows based on price and display it as new row, further i want to find difference/avg/% and to be displayed each as new rows (col is also ok)
for example
A|A1|1
B|B1|2
B|B2|3
C|C1|1
C|C2|2
C|C3|4
D|D1|3
D|D2|2
E|E1|9 (ie., sum of A,B and C based on selected CategoryGroup
F|f1|7 (ie., difference of E and D , base don selected category group.

Thanks

Tags (1)
0 Karma

cmerriman
Super Champion

do you mean based on selected Category and CategoryGroup?

It looks like, in your example, that E|E1|9 would be the sum of A1, B1, B2, C1, C2, but not C3. and f1 would be the difference of E1 and D2.

you could do something like:

|makeresults |eval data="CategoryGroup=A,Category=A1,Price=1 CategoryGroup=B,Category=B1,Price=2 CategoryGroup=B,Category=B2,Price=3 CategoryGroup=C,Category=C1,Price=1 CategoryGroup=C,Category=C2,Price=2 CategoryGroup=C,Category=C3,Price=4 CategoryGroup=D,Category=D1,Price=3 CategoryGroup=D,Category=D2,Price=2"|makemv data|mvexpand data|rename data as _raw|kv|table CategoryGroup Category Price|eval group1=if((like(Category,"%1") OR like(Category,"%2")) AND in(CategoryGroup,"A","B","C"),Price,0)|appendpipe [stats sum(group1) as Price|fillnull Category value=E1 |fillnull CategoryGroup value=E]|autoregress Price|appendpipe [eval Price=if(Category="E1",Price-Price_p1,null())|replace E* with F* in Category, CategoryGroup |where isnotnull(Price)] |fields - group1 Price_p1

deepa_purushoth
Engager

Hi cmerriman,
Thanks, i tried running this query but am getting error ": This command must be the first command of a search" and also i would like to add the price column is not indexed field it is derived field so i excluded that field while running. Further I restricted my search only to field "Category group" as the Category and Price field value is not static.
Can you please help me here? Thanks in advance

0 Karma

deepa_purushoth
Engager

and also Error in 'eval' command: The 'in' function is unsupported or undefined.

0 Karma

cmerriman
Super Champion

Sorry, what I sent was just as an example, a run anywhere set.
Try to use the query from eval group1 and down.

The in function is also only available after v6.6.
It can be replaced with (CategoryGroup="A" OR CategoryGroup="B" OR CategoryGroup="C")

deepa_purushoth
Engager

Hi cmerriman,

Thanks for the input, based on that i updated my condition and tried, however in total sum i am not getting expected output. ie.,i want to sum category group A, B and C but not D. but my total sum includes D as well ie., A+B+C+D as E and F as E-D which is not correct, please advice.

i simplified my search by considering only category group as category may have more/less items depends on the condition.

my search
|eval group1= if(("Category Group"="A") OR ("Category Group"="B") OR ("Category Group"="C")
AND("Category Group"!= "D"),0,Price)
|appendpipe [stats sum(group1) as Price|fillnull Category value=E1 |fillnull "Category Group" value=E]
|autoregress Price|appendpipe [eval Price=if(Category="E1",Price-Price_p1,null())
|replace E* with F* in Category, "Category Group" |where isnotnull(Price)]
|fields - group1 Price_p1

0 Karma

cmerriman
Super Champion

it looks like your search is actually ONLY summing D as E and then subtracting the last value of D (in your original example, it would be a 2), from E as F.

try this:

|eval group1= if(('Category Group'="A") OR ('Category Group'="B") OR ('Category Group'="C") AND ('Category Group'!= "D"),Price,0)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi

Can you please try this search?

YOUR_SEARCH
| table CategoryGroup, Category,Price
| eval Price1=if(CategoryGroup="D",0,Price) | addcoltotals |fillnull value="E" CategoryGroup |fillnull value="E1" Category | eval Price3=case(CategoryGroup="D",Price * (-1),CategoryGroup="E",Price)  | addcoltotals |fillnull value="F" CategoryGroup |fillnull value="F1" Category | eval Price=case(CategoryGroup="D",Price,CategoryGroup="E" OR CategoryGroup="F",Price3,1=1,Price) | table CategoryGroup Category Price

Thanks

0 Karma

deepa_purushoth
Engager

Hi kamlesh_vaghela.

Thanks, however can you please explain me the query flow, because the total adds up is not correct, it is fetching first and the last row.
I treid adding more than one condition using OR in line 3, but it didnt work...⚠ Error in 'eval' command: The expression is malformed. Expected ).

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi@deepa_purushothaman,

I'm using this search.

| makeresults 
| eval CategoryGroup="A", Category="A1",Price="1" 
| append 
    [| makeresults 
    | eval CategoryGroup="B", Category="B1",Price="2" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="B", Category="B2",Price="3" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="C", Category="C1",Price="1" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="C", Category="C2",Price="2" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="C", Category="C3",Price="3" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="D", Category="D1",Price="3" ] 
| append 
    [| makeresults 
    | eval CategoryGroup="D", Category="D2",Price="2" ] 
| table CategoryGroup, Category,Price 
| eval Price1=if(CategoryGroup="D",0,Price) 
| addcoltotals 
| fillnull value="E" CategoryGroup 
| fillnull value="E1" Category 
| eval Price3=case(CategoryGroup="D",Price * (-1),CategoryGroup="E",Price) 
| addcoltotals 
| fillnull value="F" CategoryGroup 
| fillnull value="F1" Category 
| eval Price=case(CategoryGroup="D",Price,CategoryGroup="E" OR CategoryGroup="F",Price3,1=1,Price) 
| table CategoryGroup Category Price

In this search, with category A, B, C & D,
create a new category "E" which has total value of A + B + C.

| eval Price1=if(CategoryGroup="D",0,Price) 
| addcoltotals 
| fillnull value="E" CategoryGroup 
| fillnull value="E1" Category 

Then creating "F" category which has E - D values.

| eval Price3=case(CategoryGroup="D",Price * (-1),CategoryGroup="E",Price) 
| addcoltotals 
| fillnull value="F" CategoryGroup 
| fillnull value="F1" Category 

Then again assigning Price fields to a particular category.

| eval Price=case(CategoryGroup="D",Price,CategoryGroup="E" OR CategoryGroup="F",Price3,1=1,Price) 

can you please share your sample search in which you get the error?

Thanks

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 ...