topic Re: Why am I getting an incorrect stats count from my transaction search? in Splunk Search

Why am I getting an incorrect stats count from my transaction search?

DanPederEriksen — Tue, 11 Aug 2015 10:02:57 GMT

Hi,

Stats count does not count all instances of variables when I use it with transactions.

Search string:

index=alto-alpha (version version=*) OR PresentationStopReason | transaction host startswith=version | search PresentationStopReason

gives the following results:

2015-08-11 08:25:07.458     Info: Starting Proximity Desktop version="desktop-1.0.0-Alpha47" OS_TYPE="osx" QT_VERSION="5.3.2" OSX_VERSION="OS X Mavericks (10.9)"
2015-08-11 09:37:37.877     Info: Connection to endpoint lost, and presentation stopped. PresentationStopReason=NetworkLost
host = 144f9ce211086089578cff547fcb17ae sourcetype = syslog_osx_qt

level="Info" uptime=000.001 Starting Proximity Desktop version="desktop-1.0.0-Alpha39" OS_TYPE="win"
level="Info" uptime=1887.327 EndpointControlAction=StopSharing PresentationStopReason=UserInput
host = dbb672153803a6d8386f0b5839697ab5 sourcetype = syslog_win_qt

2015-08-10 16:39:44.975     Info: Starting Proximity Desktop version="desktop-1.0.0-Alpha47" OS_TYPE="osx" QT_VERSION="5.3.2" OSX_VERSION="OS X Yosemite (10.10)"
2015-08-10 16:56:39.911     Info: EndpointControlAction=StopSharing PresentationStopReason=UserInput
host = 3bcefedc3125186c883196f74c99cdb8 sourcetype = syslog_osx_qt

2015-08-10 16:14:38.144     Info: Starting Proximity Desktop version="desktop-1.0.0-Alpha47" OS_TYPE="osx" QT_VERSION="5.3.2" OSX_VERSION="OS X Yosemite (10.10)"
2015-08-10 16:15:04.600     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
2015-08-10 16:15:17.717     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
2015-08-10 16:15:33.252     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
2015-08-10 16:15:40.505     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
2015-08-10 16:15:48.252     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
2015-08-10 16:48:40.083     Info: EndpointControlAction=StopSharing PresentationStopReason=UserInput
host = 8f8567f4c2d021e1e7f79132f90bceae sourcetype = syslog_osx_qt

2015-08-10 16:13:44.281     Info: Starting Proximity Desktop version="desktop-1.0.0-Alpha47" OS_TYPE="osx" QT_VERSION="5.3.2" OSX_VERSION="OS X Yosemite (10.10)"
2015-08-10 16:13:49.741     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
2015-08-10 16:13:58.410     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
2015-08-10 16:14:09.546     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
host = 8f8567f4c2d021e1e7f79132f90bceae sourcetype = syslog_osx_qt

2015-08-10 16:13:12.804     Info: Starting Proximity Desktop version="desktop-1.0.0-Alpha47" OS_TYPE="osx" QT_VERSION="5.3.2" OSX_VERSION="OS X Yosemite (10.10)"
2015-08-10 16:13:20.478     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
2015-08-10 16:13:29.461     Info: Pairing to endpoint lost, and presentation stopped. PresentationStopReason=PairingLost
host = 8f8567f4c2d021e1e7f79132f90bceae sourcetype = syslog_osx_qt

There are 14 instances of the PresentationStopReason field in the results. When adding |stats count by PresentationStopReason to the search string, the result is as following:

index=alto-alpha (version version=*) OR PresentationStopReason | transaction host startswith=version | search PresentationStopReason | stats count by PresentationStopReason

PresentationStopReason  count
NetworkLost              1
PairingLost              3
UserInput                  3

Giving a total of 7 instances of PresentationStopReason, when there should be 14. Stats count is not working the way I expect. How can I count all the instances of PresentationStopReason? PairingLost should be 10.

Re: Why am I getting an incorrect stats count from my transaction search?

woodcock — Tue, 11 Aug 2015 15:54:27 GMT

This is because stats counts the number of events with each value; the way that you have orchestrated things, any event will only be counted once for any specific value. This is not a bug, although it may be a bit surprising to some. The full reason is that while a field may be multivalued, Splunk doesn't create any event's multivalued field with the same value more than once unless you force it to. Your results are telling you that there are 3 events for which the field PresentationStopReason has the value PairingLost, which is correct.

Re: Why am I getting an incorrect stats count from my transaction search?

steveyz — Tue, 11 Aug 2015 18:40:57 GMT

you can do mvexpand on the PresentationStopReason field before the stats if you want to count each value individually.

Re: Why am I getting an incorrect stats count from my transaction search?

woodcock — Tue, 11 Aug 2015 18:59:54 GMT

But again, unless we tell Splunk to create a MV field with duplicate values, this will not work.

Re: Why am I getting an incorrect stats count from my transaction search?

somesoni2 — Tue, 11 Aug 2015 19:05:48 GMT

The MV field output from the transaction command is similar to "|stats values(field)" (remove duplicate values), not "| stats list(field)" (keep all values). So count correctly , you can try something like this

index=alto-alpha (version version=*) OR PresentationStopReason | transaction host startswith=version | search PresentationStopReason | rex max_match=0 "PresentationStopReason=(?<PresentationStopReason>\w+)" | stats count by PresentationStopReason

Re: Why am I getting an incorrect stats count from my transaction search?

DanPederEriksen — Tue, 11 Aug 2015 19:05:50 GMT

How would I go about counting fields with the same values?

Adding mvlist=true to the transaction almost gets me there, The only problem is that in also creates the value NULL and counts it. E.g:
PresentationStopReason count
NULL 3
PairingLost 1
UserInput 3

If I only could remove the NULL.

One other approach would be expand the events inside the transaction?

Re: Why am I getting an incorrect stats count from my transaction search?

DanPederEriksen — Wed, 12 Aug 2015 07:07:38 GMT

This search query produces the result I am after. Thanks somesoni2!