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?
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)
I would look at the search job inspector for each of these commands and see if there were underlying errors or messages.
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
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