topic Re: How to modify my search to create a table showing the distinct count of user sign-in and sign-ups by product? in Splunk Search

How to modify my search to create a table showing the distinct count of user sign-in and sign-ups by product?

saimaday2 — Thu, 29 Sep 2016 05:30:10 GMT

Say I have the following 4 logs:

And I want to create the final output table as:

I want to count the distinct number of users that successfully and unsuccessfully signed-up and signed-in by product for a particular day. Additionally I want to exclude those sign-in results where api=10 or api=20.

I was thinking something along the lines of the following but it is giving me null results:

index=abc*
| bucket _time span=1d
| eval signup=if(search="sign-up","1","0")
| eval signin=if(search="sign-in","1","0")
| eval message=
case(signup=1 AND errorcode="success" ,"Successful sign-UP", sign-up=1 AND errorcode="fail"  ,"Failed sign-UP", 
signin=1 AND errorcode="success" ,"Successful sign-IN", sign-in=1 AND errorcode="user not found"  ,"User not found sign-IN")
| chart dc(user) over product by message
| table product Successful sign-UP, Failed sign-UP, Successful sign-IN, Failed sign-IN

Re: How to modify my search to create a table showing the distinct count of user sign-in and sign-ups by product?

mrgibbon — Fri, 30 Sep 2016 06:02:39 GMT

Im just working through the logic now, without having sample data to test on I might not get answer for you, but...
This:
table product Successful sign-UP, Failed sign-UP, Successful sign-IN, Failed sign-IN
Needs to be this for starters:
table product "Successful sign-UP", "Failed sign-UP", "Successful sign-IN", "Failed sign-IN"

I'll let you know if I crack the rest.

Re: How to modify my search to create a table showing the distinct count of user sign-in and sign-ups by product?

sundareshr — Fri, 07 Oct 2016 22:59:12 GMT

Try this

index=abc* 
| eval message=
    case(match(_raw, "sign-up") AND errorcode="success", "Successful sign-UP", match(_raw, "sign-up") AND errorcode="fail" ,"Failed sign-UP", 
    match(_raw, "sign-in") AND errorcode="success", "Successful sign-IN", match(_raw, "sign-in") AND errorcode="user not found", "User not found sign-IN") 
| chart dc(user) over product by message