topic How to add the results of a chart? in Splunk Search

How to add the results of a chart?

realajay89 — Mon, 28 Sep 2020 17:31:54 GMT

source=XXXXX | lookup customer_journey.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction "Serial Number" | stats latest("Availability ") as "Success Rate (%)" latest(HTTP_br__Errors) as "Number of Failed" latest("Page Time _sec") as "Response Time (Secs)" latest("Total Hits") as "Count" by Transaction, "Serial Number" |eval(Transaction="| convert num("Total Hits") num("Number of Failed")| sort "Serial Number" | table "Transaction" "Success Rate (%)" "Response Time (Secs)" "Count"

Result
Transaction↕ Success Rate (%)↕ Response Time (Secs)↕ Count↕
Login Start 99.22 1.31
Login Result Success 100 2.19
Login Result wrong username | pwd 99.97 0.8

i want to add the results of the chart . can anyone pls help

Login transaction should include sum of "login start" and "Login Result Success" Response Times, i.e. 1.31 + 2.19 = 3.5
Login Wrong User Name/Pwd transaction should include sum of "login start" and "Login Wrong User Name/Pwd" Response Times, i.e. 1.31 + 0.8= 2.1

can anyone pls help on me to get the results

Re: How to add the results of a chart?

norbert_hamel — Wed, 10 Sep 2014 16:07:11 GMT

The main issue here is that the values you want to sum are stored in one column in individual lines, and not in multiple columns side by side within one line.

However, you can try to calculate the sum of the values in lines "Login Start" and "Login Result Success" using the following approach:

| your search | table "Transaction" "Success Rate (%)" "Response Time (Secs)" "Count" 
| rename "Response Time (Secs)" AS SUM
| stats sum(eval(if(Transaction="Login Start" OR Transaction="Login Result  Success",SUM,null()))) as MyResult

Note that I have rename the column with response time only the avoid filed names including spaces, since it might cause issue for calculations.

Once you have that, you can do the same for the other pair of values.

Putting both searches then into sub-searches with | append [subsearch] will append those results to the existing table.

Re: How to add the results of a chart?

realajay89 — Thu, 11 Sep 2014 12:39:03 GMT

Thanks for help but im not sure how to use the result MyResult in to the main search . can u pls guide me through it . at the end of subsearch "---SUM,null()))) as myresult1] . how it can modified to link to the main search ??

Re: How to add the results of a chart?

realajay89 — Mon, 28 Sep 2020 17:33:23 GMT

subsearch 1
apped[search source="HP_RUM_Metonline_July.csv" | lookup customer_journey_metonline_ResponseTime.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction | stats latest("Page Time _sec") as "Response Time (Secs)" by Transaction | rename "Response Time (Secs)" AS SUM
| stats sum(eval(if(Transaction="Login Start" OR Transaction="Login Result Success",SUM,null()))) as myresult1]

Re: How to add the results of a chart?

norbert_hamel — Thu, 11 Sep 2014 12:54:34 GMT

Could you perhaps post the original search you are running? I am not sure if the snippet in the first post is exactly the search you are working on. Then I will try to complete this...

Re: How to add the results of a chart?

realajay89 — Mon, 28 Sep 2020 17:33:29 GMT

Basic search

source="HP_RUM_Metonline_July.csv" | lookup customer_journey_metonline_ResponseTime.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction "Serial Number" | stats latest("Availability ") as "Success Rate (%)" latest(HTTP_br__Errors) as "Number of Failed" latest("Page Time _sec") as "Response Time (Secs)" latest("Total Hits") as "Count" by Transaction, "Serial Number" | sort "Serial Number"| convert num("Total Hits") num("Number of Failed") | table "Transaction" "Success Rate (%)" "Response Time (Secs)" "Count"

Re: How to add the results of a chart?

realajay89 — Mon, 28 Sep 2020 17:33:31 GMT

subsearch 1
search source="HP_RUM_Metonline_July.csv" | lookup customer_journey_metonline_ResponseTime.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction | stats latest("Page Time _sec") as "Response Time (Secs)" by Transaction | rename "Response Time (Secs)" AS SUM
| stats sum(eval(if(Transaction="Login Start" OR Transaction="Login Result Success",SUM,null()))) as myresult1

Re: How to add the results of a chart?

realajay89 — Mon, 28 Sep 2020 17:33:34 GMT

subsearch 2

source="HP_RUM_Metonline_July.csv" | lookup customer_journey_metonline_ResponseTime.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction | stats latest("Page Time _sec") as "Response Time (Secs)" by Transaction| rename "Response Time (Secs)" AS SUM
| stats sum(eval(if(Transaction="Login Start" OR Transaction="Login Result wrong username | pwd",SUM,null()))) as myresult2

Re: How to add the results of a chart?

realajay89 — Thu, 11 Sep 2014 13:12:43 GMT

im not able to return the results from sub searches(myresult 1 and my result2 ) to the main Basic search

Re: How to add the results of a chart?

norbert_hamel — Thu, 11 Sep 2014 15:33:35 GMT

The abstract structure of the search is the following:

<my basic search>

| append 
[ search <my basic search>
| rename column header to "SUM" in order to avoid spaces
| stats sum(eval(if(<condition 1>,SUM,null()))) as SUM
| eval Transaction="Sum for condition 1" ]

| append 
[ search <my basic search>
| rename column header to "SUM" in order to avoid spaces
| stats sum(eval(if(<condition 2>,SUM,null()))) as SUM
| eval Transaction="Sum for condition 2" ]

Means you have one basic search, then you append exactly the same search with

| append [ search <my basic search> ]

Then you need to extend the appended searches to do 3 things:

rename the field / column header of the required values so that is does not contain spaces, in this example "SUM".
perform the conditional summary using the SUM field. This result has to share the same name, means SUM in this example.
create a new field transaction for this result so that you can see in the table what this value is telling you.

The complete search could then read like:

source="HP_RUM_Metonline_July.csv" 
| lookup customer_journey_metonline_ResponseTime.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction "Serial Number" 
| stats latest("Availability ") as "Success Rate (%)" latest(HTTP_br__Errors) as "Number of Failed" latest("Page Time _sec") as "Response Time (Secs)" latest("Total Hits") as "Count"  by Transaction, "Serial Number" 
| sort "Serial Number"
| convert num("Total Hits") num("Number of Failed") 
| table "Transaction" "Success Rate (%)" "Response Time (Secs)" "Count"

| append [ search source="HP_RUM_Metonline_July.csv" 
| lookup customer_journey_metonline_ResponseTime.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction "Serial Number" 
| stats latest("Availability ") as "Success Rate (%)" latest(HTTP_br__Errors) as "Number of Failed" latest("Page Time _sec") as "Response Time (Secs)" latest("Total Hits") as "Count"  by Transaction, "Serial Number" 
| sort "Serial Number"
| convert num("Total Hits") num("Number of Failed") 
| table "Transaction" "Success Rate (%)" "Response Time (Secs)" "Count"
| rename "Response Time (Secs)" AS SUM
| stats sum(eval(if(Transaction="Login Start" OR Transaction="Login Result  Success",SUM,null()))) as SUM 
| eval Transaction="Sum of Login Start and Login Result  Success" ]

| append [ search source="HP_RUM_Metonline_July.csv" 
| lookup customer_journey_metonline_ResponseTime.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction "Serial Number" 
| stats latest("Availability ") as "Success Rate (%)" latest(HTTP_br__Errors) as "Number of Failed" latest("Page Time _sec") as "Response Time (Secs)" latest("Total Hits") as "Count"  by Transaction, "Serial Number" 
| sort "Serial Number"
| convert num("Total Hits") num("Number of Failed") 
| table "Transaction" "Success Rate (%)" "Response Time (Secs)" "Count"
| rename "Response Time (Secs)" AS SUM
| stats sum(eval(if(Transaction="Login Start" OR Transaction="Login Result wrong username | pwd",SUM,null()))) as SUM 
| eval Transaction="Sum of Login Start and Login Result  Success" ]

Maybe you can later on strip out some parts of the appended search, but just for the matter of completeness I have kept it here.

Re: How to add the results of a chart?

realajay89 — Fri, 12 Sep 2014 10:02:35 GMT

Thanks norbert . really appreciate it .. just a final modification. the results coming in a new column named SUM instead of in response time column and in a new row instead of replacing the existing columns . Let me help in making this happen.

Re: How to add the results of a chart?

realajay89 — Fri, 12 Sep 2014 10:35:52 GMT

Transaction Success Rate (%) Response Time (Secs) Count SUM
Login Start 99.92 1.21 1,613,652

Login Result Success 100 1.53 1,296,572

Login Result wrong username | pwd 100 0.76 206,189
View/ Update Account Profile 99.37 0.7 1,371,481

View Policy/ Holdings 100 3.14 556,889
Register For Online Access 100 6.67 883
View Product Detail 100 1.22 34,818

Sum of Login Start and Login Result Success 2.74
Sum of Login Start and Login Result Success 1.97

Re: How to add the results of a chart?

realajay89 — Fri, 12 Sep 2014 12:51:52 GMT

the above is my output . ideally according to the query u gave me Login start and Login Result success has to be replaced with new ones . but this is not happening . instead a new colums names SUM is being included in the chat . can you help pls

Re: How to add the results of a chart?

realajay89 — Fri, 12 Sep 2014 13:13:54 GMT

Transaction↕ Success Rate (%)↕ Response Time (Secs)↕ Count↕

i wish the output is in this form