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

[DanPederEriksen]

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.

[somesoni2 · Accepted solution]

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

[DanPederEriksen]

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

[woodcock]

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.

[DanPederEriksen]

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?

[steveyz]

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

[woodcock]

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