Archive
Highlighted

How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

New Member

How can I change this query to count the SUM of my events/sec instead of the count of (X OR Y OR Z)/sec :

host=myhost "X" OR "Y" OR "Z" | bucket _time span=1s | chart count over _time

Thank you!- A.C.

Tags (1)
0 Karma
Highlighted

Re: How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

SplunkTrust
SplunkTrust

Try

host=myhost "X" OR "Y" OR "Z" | bucket _time span=1s | chart sum(myfield) over _time

Replace "myfield" with whatever field you are trying to sum.

0 Karma
Highlighted

Re: How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

New Member

Thank you, rich7177. Please my my comments to the comment above.

0 Karma
Highlighted

Re: How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

SplunkTrust
SplunkTrust

Well sure, darn it, never as simple as expected. 🙂 I'll take a look, but somesoni2 rocks these things pretty well. 🙂

0 Karma
Highlighted

Re: How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

SplunkTrust
SplunkTrust

What is the difference (for your data) between count of (X OR Y OR Z) and count of (SUM(X, Y Z))? If X, Y and Z are the string filters, then they both should be same.

0 Karma
Highlighted

Re: How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

New Member

Trying to determine calls per second (SIP) from my logs.
Adding incoming SIP INVITE + outgoing SIP INVITE + outgoing SIP INVITE.

This is my actual filter:
host=myhost "Received INVITE" OR "Sent Invite" OR "Sent re-Invite" | bucket _time span=1s | chart count over _time

This is how my log actually looks like, not very helpful:
12:12:49.773|FYI| -1 | 0X5710BDE1 | U1 : Received INVITE

Trying to find something to use as 'myfield', as rich7177 suggested. Maybe regex to extract the '0X5710BDE1 ', which would be unique within the second but not within my timespan.

Hope I'm making sense and thank you both - rich and somesoni2.

0 Karma
Highlighted

Re: How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

New Member

Also - to your comment that they should be the same - I thought so but then count of X + count of Y + count of Z is not equal to count of (X OR Y OR Z).
I'm not 100% sure but my guess is that if I have X and Y and Z logs in within the same second, only 1 instead of 3 get counted.
For example, count of X is 10,997, count of Y is 13,419 (totaling 24,416) while count of (X OR Y) is 22,824. My first guess is that I'm not counting all but only one within a second.

0 Karma
Highlighted

Re: How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

SplunkTrust
SplunkTrust

Just want to understand what is wrong with current search's output? Do you want to merge the count of some events occurred within same sec or something?

0 Karma
Highlighted

Re: How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

New Member

I guess I'm being a bit nit-picky here. Trying to get the CPS (call per second) value from logs.

I noticed that the max value is the same if I add extra "count of", even over large timespans. This didn't make sense to me, unless "count of X + count of Y + count of Z) <> count of (X OR Y OR Z).

It is hard to find mismatches within the second but if I set "bucket _time span=1h", I can see it clearly.
I believe that if I have one of each X, Y and Z happening inside the same second, the count of (X OR Y OR Z) = 1 and not 3. I need to count each occurrence (log).

For example, within 1h, count of X = 43, count of Y = 107, count of Z = 173, adding to a total of 323.
Within 1h, count of X OR Y OR Z is 316. So my conclusion is that within that hour, 7 times I had two events happening within the same second and those two events were counted as one 7 times within the hour.

This is how I get count of X:
host=myhost "Received INVITE" | bucket _time span=1h | chart count over _time
This is how I get count of Y:
host=myhost "Sent Invite" | bucket _time span=1h | chart count over _time
and so on
This is how I get count of X OR Y OR Z:
host=myhost "Received INVITE" OR "Sent Invite" OR "Sent re-Invite" | bucket _time span=1h | chart count over _time.

I'm not sure this clarifies more what I'm trying to do. I did however get an approximation that is well covering my max CPS by adding max count of X + max count of Y + max count of Z,

Thanks for your help.
A.C.

0 Karma
Highlighted

Re: How to write count of (SUM(X,Y,Z)) instead of count of (X OR Y OR Z) ?

SplunkTrust
SplunkTrust

I can see what you are saying but I can't seem to replicate it. Here's what I did to try, though, follow along the logic and tell me where your scenario differed from what I've done:

My personal firewall logs here at home have entries on the same second over a period of 1 hour late last night (chosen so both start and end of period is in the past and static.)

index=fw | bucket _time span=1s | chart count over _time | addcoltotals count

In that case, I had 913 (or something like that) events and 913 was the overall count. That matched. I tried this over a variety of spans and the total and eventcount always matched no matter what I did. The count of lines in the statistics tab got smaller, but that's because some seconds summarized to 2 or 3 events.

So in case maybe it had something to do with the OR, I tried:

index=fw action=allowed OR action=blocked 
| bucket _time span=1s | chart count over _time | addcoltotals count

And that also always matched counts, 376 over this time period. There are individual seconds with two blocked events, some with two allowed events, and some seconds with both types of events so I think all combinations were covered.

Now, the real question here is does the individual events for each of the OR pieces add up right?

Using action=allowed all matches at 275. Using action=blocked also matches at 101. 275+101 is indeed 376, so it still matches.

I also tried adding my own "counter" field to sum instead of count on.

index=fw action=blocked OR action=allowed 
| eval ticker=1 | bucket _time span=1m 
| chart sum(ticker) as count over _time by action| addcoltotals 

And over all the variations (including and not including "by action" in the chart because I'm lazy, various time spans, etc...) of that I couldn't get it to show non-matching numbers.

What that leaves me with is two things:

One is I can't easily test sub-second events because I don't have that resolution on the logs and don't have any available with milliseconds or whatever. Mine are just to the second with ".000" at the end. I can find some if we think this may be required.

The second is what exact version of Splunk? I'm using 6.5.2 in my test machine here.

0 Karma