Splunk Search
Highlighted

How to use the results of one search in another search and include both results in the same chart?

New Member

Hi,

I want to use the result of one search, and then use this result in another search to put it in the same chart. This thing needs to be done for 6 results and I want to display this on a chart.

I don't want to use to join as it takes forever to give the desired result. I tried using eval case and match, but I am unable to use join in that search.

My search is exactly the same for all the searches except the fields referer and proxyurl.
Again, appendcols sometimes mess up with timeinterval giving wrong results for duration greater than 30days.

My search looks:

sourcetype=*index* Referer="*/prepaid/cart/orderreview" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* 
| join session_id[search (Referer="*/prepaid/accessories" AND proxyURL="*/purchase-path-browse/v1/available-offers" http_method="POST") result=*| table session_id] 
| rex field=Referer "http?:\/\/(?[^/]+)"  | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") |
search ENV =* |eval Referer="Purchase Accessories"
|timechart span=1d count(eval(responseStatusCode="200")) as "Successful Transactions"  by Referer |

appendcols[ search sourcetype=*index* Referer="*/prepaid/cart/orderpayment?cartId*" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* 
| join session_id[search (Referer="*/prepaid/browse-devices" AND proxyURL="*/purchase-path-browse/v1/available-offers" http_method="POST") result=*| table session_id] 
| rex field=Referer "http?:\/\/(?[^/]+)"  | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") |
search ENV =* |eval Referer="Single Line Purchase Device"
|timechart span=1d count(eval(responseStatusCode="200")) as "Successful Transactions"  by Referer] |

appendcols[ search sourcetype=*index* Referer="*/prepaid/cart/orderreview" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* 
| join session_id[search (Referer="*/prepaid/myT-Mobile/change-plans-services/rate-plans" AND proxyURL="*/purchase-path-browse/v1/eligible-offers" http_method="POST") result=*| table session_id] 
| rex field=Referer "http?:\/\/(?[^/]+)"  | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") |
search ENV =* |eval Referer="Purhase session pass"
|timechart span=1d count(eval(responseStatusCode="200")) as "Successful Transactions"  by Referer] |

appendcols[ search sourcetype=*index* Referer="*/prepaid/cart/orderreview" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* 
| join session_id[search ( Referer="*/prepaid/myT-Mobile/change-plans-services/services" AND proxyURL="*/purchase-path-browse/v1/conflicts"  http_method="POST") result=*| table session_id] 
| rex field=Referer "http?:\/\/(?[^/]+)"  | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") |
search ENV =* |eval Referer="Change Rate Plan"
|timechart span=1d count(eval(responseStatusCode="200")) as "Successful Transactions"  by Referer] |

appendcols[ search sourcetype=*index* Referer="*/prepaid/cart/orderpayment?cartId=*" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* 
| join session_id[search (Referer="*/prepaid/bring-your-own-device" AND proxyURL="*/utility/v1/general-info/valid-imei?imei=*" http_method="POST") result=*| table session_id] 
| rex field=Referer "http?:\/\/(?[^/]+)"  | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") |
search ENV =* |eval Referer="Single Line BYOD"
|timechart span=1d count(eval(responseStatusCode="200")) as "Successful Transactions"  by Referer] |

appendcols[ search sourcetype=*index* Referer="*/prepaid/cart/orderconfirm" AND proxyURL="*purchase-path-order/v1/orders/*" AND http_method="POST" result=* channel_id=* 
| join session_id[search (Referer="*/prepaid/shop/device-details/*" AND proxyURL="*/purchase-path-browse/v1/product-details/*" http_method="POST") result=*| table session_id] 
| rex field=Referer "http?:\/\/(?[^/]+)"  | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") |
search ENV =* |eval Referer="Change/Upgrade Device"
|timechart span=1d count(eval(responseStatusCode="200")) as "Successful Transactions"  by Referer] 
0 Karma
Highlighted

Re: How to use the results of one search in another search and include both results in the same chart?

Legend

Have you tried getting rid of all the subsearches (join & appendcols) and doing just this?

sourcetype=index http_method="POST" result= channel_id= | timechart span=1d count by referer
0 Karma
Highlighted

Re: How to use the results of one search in another search and include both results in the same chart?

SplunkTrust
SplunkTrust

I see your delema, and while im sure there is a better way to join all these together, I used subsearches and append. I removed your rex command because it didnt have any field names it was extracting. I also added responseStatusCode="200" to every search instead of doing the eval in the charting commands. Finally, I used stats count by referer _time on all the searches and roll those up into one timechart at the very end.

BTW referrer has 4 Rs, not 3.

sourcetype=*index* Referer="*/prepaid/cart/orderreview" proxyURL="*/payment/v1/orders/payment-method" http_method="POST" result=* channel_id=* responseStatusCode="200"
 [ 
 search Referer="*/prepaid/accessories" proxyURL="*/purchase-path-browse/v1/available-offers" http_method="POST" responseStatusCode="200" result=*
 | fields session_id 
 | format 
 ]
| eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") 
| search ENV =* 
| eval Referer="Purchase Accessories"
| stats count by Referer, _time 
| append
 [ 
 search sourcetype=*index* Referer="*/prepaid/cart/orderpayment?cartId*" proxyURL="*/payment/v1/orders/payment-method" http_method="POST" result=* channel_id=* responseStatusCode="200" 
  [
  search Referer="*/prepaid/browse-devices" proxyURL="*/purchase-path-browse/v1/available-offers" http_method="POST" result=* responseStatusCode="200"
  | fields session_id
  | format
  ]  
 | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") 
 | search ENV =* 
 | eval Referer="Single Line Purchase Device"
 | stats count by Referer, _time
 ] 
| append
 [ 
 search sourcetype=*index* Referer="*/prepaid/cart/orderreview" proxyURL="*/payment/v1/orders/payment-method" http_method="POST" result=* channel_id=* responseStatusCode="200" 
  [
  search Referer="*/prepaid/myT-Mobile/change-plans-services/rate-plans" proxyURL="*/purchase-path-browse/v1/eligible-offers" http_method="POST" result=* responseStatusCode="200"
  | fields session_id
  | format
  ] 
 | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") 
 | search ENV =* 
 | eval Referer="Purhase session pass"
 | stats count by Referer, _time
 ] 
| append
 [ 
 search sourcetype=*index* Referer="*/prepaid/cart/orderpayment?cartId=*" proxyURL="*/payment/v1/orders/payment-method" http_method="POST" result=* channel_id=* responseStatusCode="200" 
  [
  search Referer="*/prepaid/bring-your-own-device" proxyURL="*/utility/v1/general-info/valid-imei?imei=*" http_method="POST" result=* responseStatusCode="200"
  | fields session_id
  | format
  ] 
 | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") 
 | search ENV =* 
 | eval Referer="Single Line BYOD"
 | stats count by Referer
 ] 
| append
 [ 
 search sourcetype=*index* Referer="*/prepaid/cart/orderconfirm" proxyURL="*purchase-path-order/v1/orders/*" http_method="POST" result=* channel_id=* responseStatusCode="200"
  [
  search Referer="*/prepaid/shop/device-details/*" proxyURL="*/purchase-path-browse/v1/product-details/*" http_method="POST" result=* responseStatusCode="200"
  | fields session_id
  | format
  ] 
 | eval ENV=case(DOMAIN LIKE "%preqat%", "PREQAT", DOMAIN LIKE "%qat%", "QAT",DOMAIN LIKE "%preprod%","PREPROD") 
 | search ENV =* 
 | eval Referer="Change/Upgrade Device"
 | stats count by Referer
 ]
| timechart count AS "Successful Transactions" by Referer
0 Karma
Highlighted

Re: How to use the results of one search in another search and include both results in the same chart?

SplunkTrust
SplunkTrust

Give this a try

sourcetype=*index* Referer="*/prepaid/cart/orderreview" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* [search (Referer="*/prepaid/accessories" AND proxyURL="*/purchase-path-browse/v1/available-offers" http_method="POST") result=* | stats count by session_id | table session_id]
| rex field=Referer "http?:\/\/(?<DOMAIN>[^/]+)" | WHERE responseStatusCode="200" and (DOMAIN LIKE "%preqat%" or DOMAIN LIKE "%qat%"or DOMAIN LIKE "%preprod%") | timechart span=1d count as "Purchase Accessories" | 
 append[ search sourcetype=*index* Referer="*/prepaid/cart/orderpayment?cartId*" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* 
 [search (Referer="*/prepaid/browse-devices" AND proxyURL="*/purchase-path-browse/v1/available-offers" http_method="POST") result=* | stats count by session_id| table session_id] 
 | rex field=Referer "http?:\/\/(?<DOMAIN>[^/]+)" | WHERE responseStatusCode="200" and (DOMAIN LIKE "%preqat%" or DOMAIN LIKE "%qat%"or DOMAIN LIKE "%preprod%")
 |timechart span=1d count as "Single Line Purchase Device"] | 
 append[ search sourcetype=*index* Referer="*/prepaid/cart/orderreview" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* 
 [search (Referer="*/prepaid/myT-Mobile/change-plans-services/rate-plans" AND proxyURL="*/purchase-path-browse/v1/eligible-offers" http_method="POST") result=* | stats count by session_id| table session_id] 
 | rex field=Referer "http?:\/\/(?<DOMAIN>[^/]+)" | WHERE responseStatusCode="200" and (DOMAIN LIKE "%preqat%" or DOMAIN LIKE "%qat%"or DOMAIN LIKE "%preprod%")
 |timechart span=1d count as "Purhase session pass"] | 
 append[ search sourcetype=*index* Referer="*/prepaid/cart/orderreview" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* 
 search ( Referer="*/prepaid/myT-Mobile/change-plans-services/services" AND proxyURL="*/purchase-path-browse/v1/conflicts"  http_method="POST") result=* | stats count by session_id| table session_id] 
 | rex field=Referer "http?:\/\/(?<DOMAIN>[^/]+)" | WHERE responseStatusCode="200" and (DOMAIN LIKE "%preqat%" or DOMAIN LIKE "%qat%"or DOMAIN LIKE "%preprod%")
 |timechart span=1d count as "Change Rate Plan"]  
 append[ search sourcetype=*index* Referer="*/prepaid/cart/orderpayment?cartId=*" AND proxyURL="*/payment/v1/orders/payment-method" AND http_method="POST" result=* channel_id=* 
 search (Referer="*/prepaid/bring-your-own-device" AND proxyURL="*/utility/v1/general-info/valid-imei?imei=*" http_method="POST") result=* | stats count by session_id| table session_id] 
| rex field=Referer "http?:\/\/(?<DOMAIN>[^/]+)" | WHERE responseStatusCode="200" and (DOMAIN LIKE "%preqat%" or DOMAIN LIKE "%qat%"or DOMAIN LIKE "%preprod%")
 |timechart span=1d count as "Single Line BYOD"] | 
 append[ search sourcetype=*index* Referer="*/prepaid/cart/orderconfirm" AND proxyURL="*purchase-path-order/v1/orders/*" AND http_method="POST" result=* channel_id=* 
 search (Referer="*/prepaid/shop/device-details/*" AND proxyURL="*/purchase-path-browse/v1/product-details/*" http_method="POST") result=* | stats count by session_id| table session_id] 
 | rex field=Referer "http?:\/\/(?<DOMAIN>[^/]+)" | WHERE responseStatusCode="200" and (DOMAIN LIKE "%preqat%" or DOMAIN LIKE "%qat%"or DOMAIN LIKE "%preprod%")
 |timechart span=1d count as "Change/Upgrade Device"] | timechart span=1d values(*) as *
0 Karma