topic Re: Distinct results with multiple fields & multiple queries? in Splunk Search

Distinct results with multiple fields & multiple queries?

mrhodes93 — Fri, 28 Feb 2020 18:42:48 GMT

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.

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.

Re: Distinct results with multiple fields & multiple queries?

to4kawa — Fri, 28 Feb 2020 19:22:46 GMT

| 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?

Re: Distinct results with multiple fields & multiple queries?

mrhodes93 — Mon, 02 Mar 2020 14:11:50 GMT

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?

Re: Distinct results with multiple fields & multiple queries?

mrhodes93 — Mon, 02 Mar 2020 16:16:08 GMT

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!

Re: Distinct results with multiple fields & multiple queries?

to4kawa — Mon, 02 Mar 2020 20:04:17 GMT

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