Splunk Search

How to create a Table where each row is the result of a query and afterwards make some operations with it?

andres91302
Communicator

Hello my unafraid nerve of steel fellas! I hope you are having a lot of fun this week...

I have been loosing my sleep and sanity trying to create a table with some values that come from search queries and then add some fields (columms) to this table that will be teh result of some simple math operations, here is an example of what I am trying to achieve and in advace I thank you and praise you for your help I REALLY DO!

[| search 1 | fields A | stats  count(A) as  Total 1]

[| search 2 | fields A | stats  count(B) as  Total 2]

[| search 3 | fields A | stats  count(C) as  Total 3]

I will be obtaining the values Total 1, Total 3 and Total 3 then I want to build this table

Stage NetConversión
ATotal 10
BTotal 2 (Total 2 - Total 1) / Total 2
CTotal 3 (Total 3 - Total 1) / Total 3



I will be so thankful if anyone can help me build this !!! THANKS SO MUCH IN ADVANCE or if you can reference some documentation as well!!! thank you so much

Labels (3)
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

hi @andres91302,
Try this. Here searches 2 and 3 are appended to search 1. Command eventstats is used to copy Total1 values to all the rows.

search 1 | fields A 
| stats  count(A) as Net 
| eval stage="A", Total1=Net
| append 
    [ search search 2 | fields B
    | stats count(B) as Net 
    | eval stage="B"] 
| append 
    [ search search 3 | fields C
    | stats count(C) as Net 
    | eval stage="C"] 
| eventstats max(Total1) as Total1 
| eval Conversión=(Net-Total1)/Total1 
| fields stage, Net, Conversión

 

If this reply helps you, a like would be appreciated.

 

View solution in original post

manjunathmeti
SplunkTrust
SplunkTrust

hi @andres91302,
Try this. Here searches 2 and 3 are appended to search 1. Command eventstats is used to copy Total1 values to all the rows.

search 1 | fields A 
| stats  count(A) as Net 
| eval stage="A", Total1=Net
| append 
    [ search search 2 | fields B
    | stats count(B) as Net 
    | eval stage="B"] 
| append 
    [ search search 3 | fields C
    | stats count(C) as Net 
    | eval stage="C"] 
| eventstats max(Total1) as Total1 
| eval Conversión=(Net-Total1)/Total1 
| fields stage, Net, Conversión

 

If this reply helps you, a like would be appreciated.

 

andres91302
Communicator

It did excaclty just what I wanted it! 10/10, thank you much I've noticed you been participatig in my questions lately and you are such a great help! Please allow me to ask you another question.... how can I modify my code to have this conversion rate calculated by month of each year... and then grahp a timeline with i.. in anoher words it would be only the Stage field and the conversion field with through time... kinda like this:

Stage  Conversion  Time
A                    0                   Jan 2021

B                   0.15                 Jan 2021

C                 0.12                     Jan 2021

A                   0                         Feb 2021

B                 0.16                     Feb 2021

C                 0.11                    Feb 2021


THANK YOU SO MUCH AGEN LIKE THANK YOU SO SO MUCH

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

hi @andres91302,

You can calculate the Time field and count by Time. Try this,

 

search 1 
| fields A 
| eval Time=strftime(_time, "%B %Y") 
| stats count(A) as Net by Time 
| eval Stage="A", Total1=Net 
| append 
    [ search search 2 
    | fields B 
    | eval Time=strftime(_time, "%B %Y") 
    | stats count(B) as Net by Time 
    | eval Stage="B"] 
| append 
    [ search search 3 
    | fields C 
    | eval Time=strftime(_time, "%B %Y") 
    | stats count(C) as Net by Time 
    | eval Stage="C"] 
| eventstats max(Total1) as Total1 by Time 
| eval Conversion=(Net-Total1)/Total1 

 

Add the below command to plot the graph, the x-axis will be Time and y-axis will be COnversion values:

| xyseries Time, Stage, Conversion

 

 

andres91302
Communicator

THANK YOU SO SO SO SO SO SOS SO MUCH LIKE YOU HAVE NO FREAKING IDEA THANK YOU SO SO MUCH

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