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 | Net | Conversión |
A | Total 1 | 0 |
B | Total 2 | (Total 2 - Total 1) / Total 2 |
C | Total 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
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.
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.
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
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
THANK YOU SO SO SO SO SO SOS SO MUCH LIKE YOU HAVE NO FREAKING IDEA THANK YOU SO SO MUCH