Splunk Search

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

sahoo0233
Path Finder

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

0 Karma

dwaddle
SplunkTrust
SplunkTrust

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

dwaddle
SplunkTrust
SplunkTrust

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

0 Karma

sahoo0233
Path Finder

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

0 Karma

markthompson
Builder

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.

sahoo0233
Path Finder

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

0 Karma

markthompson
Builder

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?

0 Karma

sahoo0233
Path Finder

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?

0 Karma

markthompson
Builder

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

0 Karma

sahoo0233
Path Finder

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:

  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

    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... 🙂

0 Karma

markthompson
Builder

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

0 Karma

sahoo0233
Path Finder

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

0 Karma

tom_frotscher
Builder

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?

0 Karma

markthompson
Builder

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

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