Reporting

clasify events based on a datetime condition

cindygibbs_08
Communicator

 

Hello my gorgeous people from this amazing community,

I've been trying to solve this problem that I have but I couldn't find a way...

I have to create a category (a new field named "PROMO") that will have only two values "YES" ,"NO" I work for a  hotel chain and I want to know if a customer in the hotel has called the company hotline at least one time within the last month prior to the reservation date. I have the reservation dates and ID by customers and I can also generate the calls on the hotline but I have no work around as to how to classify this new field 😞

To illustrate if this is the data for the HOTLINE

ID_CXDATE_CALL
BETHANY2020-04-15T09:05:49-04:00
ALEX2020-04-21T07:25:15-04:00

 

ID_CXDATE_BOOKINGBOOKING_REF
BETHANY2020-04-26T09:05:49-04:00TY873
ALEX2020-03-28T07:25:15-04:00UJU4


Then my expected results will be:

ID_CXBOOKING_REFPROMO
BETHANYTY873YES
ALEXUJU4NO

 

This is because Bethany in fact called on the line 9 days prior to her booking (TY873) but Alex's call was actually AFTER his booking thats why he gets classify as "NO"

I feel like my main challenge is how to properly write my code so that Splunk looks for dates (if there are any)  BEFORE the booking date so that splunk can decide if there is a 30 days length of difference between them... and also I have the challenge to write the code to classify them.. Since this data is unstructured (as the information comes from different  events and a cx may or many have not called on the line) I dont know what would be the proper way to   count for this information..


please dont judge... but I have been trying out this code:

 

 

|multisearch 
[| search index ="hotline"  | fields CX_ID CALL_DATE]
[| search index ="bookings" | fields CX_ID DATE_BOOKING CX_ID]
| stats values(CALL_DATE) as HOTLINE values(DATE_BOOKING ) as DATE_BOOKING by ID_CX DATE_BOOKING 
| eval PROMO=if(...)

 

 


so bacsically I have not been able to classify because when I ask splunk to subtract the times I get nothing... And I don't know how to code that it has to look for the dates prior to the date of bookings...

Thank you so much to all the people that can help me out on this one I am truly grateful for that... 

kindly,

Cindy

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Search both indexes, parse the date fields into the _time field, sort and copy down the latest call time, then compare.

index="hotline" OR index="bookings"
| eval _time=if(isnotnull(DATE_CALL),strptime(DATE_CALL,"%Y-%m-%dT%H:%M:%S%:z"),if(isnotnull(DATE_BOOKING),strptime(DATE_BOOKING,"%Y-%m-%dT%H:%M:%S%:z"),_time))
| sort ID_CX _time
| streamstats latest(DATE_CALL) as last_call by ID_CX
| eval promo=if(isnotnull(BOOKING_REF),if(_time-strptime(last_call,"%Y-%m-%dT%H:%M:%S%:z")<30*24*60*60,"YES","NO"),null)

View solution in original post

cindygibbs_08
Communicator

@ITWhisperer 1) Why you always gotta do everythig right? 2) Thanks A LOT my dear gentelman....  3) Why are we using  streamstats instead of stats.. this is the first time I heard of that command.. Thank you so so much this solved my issue! thanks a lot... 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

To answer question 3 - the way splunk processes the data can be thought of as a queue or stream of events being passed along a chain of processes; so, the sort (particularly by _time in this instance - strictly speaking, the sort need only be done by _time because the streamstats is grouping by id) gathers all the events together and, once it has determined the order, sends them down the chain (pipeline) to the next process. The streamstats processes the stream of events as they are passing through, so the latest function will return the latest value (for the current id) it has seen so far and add it to each event as it passes. This combination makes sure that when the booking event comes along, it will have the last call time, allowing you to determine whether the promo can be applied.

0 Karma

cindygibbs_08
Communicator

omg! Your explanation was everything I needed to undertand this challenge and the code you gave me, thank you so much @ITWhisperer  like trully... thanks I feel like I own you at least a thousand hugs! THANKS A LOTTT INDEED

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Search both indexes, parse the date fields into the _time field, sort and copy down the latest call time, then compare.

index="hotline" OR index="bookings"
| eval _time=if(isnotnull(DATE_CALL),strptime(DATE_CALL,"%Y-%m-%dT%H:%M:%S%:z"),if(isnotnull(DATE_BOOKING),strptime(DATE_BOOKING,"%Y-%m-%dT%H:%M:%S%:z"),_time))
| sort ID_CX _time
| streamstats latest(DATE_CALL) as last_call by ID_CX
| eval promo=if(isnotnull(BOOKING_REF),if(_time-strptime(last_call,"%Y-%m-%dT%H:%M:%S%:z")<30*24*60*60,"YES","NO"),null)
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...