Existing Query returning no data

[kmccowen]

Hello everyone,
We have a dashboard that contains a few panels that recently stopped returning data. I've tried to figure out what has changed but no luck so far. I was not the one who originally created the dashboard or the queries but used so that makes it more difficult. Was hoping I could provide the query along with a transaction to compare to and try to get some assistance. Let me know if I need to provide any other info.

Query:
index=gateway host=sc58lgwap0* (Text="START GTWY_ORDERING: BEGINNING NEW ORDERING SESSION" OR Text="START ORDERING.CLOSE_MODAL: ON CLOSE CALLED" OR Text="START ORDERING.NEW_CONNECT: NAVIGATED TO NEW CONNECT" OR Text="START ORDERING.OFFER_SELECTION: NAVIGATED TO OFFER SELECTION" OR Text="START ORDERING.OFFER_CUSTOMIZATION: LOADING OFFER CUSTOMIZATION" OR Text="START ORDERING.CUSTOMER_VERIFICATION: NAVIGATED TO CUSTOMER VERIFICATION" OR Text="START ORDERING.SCHEDULE_INSTALL: NAVIGATED TO SCHEDULE INSTALL" OR Text="START ORDERING.ORDER_SUMMARY: NAVIGATED TO ORDER SUMMARY" OR Text="START ORDERING.ORDER_CONFIRMATION: NAVIGATED TO ORDER CONFIRMATION" OR Text="START ORDERING.CLOSE_CLICK: ORDER CONFIRMATION CLOSE CLICK") | EVAL eventorder = case(Text == "START GTWY_ORDERING: BEGINNING NEW ORDERING SESSION",1, Text == "START ORDERING.NEW_CONNECT: NAVIGATED TO NEW CONNECT",2, Text == "START ORDERING.OFFER_SELECTION: NAVIGATED TO OFFER SELECTION",3, Text == "START ORDERING.OFFER_CUSTOMIZATION: LOADING OFFER CUSTOMIZATION",4, Text == "START ORDERING.CUSTOMER_VERIFICATION: NAVIGATED TO CUSTOMER VERIFICATION",5, Text == "START ORDERING.SCHEDULE_INSTALL: NAVIGATED TO SCHEDULE INSTALL",6, Text == "START ORDERING.ORDER_SUMMARY: NAVIGATED TO ORDER SUMMARY",7, Text == "START ORDERING.ORDER_CONFIRMATION: NAVIGATED TO ORDER CONFIRMATION",8, Text == "START ORDERING.CLOSE_CLICK: ORDER CONFIRMATION CLOSE CLICK",9, Text == "START ORDERING.CLOSE_MODAL: ON CLOSE CALLED",100) | EVAL source = case(Text == "START GTWY_ORDERING: BEGINNING NEW ORDERING SESSION","Order Start", Text == "START ORDERING.NEW_CONNECT: NAVIGATED TO NEW CONNECT","Customer Information",Text == "START ORDERING.OFFER_SELECTION: NAVIGATED TO OFFER SELECTION","Offer Selection", Text == "START ORDERING.OFFER_CUSTOMIZATION: LOADING OFFER CUSTOMIZATION","Offer Customization", Text == "START ORDERING.CUSTOMER_VERIFICATION: NAVIGATED TO CUSTOMER VERIFICATION","Customer Verification", Text == "START ORDERING.SCHEDULE_INSTALL: NAVIGATED TO SCHEDULE INSTALL","Schedule Install", Text == "START ORDERING.ORDER_SUMMARY: NAVIGATED TO ORDER SUMMARY","Order Summary", Text == "START ORDERING.ORDER_CONFIRMATION: NAVIGATED TO ORDER CONFIRMATION","Order Confirmation", Text == "START ORDERING.CLOSE_CLICK: ORDER CONFIRMATION CLOSE CLICK","Order Complete", Text == "START ORDERING.CLOSE_MODAL: ON CLOSE CALLED","Order Canceled") | streamstats current=f last(source) as nextevent1 by TxRoot | streamstats current=f last(eventorder) as nexteventorder1 by TxRoot | EVAL nextevent2=coalesce(nextevent1,"NA") | EVAL nexteventorder=coalesce(nexteventorder1,101) | where eventorder < nexteventorder | EVAL target = case(nextevent2 == "NA" and eventorder <= 4 ,"Abandoned Pre Biller Creation", nextevent2 == "NA" and eventorder != 100 and eventorder != 9,"Abandoned Post Biller Creation", 1 == 1, nextevent2) | where target != "NA" | timechart span=10m count(eval(target="Abandoned Pre Biller Creation" AND source="Customer Information")) as "Abandoned Pre Biller" count(eval(target="Offer Selection" AND source="Customer Information")) as "Offer Selection" count(eval(target="Offer Customization" AND source="Customer Information")) as "Offer Customization" count(eval(target="Customer Verification" AND source="Customer Information")) as "Customer Verification" count(eval(target="Schedule Install" AND source="Customer Information")) as "Schedule Install" count(eval(target="Order Summary" AND source="Customer Information")) as "Order Summary" count(eval(target="Order Confirmation" AND source="Customer Information")) as "Order Confirmation" count(eval(target="Order Complete" AND source="Customer Information")) as "Order Complete" count(eval(target="Abandoned Post Biller Creation" AND source="Customer Information")) as "Abandoned Post Biller Creation" count(eval(target="Order Canceled" AND source="Customer Information")) as "Order Cancelled"

Example of An Order which is what is being evaluated (Text Field and Time Stamp):
_time Text
2017-05-09T15:24:03.684-0500 START ORDERING.CLOSE_CLICK: ORDER CONFIRMATION CLOSE CLICK
2017-05-09T15:23:58.782-0500 START ORDERING.ORDER_CONFIRMATION: NAVIGATED TO ORDER CONFIRMATION
2017-05-09T15:23:58.782-0500 ORDER CONFIRMATION SUCCESS
2017-05-09T15:23:58.782-0500 START OrderingModel.postUpdateOrderConfirmation: CONFIRM ORDER
2017-05-09T15:23:58.782-0500 UPDATE SCHEDULE SUCCESS
2017-05-09T15:23:58.782-0500 UPDATE SCHEDULE COMPLETE
2017-05-09T15:23:57.741-0500 UPDATE EMAIL SUCCESS
2017-05-09T15:23:57.741-0500 CUSTOMER PII SUCCESS
2017-05-09T15:23:56.735-0500 START OrderingModel.putCustomerPiiInfo: CUSTOMER PII INFO
2017-05-09T15:23:56.735-0500 START OrderingModel.setSchedule: SETTING SCHEDULE
2017-05-09T15:23:56.735-0500 START OrderingModel.setSchedule: UPDATE SCHEDULE
2017-05-09T15:23:56.735-0500 START OrderingModel.postCustomerEmail: UPDATE EMAIL
2017-05-09T15:23:56.735-0500 START ORDERING.SUBMIT_CLICK: SUBMIT ORDER BUTTON CLICK
2017-05-09T15:23:55.599-0500 START DISCLAIMERS CHECKBOX CLICK: DISCLAIMERS CHECKBOX CLICK
2017-05-09T15:23:55.598-0500 START INSTALLATION INFORMATION CHECKBOX CLICK: INSTALLATION INFORMATION CHECKBOX CLICK
2017-05-09T15:23:54.559-0500 GET STATEMENTS SUCCESSFUL
2017-05-09T15:23:54.559-0500 START BILLING SUMMARY CHECKBOX CLICK: BILLING SUMMARY CHECKBOX CLICK
2017-05-09T15:23:53.146-0500 START Billing.StatementModel.getStatements: GET STATEMENTS
2017-05-09T15:23:53.146-0500 START CHECK_FOR_STORE_INFO: CHECK FOR STORE INFO
2017-05-09T15:23:53.146-0500 START ORDERING.ORDER_SUMMARY: NAVIGATED TO ORDER SUMMARY
2017-05-09T15:23:51.110-0500 START UI:PREFCOMM:PREFERENCES_LOAD:
2017-05-09T15:23:51.110-0500 Contact preferences created successfully
2017-05-09T15:23:51.110-0500 Contact preferences created successfully
2017-05-09T15:23:50.113-0500 START UI:PREFCOMM:CONTACT_PT_ADD_PREFERENCES: - 112009179
2017-05-09T15:23:50.113-0500 START UI:PREFCOMM:CONTACT_PT_ADD_PREFERENCES: - 112009181
2017-05-09T15:23:50.113-0500 START UI:PREFCOMM:ACCOUNT_SAVE: - 835*
2017-05-09T15:23:47.198-0500 START UI:PREFCOMM:PREFERENCES_LOAD:
2017-05-09T15:23:47.198-0500 START UI:PREFCOMM:CONTACT_PT_LOAD:
2017-05-09T15:23:47.198-0500 START UI:PREFCOMM:EVENT_FAMILY_LOAD:
2017-05-09T15:23:47.198-0500 START UI:PREFCOMM:CONTACT_PT_LOAD:
2017-05-09T15:23:46.199-0500 START UI:PREFCOMM:COMM_CONTACT_ADD:
2017-05-09T15:23:45.203-0500 START UI:PREFCOMM:COMM_CONTACT_LOAD:
2017-05-09T15:23:45.203-0500 START UI:PREFCOMM:CONTACT_ALL_LOAD:
2017-05-09T15:23:45.203-0500 START ORDERING.CONTACT_PREFERENCES: NAVIGATED TO CONTACT PREFERENCES
2017-05-09T15:23:45.203-0500 UPDATE SCHEDULE COMPLETE
2017-05-09T15:23:42.689-0500 START OrderingModel.setSchedule: SETTING SCHEDULE
2017-05-09T15:23:42.689-0500 START ORDERING.NEXT_CLICK: SCHEDULING NEXT BUTTON CLICK
2017-05-09T15:22:59.355-0500 GET SELECTED WINDOW COMPLETE
2017-05-09T15:22:56.309-0500 START OrderingModel.getSelectedWindow: GET SELECTED WINDOW
2017-05-09T15:22:56.309-0500 NEW SCHEDULE WINDOWS COMPLETE
2017-05-09T15:22:52.246-0500 START OrderingModel.getSchedules: REQUEST NEW SCHEDULE WINDOWS CALLED
2017-05-09T15:22:52.246-0500 START ORDERING.SCHEDULE_INSTALL: NAVIGATED TO SCHEDULE INSTALL
2017-05-09T15:22:52.246-0500 TPV PIN ORDER REQUEST PROCESSED
2017-05-09T15:22:50.967-0500 START OrderingModel.initiateTPVPin: TPV PIN REQUEST INITIATED
2017-05-09T15:22:50.967-0500 SUBMIT ORDER SENT TO BILLER
2017-05-09T15:22:40.275-0500 START OrderingModel.submitBillerOrder: SUBMIT ORDER
2017-05-09T15:22:40.275-0500 VALIDATE ORDER SUCCESS
2017-05-09T15:22:39.004-0500 START OrderingModel.validateOrder: VALIDATE ORDER
2017-05-09T15:22:39.004-0500 START ORDERING.NEXT_CLICK: OFFER CUSTOMIZATION MISCELLANEOUS NEXT BUTTON CLICK
2017-05-09T15:22:37.774-0500 START ORDERING.GET_RTCSR_CONFIG: GET RTCSR CONFIG
2017-05-09T15:22:37.774-0500 VALIDATE ORDER SUCCESS
2017-05-09T15:22:36.771-0500 START OrderingModel.validateOrder: VALIDATE ORDER
2017-05-09T15:22:36.771-0500 START OrderingModel.validateMiscProduct: VALIDATE MISCELLANEOUS PRODUCT ON MISC LOAD
2017-05-09T15:22:36.771-0500 START ORDERING.OFFER_CUSTOMIZATION_MISC: NAVIGATED TO OFFER CUSTOMIZATION MISCELLANEOUS
2017-05-09T15:22:36.771-0500 START ORDERING.NEXT_CLICK: CUSTOMER VERIFICATION NEXT BUTTON CLICK
2017-05-09T15:22:35.094-0500 CREDIT CHECK COMPLETE (CUSTOMER FOUND)
2017-05-09T15:22:34.087-0500 START OrderingModel.verifyCustomer: CREDIT CHECK
2017-05-09T15:22:34.087-0500 START ORDERING.VERIFY_CLICK: VERIFY CUSTOMER CLICKED
2017-05-09T15:22:07.413-0500 START ORDERING.CUSTOMER_VERIFICATION: NAVIGATED TO CUSTOMER VERIFICATION
2017-05-09T15:22:07.413-0500 GET SELF INSTALL ELIGIBILITY SUCCESS
2017-05-09T15:22:06.394-0500 START OrderingModel.checkSelfInstallEligibility: GET SELF INSTALL ELIGIBILITY
2017-05-09T15:22:06.393-0500 VALIDATE ORDER SUCCESS
2017-05-09T15:22:04.803-0500 START OrderingModel.validateOrder: VALIDATE ORDER
2017-05-09T15:22:04.803-0500 START ORDERING.NEXT_CLICK: OFFER CUSTOMIZATION LOB NEXT BUTTON CLICK
2017-05-09T15:22:03.408-0500 START ORDERING.DONE_CLICK: HOST TN POPUP DONE BUTTON CLICK
2017-05-09T15:21:39.825-0500 GET TELEPHONE NUMBER INVENTORY SUCCESS
2017-05-09T15:21:39.825-0500 GET AREA CODE AND EXCHANGES SUCCESS
2017-05-09T15:21:38.810-0500 START OrderingModel.getAvailableExchanges: GET AREA CODE AND EXCHANGES
2017-05-09T15:21:38.810-0500 START OrderingModel.getAvailableTNListing: GET TELEPHONE NUMBER INVENTORY
2017-05-09T15:21:38.810-0500 START ORDERING.HOST_PHONE_NUMBER_MODEL: HOST BUTTON CLICK
2017-05-09T15:21:36.349-0500 START ORDERING.GET_RTCSR_CONFIG: GET RTCSR CONFIG
2017-05-09T15:21:36.349-0500 START ORDERING.VOICE_LOB_WINDOW_OPEN: AttachmentPurpose: +LB, URL: https://chtrgwy.corp.chartercom.com:443/static/mec_attach/POP_UP_VOICE_MAIN.html - BATTERY BACKUP WINDOW DISPLAYED
2017-05-09T15:21:13.196-0500 GET QUOTE SUCCESSFUL
2017-05-09T15:21:11.695-0500 ORDER REASONS SUCCESS
2017-05-09T15:21:11.695-0500 START OrderingModel.getQuote: GET QUOTE
2017-05-09T15:21:11.695-0500 START ORDERING.OFFER_CUSTOMIZATION: LOADING OFFER CUSTOMIZATION
2017-05-09T15:21:11.695-0500 START OrderingModel.getOrderReasons: ORDER REASONS
2017-05-09T15:21:11.695-0500 START ORDERING.OFFER_CUSTOMIZATION: NAVIGATED TO OFFER CUSTOMIZATION
2017-05-09T15:21:11.695-0500 START ORDERING.NEXT_CLICK: OFFER SELECTION NEXT BUTTON CLICK
2017-05-09T15:21:06.390-0500 START ORDERING.OFFER_SELECTED: OFFERS SELECTED
2017-05-09T15:21:02.910-0500 OFFERS RECEIVED
2017-05-09T15:21:02.910-0500 START Ordering.Model.getOffers: GET OFFERS
2017-05-09T15:21:02.909-0500 FILTER FACETS RECEIVED
2017-05-09T15:21:01.883-0500 START ORDERING.Ordering.Model.getFilterFacets: GET FILTER FACETS
2017-05-09T15:21:01.883-0500 START ORDERING.OFFER_SELECTION: NAVIGATED TO OFFER SELECTION
2017-05-09T15:21:01.883-0500 VERIFY ALL TELEPHONE NUMBERS SUCCESSFUL
2017-05-09T15:21:01.883-0500 TELEPHONE NUMBER VALIDATED: (706) 218-7393
2017-05-09T15:21:01.883-0500 TELEPHONE NUMBER VALIDATED: (706) 847-9832
2017-05-09T15:21:01.883-0500 START ORDERING.OrderingModel.verifyTN: Verifying All Telephone Numbers
2017-05-09T15:21:01.883-0500 START OrderingModel.verifyTN: Verifying Telephone Number: (706) 847-9832
2017-05-09T15:21:01.883-0500 START OrderingModel.verifyTN: Verifying Telephone Number: (706) 218-7393
2017-05-09T15:21:01.883-0500 START ORDERING.NEXT_CLICK: CUSTOMER INFORMATION NEXT BUTTON CLICK
2017-05-09T15:18:14.805-0500 LOCATION SERVICEABLE
2017-05-09T15:18:14.805-0500 SERVICEABILITY PROCESSED
2017-05-09T15:18:14.805-0500 SERVICEABILITY PROCESSED
2017-05-09T15:18:14.805-0500 SERVICEABILITY PROCESSED
2017-05-09T15:18:13.785-0500 START GET_SERVICEABILITIES: GETTING SERVICEABILITY
2017-05-09T15:18:13.785-0500 START GET_SERVICEABILITIES: GETTING SERVICEABILITY
2017-05-09T15:18:13.785-0500 START GET_SERVICEABILITIES: GETTING SERVICEABILITY
2017-05-09T15:18:13.785-0500 START ORDERING.OrderingModel.getServiceability: LOADING SERVICEABILITY
2017-05-09T15:18:13.785-0500 START GTWY_ORDERING: BEGINNING NEW ORDERING SESSION
2017-05-09T15:18:13.785-0500 START ORDERING.NEW_CONNECT: NAVIGATED TO NEW CONNECT

[kmccowen]

Still no luck 😞 Any more suggestions?

[DalJeanis]

Start by fixing the case of the words and and EVAL everywhere. AND should always be in caps, eval should be in lower case. The latter is probably not the problem, but the former often will be. I would bet that fixing the lower case ands in the line near the end -- | EVAL target= blah blah -- will resolve your immediate problem.

After that, here are some suggestions to clean up the code and make it less convoluted.

---

The whole thing can be made to read much more simply if you move all the literals and values for Text, eventorder and source into a csv file.

That way, everything before the streamstats command becomes...

index=gateway host=sc58lgwap0* [| inputcsv mystatuslist.csv | table Text]
| table _time Text TXRoot
| join Text [| inputcsv mystatuslist.csv | table Text eventorder source]
| table _time Text TXRoot eventorder source

... which I think you'd agree is less confusing, yes?

---

The part of the logic that I seriously wonder about is that this timechart only appears to be charting records where the source is "Customer Information", but it seems to be processing much more than that. I'm not sure whether anything can be done in the middle of the search, since I don't know your data or your sales funnel.

The last couple of lines can probably be simplified to...

| where target != "NA"  AND  source="Customer Information"
| timechart span=10m count by target

...and if not, at the very least to ...

| where target != "NA"  AND  source="Customer Information"
| timechart span=10m  count(eval(target="Abandoned Pre Biller Creation")) as "Abandoned Pre Biller" 
count(eval(target="Offer Selection")) as "Offer Selection"  count(eval(target="Offer Customization")) as "Offer Customization" .....  more lines for all the others  ...  count(eval(target="Order Canceled")) as "Order Cancelled"

... if the order is critical, then you can prepend a number to the "target" literal to force the order.

[kmccowen]

I've corrected the "and" and "eval" but they query is returning the save results which is all values of zeros.

time
Abandoned Pre Biller
Offer Selection
Offer Customization
Customer Verification
Schedule Install
Order Summary
Order Confirmation
Order Complete
Abandoned Post Biller Creation
Order Cancelled

2017-05-03 07:00:00 0 0 0 0 0 0 0 0 0 0
2017-05-03 07:10:00 0 0 0 0 0 0 0 0 0 0

[kmccowen]

Here is the fixed query with corrected "and" and "eval"

index=gateway host=sc58lgwap0* (Text="START GTWY_ORDERING: BEGINNING NEW ORDERING SESSION" OR Text="START ORDERING.CLOSE_MODAL: ON CLOSE CALLED" OR Text="START ORDERING.NEW_CONNECT: NAVIGATED TO NEW CONNECT" OR Text="START ORDERING.OFFER_SELECTION: NAVIGATED TO OFFER SELECTION" OR Text="START ORDERING.OFFER_CUSTOMIZATION: LOADING OFFER CUSTOMIZATION" OR Text="START ORDERING.CUSTOMER_VERIFICATION: NAVIGATED TO CUSTOMER VERIFICATION" OR Text="START ORDERING.SCHEDULE_INSTALL: NAVIGATED TO SCHEDULE INSTALL" OR Text="START ORDERING.ORDER_SUMMARY: NAVIGATED TO ORDER SUMMARY" OR Text="START ORDERING.ORDER_CONFIRMATION: NAVIGATED TO ORDER CONFIRMATION" OR Text="START ORDERING.CLOSE_CLICK: ORDER CONFIRMATION CLOSE CLICK") | eval eventorder = case(Text == "START GTWY_ORDERING: BEGINNING NEW ORDERING SESSION",1, Text == "START ORDERING.NEW_CONNECT: NAVIGATED TO NEW CONNECT",2, Text == "START ORDERING.OFFER_SELECTION: NAVIGATED TO OFFER SELECTION",3, Text == "START ORDERING.OFFER_CUSTOMIZATION: LOADING OFFER CUSTOMIZATION",4, Text == "START ORDERING.CUSTOMER_VERIFICATION: NAVIGATED TO CUSTOMER VERIFICATION",5, Text == "START ORDERING.SCHEDULE_INSTALL: NAVIGATED TO SCHEDULE INSTALL",6, Text == "START ORDERING.ORDER_SUMMARY: NAVIGATED TO ORDER SUMMARY",7, Text == "START ORDERING.ORDER_CONFIRMATION: NAVIGATED TO ORDER CONFIRMATION",8, Text == "START ORDERING.CLOSE_CLICK: ORDER CONFIRMATION CLOSE CLICK",9, Text == "START ORDERING.CLOSE_MODAL: ON CLOSE CALLED",100) | eval source = case(Text == "START GTWY_ORDERING: BEGINNING NEW ORDERING SESSION","Order Start", Text == "START ORDERING.NEW_CONNECT: NAVIGATED TO NEW CONNECT","Customer Information",Text == "START ORDERING.OFFER_SELECTION: NAVIGATED TO OFFER SELECTION","Offer Selection", Text == "START ORDERING.OFFER_CUSTOMIZATION: LOADING OFFER CUSTOMIZATION","Offer Customization", Text == "START ORDERING.CUSTOMER_VERIFICATION: NAVIGATED TO CUSTOMER VERIFICATION","Customer Verification", Text == "START ORDERING.SCHEDULE_INSTALL: NAVIGATED TO SCHEDULE INSTALL","Schedule Install", Text == "START ORDERING.ORDER_SUMMARY: NAVIGATED TO ORDER SUMMARY","Order Summary", Text == "START ORDERING.ORDER_CONFIRMATION: NAVIGATED TO ORDER CONFIRMATION","Order Confirmation", Text == "START ORDERING.CLOSE_CLICK: ORDER CONFIRMATION CLOSE CLICK","Order Complete", Text == "START ORDERING.CLOSE_MODAL: ON CLOSE CALLED","Order Canceled") | streamstats current=f last(source) as nextevent1 by TxRoot | streamstats current=f last(eventorder) as nexteventorder1 by TxRoot | eval nextevent2=coalesce(nextevent1,"NA") | eval nexteventorder=coalesce(nexteventorder1,101) | where eventorder < nexteventorder | eval target = case(nextevent2 == "NA" AND eventorder <= 4 ,"Abandoned Pre Biller Creation", nextevent2 == "NA" AND eventorder != 100 AND eventorder != 9,"Abandoned Post Biller Creation", 1 == 1, nextevent2) | where target != "NA" | timechart span=10m count(eval(target="Abandoned Pre Biller Creation" AND source="Customer Information")) as "Abandoned Pre Biller" count(eval(target="Offer Selection" AND source="Customer Information")) as "Offer Selection" count(eval(target="Offer Customization" AND source="Customer Information")) as "Offer Customization" count(eval(target="Customer Verification" AND source="Customer Information")) as "Customer Verification" count(eval(target="Schedule Install" AND source="Customer Information")) as "Schedule Install" count(eval(target="Order Summary" AND source="Customer Information")) as "Order Summary" count(eval(target="Order Confirmation" AND source="Customer Information")) as "Order Confirmation" count(eval(target="Order Complete" AND source="Customer Information")) as "Order Complete" count(eval(target="Abandoned Post Biller Creation" AND source="Customer Information")) as "Abandoned Post Biller Creation" count(eval(target="Order Canceled" AND source="Customer Information")) as "Order Cancelled"

[kmccowen]

The order is critical like you said. I'll try using new ending you provided and see if that changes the results. Thanks for the help!