How to create a table with individual results based on conditions from two search queries

[cindygibbs_08]

Hi guys,

This is a bit embarrassing but I am all hands tied trying to figuring out how to compute the following results:

I have to count how many times a client has successfully change his/her account "nickname"  in other to do so... the client has to go to transaction "change_id_page_nick" and received the approval tag "04X" and after that the client is taken to the transition "confirm_token_change" and get the approval tag "051"  any other scenario results in an unsuccessful attempt  to change the nickname and should be recorded nonetheless.

lets suppose that the clients in the first stage ("change_id_page_nick")  look like this:

ID_CLIENT	TAG
Gabby	04X
Gabby	08x
Alex	04x
Nicole	04X

and for the second stage ("confirm_token_change") it can look like this:

ID_CLIENT	TAG
Gabby	051
Gabby	04P
Alex	051

What I want to achieve is this table:

ID_CLIENT	TAG_1	TAG_2	SUCCESFULL?
Gabby	04X	051	YES
Gabby	08X	04P	NO
Alex	04x	051	YES
Nicole	04X	N.A	NO

this table allows me to see every attempt made by all the clients also it allows me to see which clients did not complete the process as we see that Nicole has a N.A in TAG_2 meaning that she did not proceed with the process. I come from the world of SQL so I thought about doing my table joins but splunk does not work like that and I will be so happy if you guys can show me how to each the table above.

The results from the first stage are obtained through the query

index="page_upload_info"
|search="change_id_page_nick"
|fields ID approval_tag

 The second stage's data

index="page_upload_info"
|search="confirm_token_change"
|fields ID approval_tag

I dont know if the best thing to do is doing a multisearch first or a join in order to get the table above

kindly,

Cindy

[ITWhisperer]

Your issue may be being able to associate the first stage with the second stage. Is there any sort of "transaction" identifier which is present in both stages that would allow you to "join" the two events? Just collecting the values from the two stages won't cut it, but lists might be enough, however, if there are any missing entries, it could get confusing.

index="page_upload_info" change_id_page_nick OR confirm_token_change
| eval TAG_1=if(match(_raw, "change_id_page_nick"),approval_tag, null)
| eval TAG_2=(match(_raw, "confirm_token_change"),approval_tag, null)
| eval SUCCESS=if(isnull(TAG_2),null,if(TAG_2="051","YES","NO"))
| stats list(TAG_1) as TAG_1 list(TAG_2) as TAG_2 list(SUCCESS) as SUCCESS by ID

[cindygibbs_08]

Hello thank you for reaching out to me and for the code you have provided I truly appreciate your time!. To answer your question: Yes my main issue is to associate the first tag with the second tag so that I can know what final combination a client got in order to tell if the nickname change was successful. to answer if there is a piece of information to identify each "transaction" yes there is, is a field name "chip_id_page08" this is a unique number that will be recorded  through  the two stages something like this:

chip_id_Page08	ID	TAG_1	TAG_2
0051-08	ALEX	X67	051
857-444	Lucy	X41	058

and even though the ID will always be recorded in the same manner, the field "chip_id_Page08" will have a unique number combination to identify each attempt so thank you so much for making me aware of that! 

You say I could do a Join then... and my heart started to beat faster but in the documentation I dont really see how to do it, plus Splunk does not allow me to "store" a table in a variable to later on do a join as I would do in Python, I will be So thankful and grateful if you have the time to show me a code that could work well adding the field "chip_id_Page08" I will be eager to hear from you again thanks a lot.

PD: As you correctly point out list() is also showing some "errors-- I believe from splunk" like a bunch of "internal code" I guess the machine is doing

Kindly,

Cindy

[ITWhisperer · Accepted solution]

Join in splunk is usually avoided where possible. The technique often used is the by clause on the stats command. So, in the example below, the first line gets all the events from the page_upload_info index which have either change_id_page_nick or confirm_token_change. The next two lines set new fields to the value of the approval_tag depending on the type of event. This should mean that each event should either have one field or the other set to the same as the approval_tag. The third eval sets the success field based on the value in the tag_2 field.

Now the magic happens - the stats command creates a list (multi-value field) of all the ids from the events with the same value in the chip_id_Page08 field; likewise for all the tag_1, tag_2 and success fields. In theory, if your data is as you expect, these list should have at most one entry.

This effectively "joins" the events from the different stages by their common identifier.

index="page_upload_info" change_id_page_nick OR confirm_token_change
| eval TAG_1=if(match(_raw, "change_id_page_nick"),approval_tag, null)
| eval TAG_2=(match(_raw, "confirm_token_change"),approval_tag, null)
| eval SUCCESS=if(isnull(TAG_2),null,if(TAG_2="051","YES","NO"))
| stats list(ID) as ID list(TAG_1) as TAG_1 list(TAG_2) as TAG_2 list(SUCCESS) as SUCCESS by chip_id_Page08

[cindygibbs_08]

@ITWhisperer    I am in tears almost I am speechless thank you for taking the time to help me and for teaching me along the way like.... I was thinking about quitting my job my new boss has now demanded that we use Splunk to do everything we used to do in SQL or JAVA or python and we haven't received proper training yet, he is already asking us to do many things... I couldn't find any online course for free other than Splunk fundamentals one but  that course alone won't train me enough to do all the things I can do in other softwares. Thank you so much   @ITWhisperer  I have one final question I promise, I have a friend that has to do something similar as I did but the event or logs that she has do not have the luxury of a common "identifier" however they do happen sequentially  in 3 stages, each stage has its own "payment id" if she could pass the "payment id" to the following stages she could now have a common identifier throught the 3 stages by client ... how would one go around doing that? is it better to ask that in another question?

@ITWhisperer  Thanks a Million from the very bottom of my heart , where can I send a congratulatory  letter to Splunk about you?

[sensitive-thug]

@cindygibbs_08 Thank you for acknowledging (and karma'ing) the help you received from @ITWhisperer - it's what the Splunk community is all about!

[ITWhisperer]

@cindygibbs_08 You are welcome. Quitting your job would be a bit extreme. You can always ask questions here, and usually someone in the community will have some sort of an answer. Once you get the hang of it, splunk can do a lot of things, although not everything, and it isn't always easy. As with most things, it is about using the right tool for the right job.

For your friends question, it is probably best to submit a new question to keep the subject separate, so others can find it more easily if they have a similar question, but, in a nut-shell, the answer may lie in transactions, or potentially streamstats and eventstats (hard to know without the details). There is often more than one way to crack a nut. The more detail your friend can provide the better. If the problem statement is too vague or over-simplified, it will only generate more questions, and inappropriate solutions.

As far as congratulatory messages, there is no need, but if you really want to, you could try messaging one of the community managers e.g. @bjennewein or @sensitive-thug or someone like that, they are usually interested in how the community is helping their customers.

[aasabatini]

Hi @cindygibbs_08 

I not sure about this comand 

search="change_id_page_nick"

Anyway try this:

(index="page_upload_info" change_id_page_nick OR confirm_token_change)
| eval TAG_ 1=if(match(_raw, "change_id_page_nick"),approval_tag, "")
| eval TAG_2=(match(_raw, "confirm_token_change"),approval_tag, "")
| stats values(TAG_1) as _TAG_1 values(TAG_2) as TAG_2 by ID