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
Engager

Say I have the following 4 logs:
alt text

And I want to create the final output table as:
alt text

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 
Tags (5)
0 Karma

sundareshr
Legend

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
0 Karma

mrgibbon
Contributor

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.

0 Karma
Get Updates on the Splunk Community!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...