How to pass an attribute from one event to another in order to create a table to summarize data

[cindygibbs_08]

Hi guys!

I'm a newbie to Splunk and I would appreciate if you could help me out on this one (Thank you to all the members of the community in advance)

A payment transaction has to be monitored in order to determine the clients that are able to successfully made a payment of a purchase in a web page. This payment process has 3 easy stages (A, B and C), and each stage will return a "clientcheck_code" and an "id payment" for each client.  When I visualize the data it looks like this:

Stage A

client	clientcheck_code	id_payment
007	S_50	USJkn
008	S_50	t6yudd
008	S_50	68sgh
006	S_50	8lpifd

Stage B

client	clientcheck_code	id_payment
007	S_50	89Jkn
008	S_50	896gyudd
008	S_59	00smoh
006	S_50	eybry

Stage C

client	clientcheck_code	id_payment
007	S_50	ijfcvgh
008	S_50	t6yuhf
006	S_50	okyrgdf

If any client gets the check_code S_59 at any stage the transaction is immediacy called by the back, thus if a client gets this code in stage two then they would not have an record for the next stage (3) as the platform will log them out.

But our main goal is to be able to visualize ALL possible combinations of check_code for each  attempt made by each client as we believe that our web server is kind of faulty at the moment.  We want to be able to see something like this:

client	A_clientcheck_code	B_clientcheck_code	C_clientcheck_code	FAILED?
007	S_50	S_50	S_50	NO
008	S_50	S_50	S_50	NO
008	S_50	S_59	NULL	YES
006	S_50	S_50	S_50	NO

this table allows us to see each attempt by each client and what code they received in every stage, notice that the client 008 did not have a code for stage C because this client got a code S_59 from the previous stage So we also want to be able to do that.

An idea to achieve the above table is to use the stats command and then list the events by client but when we use the list() command sometimes we get very weird outcome from splunk and unfortunately the filed "id_payment" is not the same through a transaction and so it can not be use as a "common identifier" to later on uses the stats command, So we thought of this solution: What if we could overwrite the field  id_payment for stages B and C with the same id_payment of the stage A, that way we could have the same id_payment for every stage and use the stats command to count by id_payment which now will be a  "common identifier" for every attempt a client makes, to illustrate the table should look like this:

client	A_clientcheck_code	B_clientcheck_code	C_clientcheck_code	FAILED?	id_payment
007	S_50	S_50	S_50	NO	USJkn
008	S_50	S_50	S_50	NO	t6yudd
008	S_50	S_59	NULL	YES	68sgh
006	S_50	S_50	S_50	NO	8lpifd

This way every record is now stored by a unique id_payment that is a common identifier to a single transaction. I will be so thankful if you guys can help me out with that! or give me some sort of documentation to go around it. Thank you so much for your time and kindness

Queries:

Stage A
index="aws_pay_001_loop_page"
|search tx_loan_where="A" | fields client id_payment clientcheck_code

Stage B
index="aws_pay_001_loop_page"
|search tx_loan_where="B" | fields client id_payment clientcheck_code

Stage C
index="aws_pay_001_loop_page"
|search tx_loan_where="C" | fields client id_payment clientcheck_code

kindly,

Cindy

[ITWhisperer · Accepted solution]

Run-anywhere example:

| makeresults 
| eval _raw="time,client,clientcheck_code,id_payment,tx_loan_where
1,7,S_50,USJkn,A
2,7,S_50,89Jkn,B
3,7,S_50,ijfcvgh,C
4,8,S_50,t6yudd,A
5,8,S_50,896gyudd,B
6,8,S_50,t6yuhf,C
7,8,S_50,68sgh,A
8,8,S_59,00smoh,B
10,6,S_50,8lpifd,A
11,6,S_50,eybry,B
12,6,S_50,okyrgdf,C"
| multikv forceheader=1
| eval _time=_time+time
| fields - _raw linecount


| eval id_payment=if(tx_loan_where="A",id_payment,null)
| filldown id_payment
| eval A_clientcheck_code=if(tx_loan_where="A",clientcheck_code,null)
| eval B_clientcheck_code=if(tx_loan_where="B",clientcheck_code,null)
| eval C_clientcheck_code=if(tx_loan_where="C",clientcheck_code,null)
| stats values(*_clientcheck_code) as *_clientcheck_code by client id_payment
| eval failed=if(C_clientcheck_code="S_50","NO","YES")

Assuming you have timestamps for the events

index="aws_pay_001_loop_page" (tx_loan_where="A" OR tx_loan_where="B" OR tx_loan_where="C")
| fields _time client id_payment clientcheck_code tx_loan_where
| sort 0 client _time
| eval id_payment=if(tx_loan_where="A",id_payment,null)
| filldown id_payment
| eval A_clientcheck_code=if(tx_loan_where="A",clientcheck_code,null)
| eval B_clientcheck_code=if(tx_loan_where="B",clientcheck_code,null)
| eval C_clientcheck_code=if(tx_loan_where="C",clientcheck_code,null)
| stats values(*_clientcheck_code) as *_clientcheck_code by client id_payment
| eval failed=if(C_clientcheck_code="S_50","NO","YES")

[cindygibbs_08]

This is exactly what I was looking forward to achieve. Words can not express my happiness @ITWhisperer  I feel like you are the highlight of this Week... And I hope you know that you have helped this woman mentally and emotionally. Thank you for your attention to detail and for making people aware of how to approach a solution using this software. You have been my rock this week and I'm sending you the biggest hug and kiss wherever you are.