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
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")
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")
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.