Help getting a proper trend line on bar graph

[audunn]

I have a search (below) which presents a nice bar graph of user activity through an app flow. What i'm having issues with is getting a trendline across the bar-graph based off 40% of the total count for "2. Guest Logins" to run across the whole graph. I want to be able to show user drop off

index=main  (app_name="checkout" OR app_name="checkout-new") NOT msg.tag="access" (msg.url="/purchase/review?*" OR msg.url="*/purchase/api/user/guest*" OR msg.url="*pricing/aip/buyer" OR msg.url="*user/customers/paymentInstruments" OR msg.url="https://api.company.com/inventory/listing/v4/?action=lookup" OR msg.url=*orderservice.*cloud*) 
| fields msg.url, msg.status, msg.activityId, msg.bff_activity, msg.message, baseline, threshold 
| replace "/purchase/review?*" WITH "1. Landed on XO" IN msg.url 
| replace "*/purchase/api/user/guest*" WITH "2. Guest Logins" IN msg.url 
| replace "*pricing/aip/buyer" WITH "3. BuyerPays" IN msg.url 
| replace "*user/customers/paymentInstruments" WITH "4. Create Payment Instrument" IN msg.url 
| replace "https://api.company.com/inventory/listing/v4/?action=lookup" WITH "5. Inventory Lookup" IN msg.url 
| replace "*orderservice.*cloud*" WITH "6. Order Service" IN msg.url 
| rex field=msg.message "XH_VI=(?<XH_VI>.{32});" 
| rex field=msg.message "session_userGUID=(?<session_userGUID>.{32});" 
| rex field=msg.message "UserID=(?<UserSessionID>.{32});" 
| rename msg.url AS URL, msg.status AS Response_Code, msg.activityId AS Activity_Id, msg.app_activity AS APP_Activity_Id 
| eval Description=case(Response_Code == "200" OR Response_Code =="201" OR Response_Code =="204", "SUCCESS", Response_Code != "200", "ERROR") 
| rex field=msg.message "ProxyRequest:\s*(?<URL>.*)\s*{" 
| eval NewDescription = if(Description = "SUCCESS", "1.SUCCESS", if(Description = "ERROR", "2.ERROR","3.NULL")) 
| sort URL 
| chart count(URL) by URL, NewDescription

[niketn · Accepted solution]

@audunn try adding the following to your existing search which adds a new column "Guest Login Threshold" at 40% of "2. Guest Logins". Then you need to create a Chart Overlay with the field "Guest Login Threshold"

 <yourCurrentSearch>
| chart count(URL) by URL, NewDescription
| eval "Guest Login Threshold"='1.SUCCESS'+'2.ERROR'
| eval "Guest Login Threshold"=case(URL=="2. Guest Logins",round('Guest Login Threshold'*0.4,2))
| eventstats max("Guest Login Threshold") as "Guest Login Threshold"

PS: Your search needs several improvements by remove fields/extractions that you are not using. Also the purpose of msg.message is unclear when msg.url is already present. Do you have only one of them present at a time? Can they both have URL?

Following is the updated query

 index=main  (app_name="checkout" OR app_name="checkout-new") NOT msg.tag="access" (msg.url="/purchase/review?*" OR msg.url="*/purchase/api/user/guest*" OR msg.url="*pricing/aip/buyer" OR msg.url="*user/customers/paymentInstruments" OR msg.url="https://api.company.com/inventory/listing/v4/?action=lookup" OR msg.url=*orderservice.*cloud*) 
| fields msg.url, msg.status, msg.message 
| replace "/purchase/review?*" WITH "1. Landed on XO" IN msg.url 
| replace "*/purchase/api/user/guest*" WITH "2. Guest Logins" IN msg.url 
| replace "*pricing/aip/buyer" WITH "3. BuyerPays" IN msg.url 
| replace "*user/customers/paymentInstruments" WITH "4. Create Payment Instrument" IN msg.url 
| replace "https://api.company.com/inventory/listing/v4/?action=lookup" WITH "5. Inventory Lookup" IN msg.url 
| replace "*orderservice.*cloud*" WITH "6. Order Service" IN msg.url 
| rename msg.url AS URL, msg.status AS Response_Code 
| eval Description=case(Response_Code == "200" OR Response_Code =="201" OR Response_Code =="204", "SUCCESS", Response_Code != "200", "ERROR") 
| rex field=msg.message "ProxyRequest:\s*(?<URL>.*)\s*{" 
| eval Description = case(Description=="SUCCESS","1.SUCCESS",
                             Description=="ERROR","2.ERROR",
                             true(),"3.NULL")
| chart count(URL) by URL,Description
| eval "Guest Login Threshold"='1.SUCCESS'+'2.ERROR'
| eval "Guest Login Threshold"=case(URL=="2. Guest Logins",round('Guest Login Threshold'*0.4,2))
| eventstats max("Guest Login Threshold") as "Guest Login Threshold"

[Sukisen1981]

hi @audunn
Your requirement is a bit unclear, right now you have your first column as urls with the url values as rows and the count by the new description values from the second column onward. So if you pipe and addtotals after the chart you will get a total count by each row and column.
Now, comes the confusing part - getting a trendline across the bar-graph based off 40% of the total count for "2. Guest Logins"
What does this mean?