Splunk Search

Why does using the transaction and stats commands generate different result counts?

ofaura
Path Finder

Hello,

I am migrating some transaction commands to stats because performance is better, but I have seen that if the time period is some hours then I didn't get the same results.

If I execute this:

index=web sourcetype=iis earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 SessionId=*
| transaction keepevicted=true SessionId | timechart  span=1h count 

I got:

_time↕            count↕    

2017-01-30 01:00  137  
2017-01-30 02:00  111  
2017-01-30 03:00  106  
2017-01-30 04:00  130  
2017-01-30 05:00  147  
2017-01-30 06:00  358  
2017-01-30 07:00  1010  
2017-01-30 08:00  2692  
2017-01-30 09:00  2996  
2017-01-30 10:00  3421  
2017-01-30 11:00  2761  
2017-01-30 12:00  1510  

When I migrate to stats:

index=web sourcetype=iis earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 SessionId=* 
| stats min(_time) as _time by SessionId | timechart span=1h count 

Here the result:

_time↕            count↕    

2017-01-30 01:00  137  
2017-01-30 02:00  111  
2017-01-30 03:00  106  
2017-01-30 04:00  130  
2017-01-30 05:00  147  
2017-01-30 06:00  358  
2017-01-30 07:00  1010  
2017-01-30 08:00  2692  
2017-01-30 09:00  2989  
2017-01-30 10:00  3378  
2017-01-30 11:00  2679  
2017-01-30 12:00  1456  

Why results are different from 2017-01-30 09:00?

0 Karma

jplumsdaine22
Influencer

Hard to say without seeing your data, but possibly you have many events coming in with identical SessionIDs . When you run this:

... | stats min(_time) as _time by SessionId | ...

If there are 10000 events with the same SessionId splunk will return only one. Whereas transaction may return multiple transactions. What are you trying to achieve with your search? A count of unique sessionIds in a given period?

You will get accurate results with the following:

index=web sourcetype=iis earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 SessionId=* | timechart span=1h dc(SessionID)
0 Karma

lguinn2
Legend

I would look at the search job inspector for each of these commands and see if there were underlying errors or messages.

0 Karma

ofaura
Path Finder

Hi,

Here is the job inspector of both sql:

(stats):

This search has completed and has returned 12 results by scanning 796,736 events in 8.394 seconds

The following messages were returned by the search subsystem:
info : Your timerange was substituted based on your search string

(SID: 1485814208.788) search.log

˅  Execution costs 



Duration (seconds)


Component

Invocations

Input count

Output count


   0.07  command.addinfo  66  122,435  122,435  
   0.03  command.fields  66  122,435  122,435  
   0.47  command.prestats  66  122,435  18,116  
   7.62  command.search  66  -  122,435  
   1.28  command.search.filter  65  -  -  
   0.37  command.search.fieldalias  65  796,736  796,736  
   0.26  command.search.index  2  -  -  
   0.26  command.search.batch.cache_setup  1  -  -  
   0.20  command.search.batch.sort  1  -  -  
   0.18  command.search.calcfields  65  796,736  796,736  
   0.02  command.search.expand_search  1  -  -  
   0.00  command.search.index.usec_1_8  124  -  -  
   0.00  command.search.index.usec_8_64  98  -  -  
   2.83  command.search.kv  65  -  -  
   2.59  command.search.rawdata  65  -  -  
   0.06  command.search.tags  65  122,435  122,435  
   0.06  command.search.typer  65  122,435  122,435  
   0.05  command.search.lookups  65  796,736  796,736  
   0.02  command.search.summary  66  -  -  
   0.13  command.stats  68  18,116  -  
   0.10  command.stats.execute_input  67  18,116  -  
   0.03  command.stats.execute_output  1  -  -  
   0.01  command.timechart  1  15,193  12  
   0.00  dispatch.check_disk_usage  1  -  -  
   0.00  dispatch.createdSearchResultInfrastructure  1  -  -  
   0.04  dispatch.evaluate  1  -  -  
   0.03  dispatch.evaluate.search  1  -  -  
   0.00  dispatch.evaluate.stats  1  -  -  
   0.00  dispatch.evaluate.timechart  1  -  -  
   7.93  dispatch.fetch  67  -  -  
   8.13  dispatch.localSearch  1  -  -  
   0.04  dispatch.optimize.FinalEval  1  -  -  
   0.13  dispatch.optimize.matchReportAcceleration  1  -  -  
   0.00  dispatch.optimize.optimization  1  -  -  
   0.00  dispatch.optimize.reparse  1  -  -  
   0.00  dispatch.optimize.toJson  1  -  -  
   0.00  dispatch.optimize.toSpl  1  -  -  
   0.19  dispatch.preview  7  -  -  
   0.14  dispatch.preview.stats.execute_output  7  -  -  
   0.06  dispatch.preview.command.timechart  7  40,653  84  
   0.01  dispatch.preview.write_results_to_disk  7  -  -  
   8.05  dispatch.stream.local  66  -  -  
   0.03  dispatch.writeStatus  21  -  -  
   0.02  startup.configuration  1  -  -  
   0.04  startup.handoff  1  -  -  


˅  Search job properties 


canSummarize true  
createTime 2017-01-30T23:10:26.000+01:00  
cursorTime 1970-01-01T01:00:00.000+01:00  
custom 
{   [-] 
       dispatch.earliest_time: -24h@h       
       dispatch.latest_time: now        
       dispatch.sample_ratio: 1     
       display.general.type: statistics     
       display.page.search.mode: smart      
       display.page.search.tab: statistics      
       search: index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 | stats min(_time) as _time by SessionId | timechart span=1h count          
}    

defaultSaveTTL 604800  
defaultTTL 600  
delegate None  
diskUsage 196608  
dispatchState DONE  
doneProgress 1  
dropCount None  
eai:acl 
{   [-] 
       app: intranet        
       can_write: true      
       modifiable: true     
       owner: admin     
       perms: { [+] 
       }        
       sharing: global      
       ttl: 600         
}    

earliestTime 2017-01-30T01:00:00.000+01:00  
eventAvailableCount None  
eventCount 122435  
eventFieldCount None  
eventIsStreaming true  
eventIsTruncated true  
eventSearch search (index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00)  
eventSorting none  
fieldMetadataResults 
{   [-] 
       _time: { [+] 
       }            
}    

fieldMetadataStatic 
{   [-] 
       _time: { [+] 
       }            
}    

isBatchModeSearch true  
isDone true  
isEventsPreviewEnabled None  
isFailed None  
isFinalized None  
isGoodSummarizationCandidate true  
isPaused None  
isPreviewEnabled true  
isRealTimeSearch None  
isRemoteTimeline None  
isSaved None  
isSavedSearch None  
isTimeCursored true  
isZombie None  
keywords sessionid::* earliest::01/30/2017:01:00:00 index::web latest::01/30/2017:12:30:00 sourcetype::iis  
label None  
latestTime 2017-01-30T12:30:00.000+01:00  
meanPreviewPeriod 1.199143  
modifiedTime 2017-01-30T23:11:57.717+01:00  
normalizedSearch litsearch ( index=web sourcetype=iis SessionId=* _time>=1485734400.000 _time<1485775800.000 ) | addinfo type=count label=prereport_events | fields keepcolorder=t "SessionId" "_time" "prestats_reserved_*" "psrsvd_*" | prestats min(_time) by SessionId  
numPreviews 7  
optimizedSearch | search (index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00) | stats min(_time) as _time by SessionId | timechart span=1h count  
pid 25325  
priority 5  
reduceSearch sistats min(_time) as _time by SessionId  
remoteSearch litsearch ( index=web sourcetype=iis SessionId=* _time>=1485734400.000 _time<1485775800.000 ) | addinfo type=count label=prereport_events | fields keepcolorder=t "SessionId" "_time" "prestats_reserved_*" "psrsvd_*" | prestats min(_time) by SessionId  
reportSearch stats min(_time) as _time by SessionId | timechart span=1h count  
request 
{   [-] 
       adhoc_search_level: smart        
       auto_cancel: 30      
       check_risky_command: false       
       custom.dispatch.earliest_time: -24h@h        
       custom.dispatch.latest_time: now     
       custom.dispatch.sample_ratio: 1      
       custom.display.general.type: statistics      
       custom.display.page.search.mode: smart       
       custom.display.page.search.tab: statistics       
       custom.search: index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 | stats min(_time) as _time by SessionId | timechart span=1h count       
       earliest_time: -24h@h        
       indexedRealtime:         
       latest_time: now     
       preview: 1       
       provenance: UI:Search        
       rf: *        
       sample_ratio: 1      
       search: search index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 | stats min(_time) as _time by SessionId | timechart span=1h count       
       status_buckets: 300      
       ui_dispatch_app: intranet            
}    

resultCount 12  
resultIsStreaming None  
resultPreviewCount 12  
runDuration 8.394  
runtime 
{   [-] 
       auto_cancel: 30      
       auto_pause: 0            
}    

sampleRatio 1  
sampleSeed 0  
scanCount 796736  
search search index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 | stats min(_time) as _time by SessionId | timechart span=1h count  
searchCanBeEventType None  
searchEarliestTime 1485734400  
searchLatestTime 1485775800  
searchProviders 
[   [-] 
       XX-XX-XXX-XX-XX      
]    

searchTotalBucketsCount 2  
searchTotalEliminatedBucketsCount None  
sid 1485814208.788  
statusBuckets None  
ttl 600  
Additional info     search.log  
0 Karma

ofaura
Path Finder

And here the Transaction one:

This search has completed and has returned 12 results by scanning 796,736 events in 19.702 seconds

The following messages were returned by the search subsystem:
info : Your timerange was substituted based on your search string

(SID: 1485814859.795) search.log

˅  Execution costs 



Duration (seconds)


Component

Invocations

Input count

Output count


   0.06  command.fields  128  122,435  122,435  
   0.26  command.pretransaction  256  367,305  367,305  
   8.54  command.search  128  -  122,435  
   1.51  command.search.filter  127  -  -  
   0.49  command.search.fieldalias  127  796,736  796,736  
   0.27  command.search.index  128  -  -  
   0.26  command.search.calcfields  127  796,736  796,736  
   0.02  command.search.expand_search  1  -  -  
   0.00  command.search.index.usec_1_8  704  -  -  
   0.00  command.search.index.usec_8_64  112  -  -  
   3.32  command.search.rawdata  127  -  -  
   2.83  command.search.kv  127  -  -  
   0.13  command.search.tags  127  122,435  122,435  
   0.11  command.search.typer  127  122,435  122,435  
   0.09  command.search.lookups  127  796,736  796,736  
   0.03  command.search.summary  128  -  -  
   0.13  command.timechart  130  15,379  -  
   0.13  command.timechart.execute_input  129  15,379  -  
   0.00  command.timechart.execute_output  1  -  -  
   18.94  command.transaction  129  122,435  20,378  
   0.00  dispatch.check_disk_usage  2  -  -  
   0.00  dispatch.createdSearchResultInfrastructure  1  -  -  
   0.04  dispatch.evaluate  1  -  -  
   0.04  dispatch.evaluate.search  1  -  -  
   0.00  dispatch.evaluate.timechart  1  -  -  
   0.00  dispatch.evaluate.transaction  1  -  -  
   0.44  dispatch.fetch  129  -  -  
   8.51  dispatch.localSearch  1  -  -  
   0.04  dispatch.optimize.FinalEval  1  -  -  
   0.16  dispatch.optimize.matchReportAcceleration  1  -  -  
   0.00  dispatch.optimize.optimization  1  -  -  
   0.00  dispatch.optimize.reparse  1  -  -  
   0.00  dispatch.optimize.toJson  1  -  -  
   0.00  dispatch.optimize.toSpl  1  -  -  
   0.02  dispatch.preview  16  -  -  
   0.02  dispatch.preview.timechart.execute_output  16  -  -  
   0.02  dispatch.preview.write_results_to_disk  16  -  -  
   0.22  dispatch.results_combiner  129  -  -  
   8.55  dispatch.stream.local  128  -  -  
   0.07  dispatch.writeStatus  39  -  -  
   0.02  startup.configuration  1  -  -  
   0.04  startup.handoff  1  -  -  




˅  Search job properties 


canSummarize None  
createTime 2017-01-30T23:20:59.000+01:00  
cursorTime 2017-01-30T01:00:09.000+01:00  
custom 
{   [-] 
       dispatch.earliest_time: 1485730800.000       
       dispatch.latest_time: 1485814167.000     
       dispatch.sample_ratio: 1     
       display.general.type: statistics     
       display.page.search.mode: smart      
       display.page.search.tab: statistics      
       search: index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 | transaction keepevicted=true SessionId | timechart span=1h count          
}    

defaultSaveTTL 604800  
defaultTTL 600  
delegate None  
diskUsage 212992  
dispatchState DONE  
doneProgress 1  
dropCount None  
eai:acl 
{   [-] 
       app: intranet        
       can_write: true      
       modifiable: true     
       owner: admin     
       perms: { [+] 
       }        
       sharing: global      
       ttl: 600         
}    

earliestTime 2017-01-30T01:00:00.000+01:00  
eventAvailableCount None  
eventCount 15379  
eventFieldCount None  
eventIsStreaming true  
eventIsTruncated true  
eventSearch search (index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00) | transaction keepevicted=true SessionId  
eventSorting desc  
fieldMetadataResults 
{   [-] 
       _time: { [+] 
       }            
}    

fieldMetadataStatic 
{   [-] 
       _time: { [+] 
       }            
}    

indexEarliestTime 1485734429  
indexLatestTime 1485775808  
isBatchModeSearch None  
isDone true  
isEventsPreviewEnabled None  
isFailed None  
isFinalized None  
isPaused None  
isPreviewEnabled true  
isRealTimeSearch None  
isRemoteTimeline None  
isSaved None  
isSavedSearch None  
isTimeCursored true  
isZombie None  
keywords SessionId::* earliest::01/30/2017:01:00:00 index::web latest::01/30/2017:12:30:00 sourcetype::iis  
label None  
latestTime 2017-01-30T12:30:00.000+01:00  
meanPreviewPeriod 1.231375  
modifiedTime 2017-01-30T23:21:25.189+01:00  
normalizedSearch litsearch ( index=web sourcetype=iis SessionId=* _time>=1485734400.000 _time<1485775800.000 ) | fields keepcolorder=t "SessionId" "_time" "_txn_ends_with" "_txn_filter_match" "_txn_starts_with" "prestats_reserved_*" "psrsvd_*" | pretransaction keepevicted=true SessionId  
numPreviews 16  
optimizedSearch | search (index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00) | transaction keepevicted=true SessionId | timechart span=1h count  
pid 28395  
priority 5  
remoteSearch litsearch ( index=web sourcetype=iis SessionId=* _time>=1485734400.000 _time<1485775800.000 ) | fields keepcolorder=t "SessionId" "_time" "_txn_ends_with" "_txn_filter_match" "_txn_starts_with" "prestats_reserved_*" "psrsvd_*" | pretransaction keepevicted=true SessionId  
reportSearch timechart span=1h count  
request 
{   [-] 
       adhoc_search_level: smart        
       auto_cancel: 30      
       check_risky_command: false       
       custom.dispatch.earliest_time: 1485730800.000        
       custom.dispatch.latest_time: 1485814167.000      
       custom.dispatch.sample_ratio: 1      
       custom.display.general.type: statistics      
       custom.display.page.search.mode: smart       
       custom.display.page.search.tab: statistics       
       custom.search: index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 | transaction keepevicted=true SessionId | timechart span=1h count       
       earliest_time: 1485730800.000        
       indexedRealtime:         
       latest_time: 1485814167.000      
       preview: 1       
       provenance: UI:Search        
       rf: *        
       sample_ratio: 1      
       search: search index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 | transaction keepevicted=true SessionId | timechart span=1h count       
       status_buckets: 300      
       ui_dispatch_app: intranet            
}    

resultCount 12  
resultIsStreaming None  
resultPreviewCount 12  
runDuration 19.702  
runtime 
{   [-] 
       auto_cancel: 30      
       auto_pause: 0            
}    

sampleRatio 1  
sampleSeed 0  
scanCount 796736  
search search index=web sourcetype=iis SessionId=* earliest=01/30/2017:01:00:00 latest=01/30/2017:12:30:00 | transaction keepevicted=true SessionId | timechart span=1h count  
searchCanBeEventType None  
searchEarliestTime 1485734400  
searchLatestTime 1485775800  
searchProviders 
[   [-] 
       XX-XX-XXX-XX-XX      
]    

searchTotalBucketsCount 2  
searchTotalEliminatedBucketsCount None  
sid 1485814859.795  
statusBuckets None  
ttl 600  
Additional info     search.log  
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...