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

[ndey]

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

[richgalloway]

Try setting the sums to zero before the stats command.

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

[ndey]

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

[richgalloway]

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