I am seeing this odd behavior in my timechart, for some reason the X axis is reversed with the newest events showing nearest to the Y axis. For some reason this seems to change based on the time window I choose, this only happens on windows larger than 30 mins and for smaller windows, 5mins or less. This seems to show normally with the newest event appearing on the right of the X axis.
Is this some issue with Splunk and how can this be fixed?
Not only does your search not have timechart
but it is calculating something useless for a charted visualization (avg_query_duration
) because it is the same for all values. In any case, try this (guessing at how you might like to use avg_query_duration
😞
... | rex "(?\d+)ms" | eventstats avg(query_duration) as avg_query_duration | eval deviation=(query_duration - avg_query_duration) | timechart avg(deviation)
Basically, I want to extract fields from logs that look like the below entry:
Log sample:
2016-10-06T21:22:15.285+0000 I COMMAND [conn337418] command PersoTestServiceDB.$cmd command: update { update: "Test_Stage", updates: 1000, ordered: false, shardVersion: [ Timestamp 0|0, ObjectId('000000000000000000000000') ] } keyUpdates:0 writeConflicts:0 numYields:0 reslen:232 locks:{ Global: { acquireCount: { r: 2000, w: 2000 } }, Database: { acquireCount: { w: 2000 } }, Collection: { acquireCount: { w: 1000 } }, Metadata: { acquireCount: { w: 1000 } }, oplog: { acquireCount: { w: 1000 } } } protocol:op_command 175ms
The above block is from a MongoDB log file, I am mostly interested in extracting the last field and then sort by the field with the largest value in "ms". I am trying to see how long queries take to complete on average as well as identify the long running queries from the logs. I would also like to list the long running query next to the query time when sorted. Also if the graph can chart time from left to right on the X-axis.
First things first, it doesn't look like you're using the actual timechart command at all.
You're using the stats command. I'm not sure if it's quite this simple, but you could just try sorting your results by _time:
index=web_mongodb host=mongodb-* "protocol:op_command" NOT "sleeping" NOT "splitChunk"
| rex "(?\d+)ms"
| eventstats avg(query_duration) as avg_query_duration
| sort 0 _time
| table _time _raw query_duration avg_query_duration
I'm not sure if this is really what you want to see, but we can help you with further requests too. For example, maybe you just want to see a graph of duration difference (duration - avg duration)...maybe that would be more telling over time?
Do you have a sort in your query?
Could you share your query?
base query | rex "(?\d+)ms" | eventstats avg(query_duration) as avg_query_duration | table _time _raw query_duration avg_query_duration
index=web_mongodb host=mongodb-* "protocol:op_command" NOT "sleeping" NOT "splitChunk" | rex "(?\d+)ms" | eventstats avg(query_duration) as avg_query_duration | table _time _raw query_duration avg_query_duration
Well, if you're charting the query duration and avg_query_duration in a timechart, I would suggest to use an aggregation command. Try like this (add span in timechart per your need)
index=web_mongodb host=mongodb-* "protocol:op_command" NOT "sleeping" NOT "splitChunk" | rex "(?<query_duration>\d+)ms" | timechart max(query_duration) as query_duration | eventstats avg(query_duration) as avg_query_duration
I would like to see the time taken by each query in the log, using "max" will just show the max duration for the time period, but won't chart by each query shown in the logs.
you can use list or values (values dedups)