Reporting

Dealing with showing and counting events with fields of the type "current status"

cindygibbs_08
Communicator

Hello People, 

Thank you so much for the amazing help you have provided me with in my last post... I have one final struggle to tackle this month in splunk and it is with regards to "How to count events that retain the same ID or code of reference but can in time change values in a specefifiedl field" for this instances.. I work for a Hotel Company and a Booking reference f.i "YHDU-984" can have 4 differnet status values = BOOKED, PAID, TRAVELED, OK Whenever a custumer maked a reservacion the status is BOOKED, and they pay it out is PAID, whenever they traveled to the destination is TRAVELED and when they arrieved at our hotel is changed to OK... so a Booking reference may have all of these status values or only  some of them... each new change in status will be recorded with the DATE_TIME and every record will also show the DESTINATION (city) and HOTEL_NAME.

It will be a lot more usefull to me that instead of counting by BOOKING_REF how manny events there are in the STATUS="OK" and so on.... ... I wanna be able to  count the number of BOOKING_REF in each STATUS taking into account the very last STATUS each BOOKING_REF has currently, I'm sorry Im not the best with words so here is an example:

lest say I can obtain this tables:

BOOKING_REFCLIENTSTATUS
HYH89ADAMBOOKED
HD983BOBBOOKED
XUUE8CHARLESBOOKED
XKSIU8JAMESBOOKED
XPPP4DINABOOKED
YHUO1TINABOOKED

 

and when I look for STATUS PAID i get this

BOOKING_REFCLIENTSTATUS
HYH89ADAMPAID
HD983BOBPAID
XUUE8CHARLESPAID
XKSIU8JAMESPAID

 

and when I look for STATUS TRAVELED i get this

BOOKING_REFCLIENTSTATUS
HYH89ADAMTRAVELED
HD983BOBTRAVELED

 

and when I look for STATUS OK I get this

 

BOOKING_REFCLIENTSTATUS
HD983BOBOK

 

if I use the stats command to count each status I get something like this:


STATUScount
BOOKED6
PAID4
TRAVELED2
OK1

 

but for my Boss (which is not a friendly person... it is confusing to interpret) so If I was able to count by the very last event or in other words the "current" status my result should look something like this:

STATUScount
BOOKED2
PAID2
TRAVELED1
OK1

 

and this is because out of the fist Bookings I can now identify which custumer have traveled and yet not arrieved at out hotel..and I can also see that Only one custumer has made it in the hotel

thank you so much guys for your help..this will be the last time I will be bothering you with my posts this month I promise! Im sending you a lot of love

 

Kindly,

Cindy

Labels (1)
0 Karma
1 Solution

ITWhisperer
Legend
| stats latest(STATUS) as STATUS by BOOKING_REF
| stats count by STATUS

View solution in original post

ITWhisperer
Legend
| stats latest(STATUS) as STATUS by BOOKING_REF
| stats count by STATUS

View solution in original post

cindygibbs_08
Communicator

10/10 Thank you so much!

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!