Splunk Search

How to present a readable time value when epoch value is on the y-axis in a scatter chart?

lessthan80
Explorer

My goal is to present a scatter chart with the size of a file each time a job runs. This requires 3 values: time, size of file, name of job. I have reviewed much of the documentation about time and timecharts and the best representation I have is with time on the y-axis, size of file on the x-axis and the name of job as the legend. the problem is the time value is in epoch and I have been unable to relate a readable time to this chart.

Search saved as a scatter chart

index=main 
sourcetype="MFT_byte_size_data"
| table MFT_job_name, MFT_fromFileBytes, timestamp, _time

Saved as a scatter chart

Data csv file
MFT_job_name    MFT_fromFileBytes   timestamp   _time

xxxxxxxx - CAD xxxxxxxx UL Sat only 130 1525558529  2018-05-05T17:15:29.000-0500
xxxxxxxx - CAD xxxxxxxx UL Sun-Fri  370 1525659756  2018-05-06T21:22:36.000-0500
xxxxxxxx - CAD xxxxxxxx UL Sun-Fri  50940   1525486960  2018-05-04T21:22:40.000-0500
xxxxxxxx - CAD xxxxxxxx UL Sun-Fri  35580   1525400533  2018-05-03T21:22:13.000-0500
xxxxxxxx - CAD xxxxxxxx UL Sun-Fri  45060   1525314133  2018-05-02T21:22:13.000-0500
xxxxxxxx - CAD xxxxxxxx UL Sun-Fri  40860   1525227747  2018-05-01T21:22:27.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 2PM New  4049    1525719615  2018-05-07T14:00:15.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 2PM New  353 1525633211  2018-05-06T14:00:11.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 2PM New  15473   1525546814  2018-05-05T14:00:14.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 2PM New  20737   1525460420  2018-05-04T14:00:20.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 2PM New  20121   1525374057  2018-05-03T14:00:57.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 2PM New  22473   1525287653  2018-05-02T14:00:53.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 2PM New  26897   1525201201  2018-05-01T14:00:01.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 2PM New  4217    1525114867  2018-04-30T14:01:07.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 7PM Sun-Fri  72  1525651216  2018-05-06T19:00:16.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 7PM Sun-Fri  3769    1525478474  2018-05-04T19:01:14.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 7PM Sun-Fri  3993    1525392038  2018-05-03T19:00:38.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 7PM Sun-Fri  4441    1525305625  2018-05-02T19:00:25.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 7PM Sun-Fri  4889    1525219222  2018-05-01T19:00:22.000-0500
xxxxxxxx - CAN-xxxxxxxx UL 7PM Sun-Fri  5561    1525132818  2018-04-30T19:00:18.000-0500
xxxxxxxx - CAN-xxxxxxxx UL new path on FIS side Sat only    15473   1525558529  2018-05-05T17:15:29.000-0500
xxxxxxxx - US xxxxxxxx UL Sat only  130 1525558529  2018-05-05T17:15:29.000-0500
xxxxxxxx - US xxxxxxxx UL Sun-Fri   10288060    1525659756  2018-05-06T21:22:36.000-0500
xxxxxxxx - US xxxxxxxx UL Sun-Fri   14757070    1525486960  2018-05-04T21:22:40.000-0500
xxxxxxxx - US xxxxxxxx UL Sun-Fri   10684390    1525400533  2018-05-03T21:22:13.000-0500
xxxxxxxx - US xxxxxxxx UL Sun-Fri   10858010    1525314133  2018-05-02T21:22:13.000-0500
xxxxxxxx - US xxxxxxxx UL Sun-Fri   13944050    1525227747  2018-05-01T21:22:27.000-0500
xxxxxxxx - US-xxxxxxxx UL 2PM New   584438  1525719615  2018-05-07T14:00:15.000-0500
xxxxxxxx - US-xxxxxxxx UL 2PM New   72  1525633211  2018-05-06T14:00:11.000-0500
xxxxxxxx - US-xxxxxxxx UL 2PM New   72  1525546814  2018-05-05T14:00:14.000-0500
xxxxxxxx - US-xxxxxxxx UL 2PM New   411325  1525460420  2018-05-04T14:00:20.000-0500
xxxxxxxx - US-xxxxxxxx UL 2PM New   346750  1525374057  2018-05-03T14:00:57.000-0500
xxxxxxxx - US-xxxxxxxx UL 2PM New   348905  1525287653  2018-05-02T14:00:53.000-0500
xxxxxxxx - US-xxxxxxxx UL 2PM New   388810  1525201201  2018-05-01T14:00:01.000-0500
xxxxxxxx - US-xxxxxxxx UL 2PM New   545209  1525114867  2018-04-30T14:01:07.000-0500
xxxxxxxx - US-xxxxxxxx UL 7PM Sun-Fri   72  1525651216  2018-05-06T19:00:16.000-0500
xxxxxxxx - US-xxxxxxxx UL 7PM Sun-Fri   253468  1525478474  2018-05-04T19:01:14.000-0500
xxxxxxxx - US-xxxxxxxx UL 7PM Sun-Fri   234053  1525392038  2018-05-03T19:00:38.000-0500
xxxxxxxx - US-xxxxxxxx UL 7PM Sun-Fri   203982  1525305625  2018-05-02T19:00:25.000-0500
xxxxxxxx - US-xxxxxxxx UL 7PM Sun-Fri   250691  1525219222  2018-05-01T19:00:22.000-0500
xxxxxxxx - US-xxxxxxxx UL 7PM Sun-Fri   188952  1525132818  2018-04-30T19:00:18.000-0500
xxxxxxxx - US-xxxxxxxx UL new path on FIS side Sat only 72  1525558529  2018-05-05T17:15:29.000-0500

Below is a good presentation of how I want the data to look, but the timestamp values mean nothing a human.

alt text

0 Karma

murakoshi
Engager

Scatter Chart is suitable for numerical expression on the X axis, not suitable for expressing rich time.
Therefore, I recommend using a Line Chart to try it.

  1. Insert Null under each row of your table.

Example:

index="dotchart" source="DotChart.csv" sourcetype="csv" 
| stats values(MFT_fromFileBytes) as Bytes by _time,MFT_job_name 
| eval {MFT_job_name}=Bytes
| append 
    [ search  index="dotchart" source="DotChart.csv"  sourcetype="csv" 
    | stats values(MFT_fromFileBytes) as Bytes by _time,MFT_job_name 
    | eval Bytes=null
        ] 
| sort 0 _time,MFT_job_name
| fields - Bytes,MFT_job_name

2. Set the graph as follows.
Graph: Line Chart
Setting: format> General> Null Values> select [Gaps]

rojyates
Explorer

That's a nice trick!

0 Karma

somesoni2
Revered Legend

The scatter chart works best with two data series (values to be shown on x-axis and y-axis should be numerical data that can be plotted). So scattered chart may not work for you. You can try column chart or bar chart.

0 Karma

lessthan80
Explorer

I stared at both column and bar charts for several hours, but could not make sense of that type of representation of individual file size values each time a specific job ran. To make either of those useful would require a separate chart for each job, when a single scatter chart provides an understandable view of all the jobs, quickly illustrating the anomolies for each job.

Let's go at it at another angle. Each square value in the scatter chart produces a pop-up as you hover over them. This pop-up includes specific variable values -- How do I include a good timestamp variable inside that pop-up?

0 Karma

somesoni2
Revered Legend

Yeah.. scatter chart would've been perfect if it would work for that type of data (time plus another data series). Have you looked at Line chart for output of following command?

index=main 
sourcetype="MFT_byte_size_data"
| chart values(MFT_fromFileBytes) over  _time by MFT_job_name limit=0

This should give you a line for each job, over time, for corresponding file size trend. Mouse hover would give time and job name with job name value as MFT_fromFileBytes. You can also try the Multiseries mode (available in Format->General) to see MFT_fromFileBytes value trend for jobs in separate charts.

0 Karma

lessthan80
Explorer

Since the line chart does provide a good time stamp and somewhat recognizable as an anomolie, I will present a single dashboard with both line and scatter charts of the same data. If something looks out of place, the charts are using the same color for each job, so it's relatively easy to move between them and determine which event needs attention.

Thanks for your help

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...