topic Re: How to merge my two searches to output combined stats count results? in Splunk Search

How to merge my two searches to output combined stats count results?

sahoo0233 — Thu, 19 Feb 2015 10:56:59 GMT

I have two URL's for which i take counts separately,

For example :

(1)

host="mobile_host" uri="/m.google.com/company-information/cs/*" |stats count AS URL, count(eval(match(uri, "^(/m.google.com/company-information/cs/company-profile)"))) AS company-profile, count(eval(match(uri, "^(/m.google.com/company-information/cs/sales-preparation)"))) AS sales-preparation

Result is :

url     company profile     sales-preparation
150            25                  25

(2)

host="host" uri="/www-prd.google.com/company-information/cs/*" |stats count AS URL, count(eval(match(uri, "^(/www-prd.google.com/company-information/cs/company-profile)"))) AS company-profile, count(eval(match(uri, "^(/www-prd.google.com/company-information/cs/sales-preparation)"))) AS sales-preparation

Result is :

url     company profile     sales-preparation
150            75                  75

So now what i want is i need the above two searches to be merged so that i should get an combined output as below:

url     company profile     sales-preparation
300            200                 100

Please i need help in this issue, i need to get a combined output for the above:
Solutions please....

Re: How to merge my two searches to output combined stats count results?

sahoo0233 — Thu, 19 Feb 2015 10:58:27 GMT

for the first string : output is like

url : 150
company profile : 75
sales-preparation : 75

similar for string 2 and output

i need a solution asap

Re: How to merge my two searches to output combined stats count results?

markthompson — Thu, 19 Feb 2015 11:01:55 GMT

Can you clarify what you actually need to do? You need to add the 2 values together?

If so, why not transaction them, then use rex to create new fields and just add the fields together?

Re: How to merge my two searches to output combined stats count results?

sahoo0233 — Thu, 19 Feb 2015 11:08:47 GMT

hey mark, i need to add the two values together and get a combined output.

I am just a learner and working on it, so could you please give an example how to create new fields using rex?

Re: How to merge my two searches to output combined stats count results?

markthompson — Thu, 19 Feb 2015 11:16:12 GMT

Ok, so can you provide examples of the 2 events please and I'll write you a search string

Re: How to merge my two searches to output combined stats count results?

sahoo0233 — Thu, 19 Feb 2015 11:23:23 GMT

Hi Mark,

The above are the two strings(in the description i gave) , i just replaced my company name with google as per confidential purposes.

please go through the two strings and give me a common search string ...

for reference below are the two strings:

host="mobile_host" uri="/m.google.com/company-information/cs/*" |stats count AS URL, count(eval(match(uri, "^(/m.google.com/company-information/cs/company-profile)"))) AS company-profile, count(eval(match(uri, "^(/m.google.com/company-information/cs/sales-preparation)"))) AS sales-preparation
1. host="host" uri="/www-prd.google.com/company-information/cs/*" |stats count AS URL, count(eval(match(uri, "^(/www-prd.google.com/company-information/cs/company-profile)"))) AS company-profile, count(eval(match(uri, "^(/www-prd.google.com/company-information/cs/sales-preparation)"))) AS sales-preparation

Thanks for the reply... 🙂

Re: How to merge my two searches to output combined stats count results?

tom_frotscher — Thu, 19 Feb 2015 12:00:04 GMT

Hi,

I don't know if i got this correctly. Have you tried something like:

(host="mobile_host" OR host="host") (uri="/m.google.com/company-information/cs/*" OR uri="/www-prd.google.com/company-information/cs/*") | stats count AS URL, count(eval(match(uri, "^(/m.google.com/company-information/cs/company-profile)"))) AS company-profile_m, count(eval(match(uri, "^(/m.google.com/company-information/cs/sales-preparation)"))) AS sales-preparation_m count(eval(match(uri, "^(/www-prd.google.com/company-information/cs/company-profile)"))) AS company-profile, count(eval(match(uri, "^(/www-prd.google.com/company-information/cs/sales-preparation)"))) AS sales-preparation | eval "sum_company-profile" = 'company-profile' + 'company-profile_m' | eval "sum_sales-preparation"='sales-preparation' + 'sales-preparation_m'

Or do you get wrong results out of this search?

Re: How to merge my two searches to output combined stats count results?

markthompson — Thu, 19 Feb 2015 12:30:43 GMT

I think Tom might have the right answer, so please share if it's correct, also convert it to answer if it is correct.

Re: How to merge my two searches to output combined stats count results?

markthompson — Thu, 19 Feb 2015 12:30:56 GMT

Hi Tom,
I believe you're right, but is that really optimized?

Re: How to merge my two searches to output combined stats count results?

sahoo0233 — Mon, 28 Sep 2020 19:01:41 GMT

HI tom,

I have run the string you provided but didn't get he desired output.

I got the below output:

URL : 765701

company-profile_m : 98110

company-profile : 543304

In the above i have removed the sales part and have run it. Now what i want is, In the table i need to get

URL : value(765701)
Sum_company-profile : summation of company-profile+company-profile_m(641414)

Solution please.....

Re: How to merge my two searches to output combined stats count results?

dwaddle — Fri, 20 Feb 2015 04:08:47 GMT

I'd wager this works. When you are using match, it's based on regular expressions so you can get away with being a little imprecise. Based on your regexes in the two different searches you should have enough commonality between the "company-profile" and "sales-preparation" on both the mobile and not-mobile sites...

( host="mobile_host" OR host="host" ) 
( uri="/m.google.com/company-information/cs/*" OR uri="/www-prd.google.com/company-information/cs/*" )
|stats count AS URL, 
       count(eval(match(uri, "/company-profile/"))) AS company-profile, 
       count(eval(match(uri, "/sales-preparation/"))) AS sales-preparation

Re: How to merge my two searches to output combined stats count results?

sahoo0233 — Fri, 20 Feb 2015 07:22:25 GMT

Hi dwaddle, its not not working. May be its adding the URL count but not the company-profile nor the sales-preapartion

Re: How to merge my two searches to output combined stats count results?

dwaddle — Fri, 20 Feb 2015 14:51:45 GMT

Well, just a thought here. It's hard to make 100% correct regexes for anonymized URLs. I don't think this work is for Google - there is no www-prd.google.com, nor does http://www.google.com/company-information actually exist. Further, I doubt Google would use Splunk to do web analytics for their own websites.

I don't have examples of your actual URLs so I'm counting on you to do be able to see the general technique and apply it in lieu of just copypasta. So let's break down the stats command above.

 |stats count AS URL, 
        count(eval(match(uri, "/company-profile/"))) AS company-profile, 
        count(eval(match(uri, "/sales-preparation/"))) AS sales-preparation

Each of the count(eval(match commands, counts items where the field uri matches a given regular expression. For company-profile the regex is general enough that anything with "/company-profile/" in the uri field should be a match, like:

m.google.com/company-information/cs/company-profile/index.html
www-prd.google.com/company-information/cs/company-profile/about-us/locations/Texas/Austin.html

And so on. Since you anonymized URLs and didn't examples to write regexes from, it's a guessing game to get the regexes right. If the above search didn't work, it's because the regexes are wrong. Get them right and it will work -- but that part is on you....

Re: How to merge my two searches to output combined stats count results?

markthompson — Fri, 20 Feb 2015 15:13:50 GMT

Sahoo,
Please provide an accurate example of your logs otherwise it's impossible for the community to help you. You must give as much detail as possible in order for anyone to be able to give you an accurate solution to your issue. Especially when regex's are concerned as they are based on the strings you are looking for.