Splunk Search

Why is the x-axis time range reversed in my timechart?

kchongo
New Member

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?

0 Karma

woodcock
Esteemed Legend

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)
0 Karma

kchongo
New Member

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.

0 Karma

maciep
Champion

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?

0 Karma

dbcase
Motivator

Do you have a sort in your query?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Could you share your query?

0 Karma

kchongo
New Member
base query | rex "(?\d+)ms" | eventstats avg(query_duration) as avg_query_duration | table _time _raw query_duration avg_query_duration
0 Karma

kchongo
New Member
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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

kchongo
New Member

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.

0 Karma

dbcase
Motivator

you can use list or values (values dedups)

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...