How to append values from different searches based on the occurrence of the first one?

[ldjamesl]

Hello there, I know this question might be worded a little weird. I'm trying to create a report that shows the top words used in a refer URL and show How many times that page was visited. So far I have the first part with this query:

index=myIndex sourcetype=myIIS 
AND host="*mySpecificHost*" 
AND cs_Referer="/mySpecificPath*"  
AND cs_uri_stem="/myPage1" 
  OR cs_uri_stem="/myPage2*" 
| rex field=_raw "myQueryParameter=(?<myQueryParameter>.+)\" 200" 
| stats count by myQueryParameter
| sort -count limit=20

This returns the top 20 query parameters I'm looking on the refer URL on something like this:

bananas - 2005
monkeys - 1432
mate - 1213

and so on...

now I would like to include how many times that same word was used originally instead of being the refer URL, therefore my en results should be something like:

Word - Count - Total Count
bananas - 2005 - 5004
monkeys - 1432 - 1500
mate - 1213 - 7000

I tried to create some kind of foreach on the parameter I'm looking for to evaluate the times it appears on a different query, something like:

 index=myIndex sourcetype=myIIS 
 AND host="*mySpecificHost*" 
 AND cs_Referer="/mySpecificPath*"  
 AND cs_uri_stem="/myPage1" 
   OR cs_uri_stem="/myPage2*" 
 | rex field=_raw "myQueryParameter=(?<myQueryParameter>.+)\" 200" 
 | stats count by myQueryParameter

  foreach myQueryParameter* 
     append [search base stuff 
     AND cs_uri_stem="/originalStem"
     AND myQueryParameter=myQueryParameter
    | stats count as TotalCount]

 | sort -count limit=20

Obviously the query above is mostly gibberish, and that's where I'm stuck, I was thinking maybe there is some other way that is simpler, but I've been looking for more than a week online with no luck. I guess I just don't know how to really word the question.

I managed to make a query similar of what I want but for the total number of events, not for the top X:

index=myIndex  sourcetype=myIIS AND host="*myFarm*" 
AND cs_Referer="http://www.myreferUrl.com*" 
AND cs_Referer="*myDesiredParameter=*" 
AND cs_uri_stem="/myPage1" 
OR cs_uri_stem="/myPage2*" 
| bucket span=1h _time
| stats count as TotalRefer by _time
    | appendcols  [search index=myIndex  sourcetype=myIIS AND     host="*myFarm*" 
       AND cs_uri_stem="/originalStem*" 
       AND myDesiredParameter!="" 
    | bucket span=1h _time
    |  stats count as TotalOriginal by _time]
| eval Rate=(TotalRefer/TotalOriginal )*100
| stats count by _time Rate TotalOriginal TotalRefer

Any ideas will be appreciated 🙂

---

After reading some answers, and playing with more code I feel I'm in the same place...

I managed to use regex to separate both parameters I need, but i still can't find how to merge both results together.

so far I have:

index=weboperations sourcetype=iis AND host="*-Web*" 
AND (
  cs_Referer=".com/s*" 
  AND cs_Referer="*userSearchQuery=*" 
  AND cs_uri_stem="/Cart/Add" 
  OR cs_uri_stem="/Product*"
) 
OR (
  cs_uri_stem="/s*" 
  AND userSearchQuery!="" 
)
| rex field=_raw "s\?userSearchQuery=(?<userSearch>.+)(?=\" 200|\s80.+|;.+)"
| eval OriginalSearch=if(cs_uri_stem="/s", userSearchQuery, "")
| eval ReferSearchQuery=if(cs_uri_stem="/s*", "", userSearch)
| where ReferSearchQuery != ""
| top limit=20 ReferSearchQuery showperc=f countfield=total

And even try some really fancy examples I found [here][1]. and ended up with something like:

<Search>
| fields + ReferSearchQuery OriginalSearch
| multireport [top limit=20 showperc=f ReferSearchQuery]
              [ stats count(eval(OriginalSearch=ReferSearchQuery)) as OriginalSearches]
| stats list(*) as * by ReferSearchQuery
| where isnotnull(count)
| sort - count

I guess I'll just need to keep playing. Not giving up yet 🙂

[landen99]

Create a rex to capture a field with the values you are looking for. Use eventstats to count the total number of events by that field for each value. Create a rex to capture the field myQueryParameter. Use stats to count the number of events and then to take the first value from the total count obtained previously by for each value of myQueryParameter.

[sundareshr]

Assuming myQueryParameter is in the referer and original, would something like this work?

index=myIndex sourcetype=myIIS  AND host="*mySpecificHost*"  AND cs_Referer="/mySpecificPath*"  AND cs_uri_stem="/myPage1" 
   OR cs_uri_stem="/myPage2*" | rex field=cs-uri-query "myQueryParameter=(?.+)\" 200" | rex field=cs-referer "myQueryParameter=(?.+)\" 200" | timechart span=1h count(myQAMain) as main  count(myQAReferer) | eval rate=ref/main*100

[ldjamesl]

Thank you for your answer, I tried it, but I'm guessing I'm missing on how to reference separate queries like "myQAMain" I'll play with this and let you know how it goes 🙂