topic count(var) by "a list of values within a field" in Splunk Search

count(var) by "a list of values within a field"

ndey — Mon, 20 Aug 2018 11:18:11 GMT

First of all, sorry, if I am missing something really obvious here but after hours of googling I am still stuck with the following problem.

Basically I have a list of URLs and a score in the format like that:

http://www.abc.com/abc/abc.html 50
http://www.abc.com/abc/abc.html 30
www.xyz.org/asd/ 12
qwer.com/asd 7

What I try to achieve now is to group some of the URLs and have the sum of the score displayed in a table. For example:

abc.com & xyz.org = "External Sites" will then lead to the following table:

`Site name | Sum

External Sites | 92`

The approach so far is to have an

| eval siteName = if(match(url, [some regex], ...)

add a new field with the site name which works.

The interesting part now is, that some of the groups might not have events present all the time and | stats sum(score) as Sum by siteName obviously gives me only sum of the groups that are present.

Is there any way to give me a table for an list of sitenames that "could" be there like the following:

`Site name | Sum

External Sites | 92
Internal Sites | 0`

Thank you very much in advance

Andreas

Re: count(var) by "a list of values within a field"

richgalloway — Mon, 20 Aug 2018 11:52:34 GMT

Try setting the sums to zero before the stats command.

... | eval 'External Sites'=0, 'Internal Sites'=0 | stats sum(...

Re: count(var) by "a list of values within a field"

ndey — Tue, 21 Aug 2018 08:18:26 GMT

Whoops, I just realized, that the example tables I put into the post are messed up, which might have made the desired format a bit unclear. Here is another try

Site Name - Sum
...............................
Internal Sites - 20
External Sites - 40

The problem is I am looking for a way to have one filed containing tags like "External Sites", "Internal Sites" .... and then sum up the score by these tags

Re: count(var) by "a list of values within a field"

richgalloway — Tue, 21 Aug 2018 11:54:18 GMT

You have the general idea in your original question. Use case to evaluate URLs as either internal or external. Then use the stats command to count internal vs external sites.

... | eval siteName = case(if(match(url, "something that matches internal sites"),"internal", 1=1, "external") | eval external=0, internal=0 | stats sum by siteName