Splunk Search

Distinct results with multiple fields & multiple queries?

mrhodes93
Explorer

Hi all, I've been struggling with a good query for this for a few days. Basically I'm trying to track users that drop off between pages in a guided web application.

I'm able to get the results for Page1 and for Page2 individually, but I don't know how to combine the two queries to get the desired result. I don't know if I need to work with join or distinct count.

alt text

Basically on page1 I can dedup the AccoutNum, UserID (I don't care if the same user comes through with the same account), but I do care if a different user does. Users B and F both came to page one with account 567, but only F proceeded.

I really want to learn so any guided help or explanation would be amazing. Please let me know if anything is unclear.

0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults 
| eval Page_1="A,123#A,123#A,810#B,567#C,834#D,395#E,928#F,567"
| eval Page_1=split(Page_1,"#")
| mvexpand Page_1
| rex field=Page_1 "(?<UserID>\w),(?<AccountNum>\d+)"
| table UserID AccountNum
| eval source="Page1"
| append [|makeresults
| eval Page_2="A,123#D,395#F,567"
| eval Page_2=split(Page_2,"#")
| mvexpand Page_2
| rex field=Page_2 "(?<UserID>\w),(?<AccountNum>\d+)"
| table UserID AccountNum
| eval source="Page2"]
| stats values(A*) as A* dc(source) as flag by UserID AccountNum
| where flag = 1
| table UserID AccountNum

What's your query?

View solution in original post

to4kawa
Ultra Champion
| makeresults 
| eval Page_1="A,123#A,123#A,810#B,567#C,834#D,395#E,928#F,567"
| eval Page_1=split(Page_1,"#")
| mvexpand Page_1
| rex field=Page_1 "(?<UserID>\w),(?<AccountNum>\d+)"
| table UserID AccountNum
| eval source="Page1"
| append [|makeresults
| eval Page_2="A,123#D,395#F,567"
| eval Page_2=split(Page_2,"#")
| mvexpand Page_2
| rex field=Page_2 "(?<UserID>\w),(?<AccountNum>\d+)"
| table UserID AccountNum
| eval source="Page2"]
| stats values(A*) as A* dc(source) as flag by UserID AccountNum
| where flag = 1
| table UserID AccountNum

What's your query?

mrhodes93
Explorer

Okay let me work with this. I presented the question in a sample table but my query (for page 1) looks something like this:

sourcetype="PCF:log" "/page1" | rex field=msg "UserID: (?\w+)" | rex field=msg "AccountNum: (?\w+)" | dedup UserID, AccountNum | table UserID, AccountNum

The only difference for page 2 would be the literal "/page2". How would this information change what you've posted above?

0 Karma

to4kawa
Ultra Champion
sourcetype="PCF:log" "/page1" OR "/page2" 
| rex field=msg "UserID: (?<UserID>\w+)" 
| rex field=msg "AccountNum: (?<AccountNum>\w+)" 
| streamstats window=1 count(eval(searchmatch("/page1"))) as page_one count(eval(searchmatch("/page2"))) as page_two
| where page_one > 0 AND page_two > 0
| table UserID, AccountNum

mrhodes93
Explorer

I was able to get exactly what I needed by simply removing the the makesresults through the table commands replacing with my two queries noted. I greatly appreciate the help @to4kawa!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...