Splunk Search
Highlighted

How to add the results of a chart?

Explorer

source=XXXXX | lookup customerjourney.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction "Serial Number" | stats latest("Availability ") as "Success Rate (%)" latest(HTTPbr__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

Tags (3)
Highlighted

Re: How to add the results of a chart?

Communicator

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.

0 Karma
Highlighted

Re: How to add the results of a chart?

Explorer

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

0 Karma
Highlighted

Re: How to add the results of a chart?

Explorer

subsearch 1
apped[search source="HPRUMMetonlineJuly.csv" | lookup customerjourneymetonlineResponseTime.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]

0 Karma
Highlighted

Re: How to add the results of a chart?

Communicator

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

0 Karma
Highlighted

Re: How to add the results of a chart?

Explorer

Basic search

source="HPRUMMetonlineJuly.csv" | lookup customerjourneymetonlineResponseTime.csv "Page Name" as "Page Name" output "Customer Journey Name" as Transaction "Serial Number" | stats latest("Availability ") as "Success Rate (%)" latest(HTTPbr_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"

0 Karma
Highlighted

Re: How to add the results of a chart?

Explorer

subsearch 1
search source="HPRUMMetonlineJuly.csv" | lookup customerjourneymetonlineResponseTime.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

0 Karma
Highlighted

Re: How to add the results of a chart?

Explorer

subsearch 2

source="HPRUMMetonlineJuly.csv" | lookup customerjourneymetonlineResponseTime.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

0 Karma
Highlighted

Re: How to add the results of a chart?

Explorer

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

0 Karma
Highlighted

Re: How to add the results of a chart?

Communicator

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:

  1. rename the field / column header of the required values so that is does not contain spaces, in this example "SUM".
  2. perform the conditional summary using the SUM field. This result has to share the same name, means SUM in this example.
  3. 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.