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

[achetreanu]

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.

[DalJeanis · Accepted solution]

Try this for the hour that you had the problem -

host=myhost "Received INVITE" OR "Sent Invite" OR "Sent re-Invite" 
| eval XCount= if(match(_raw,"Received INVITE"),1,0)
| eval YCount= if(match(_raw,"Sent Invite" ),1,0)
| eval ZCount= if(match(_raw,"Sent re-Invite"),1,0)
| eval TCount= 1
| eval UCount=XCount+YCount+ZCount
| search Tcount!=UCount

If that gets no individual events that count as more than one type, then remove the last line and continue with this -

| bucket _time span=1h 
| eventstats 
    count as CountSum, sum(TCount) as TSum, sum(UCount) as USum,
    sum(XCount) as XSum, sum(YCount) as YSum, sum(ZCount) as ZSum 
    by _time
| where TSum != USum

That will return only the events that are in an hour where the two sums do not match, for further review.

[achetreanu]

Thank you! Fixed a spelling error (ZSum instead the XSum you clearly meant) and tried it. Ended up using this query to count my max CPS (calls per second). I added "1" for TCount to the final result and it matches the events I see when I narrow to the second:

host=myhost* "Received INVITE" OR "Sent Invite" OR "Sent re-Invite"
| eval XCount= if(match(_raw,"Received INVITE"),1,0)
| eval YCount= if(match(_raw,"Sent Invite" ),1,0)
| eval ZCount= if(match(_raw,"Sent re-Invite"),1,0)
| eval TCount= 1
| eval UCount=XCount+YCount+ZCount
| bucket _time span=1s
| eventstats count as CountSum, sum(TCount) as TSum, sum(UCount) as USum, sum(XCount) as XSum, sum(YCount) as YSum, sum(ZCount) as ZSum by _time
| where TSum != USum | chart sum(USum) as count over _time

[DalJeanis]

Ah, good catch. Fixed code to XSum for posterity.

I'm assuming there was at least one event that qualified for more than one of X, Y or Z.

If your question has been answered, please mark an answer as accepted.

---

By the way, best to get into the habit of always marking your code before posting it - just highlight the part of your post that is code, and press the button with 101 010 on it. (Hopefully you are using Chrome so the button is visible.)

It doesn't matter a whole lot with this particular code, but once you start using regexes, the web interface here will delete the tags-in-angle-brackets before posting and other people will not be seeing the code you thought you posted.

[achetreanu]

Thanks for the guidance on details. I don't post here often and I'm still learning the ropes. Will do.

[Richfez]

And another great answer!

[somesoni2]

The count of X + count of Y + count of Z should be same as count of (X OR Y OR Z)if there is no overlap between events with X OR Y OR Z. It seems to me that there are events where one or more of X,Y,Z is available in same event, thus counting individually count of X + count of Y + count of Z gives extra count since an event with one or more of X,Y,Z will be counted more than once. The query that you've is giving you correct results as it's counting the number of events which have either of X, Y, Z. If you really want to count the number of events of each X, Y, Z in single search, try like this

host=myhost "X" OR "Y" OR "Z" |eval temp=if(searchmatch("X"),1,0)+if(searchmatch("Y"),1,0)+if(searchmatch("Z"),1,0)| bucket _time span=1s | chart sum(temp) as count over _time

In the firewall log example of @rich7177, the field action can either be allowed or blocked in a single event, not both, so count for him will always be same/correct. You're doing text based search and that can capture events with overlapping search criteria.

[achetreanu]

Thank you! Yes, in SQL terms, I want a full outer join + inner join. I used your query and it works - the results are more believable. I get a bigger count than the biggest count of either X or Y or Z now. I probably need to refine my query a bit more - I checked a few results by drilling down to the specified second and I see 1 or 2 extra counts but this is the right direction.
Thank you both very much!

[Richfez]

Ah, I think this will be the answer. Excellent work sir on digging down until you found the issue. Glad I could serve as a great example of what won't work!

[somesoni2]

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.

[achetreanu]

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.

[somesoni2]

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?

[achetreanu]

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.

[Richfez]

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.

[achetreanu]

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.

[Richfez]

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.

[achetreanu]

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

[Richfez]

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