Splunk Search

How to find difference in field total over time?

sohaibomar
Explorer

I have event data in below format:

Sep 15 2017 07:06:07    app=yahoo    dataconsumed=50
Sep 15 2017 08:16:07    app=skype    dataconsumed=150
Sep 14 2017 10:26:07    app=facebook   dataconsumed=10
Sep 14 2017 12:26:07    app=facebook    dataconsumed=5
Sep 13 2017 7:26:07    app=yahoo    dataconsumed=10
Sep 13 2017 9:26:07    app=skype    dataconsumed=50
Sep 12 2017 3:26:07    app=facebook   dataconsumed=80
Sep 12 2017 1:26:07    app=facebook    dataconsumed=0

How should I perform the following tasks:

  1. For any given time range, search and split the events in to two halves of "day" or "hours" i.e if "All Time" is selected as time range using Time Picker, I should be able to split above events into two halves by day(firsthalf=sep15-sep14 and secondhalf=sep 13-sep12) or by hour(firsthalf=48hour secondhalf=48hour).
  2. Then after splitting events into two halves, I must sum dataconsumed by app in both halves(events split by time) i.e

    time app total_dataconsumed

    firsthalf yahoo 50
    skype 150

    facebook 15

    secondhalf yahoo 10
    skype 50
    facebook 80

  3. Find difference between total_dataconsumed by app using firsthalf and secondhalf i.e firsthalf - secindhalf

    app difference

    yahoo 40
    skype 100
    facebook -65

I am still stuck on step 1, I don't seem to understand how should one split the search events into halves/spans and apply stats on both halves?

0 Karma

woodcock
Esteemed Legend

Try this:

Your Base Search Here
| addinfo
| eval time=if((_time <= (now()-(if(isnum(info_max_time), info_max_time, now()) - info_min_time)/2)), "firsthalf", "secondhalf")
| stats sum(dataconsumed) AS total_dataconsumed BY app time
0 Karma

jluo_splunk
Splunk Employee
Splunk Employee

There may be an easier way to do this, but you could try..

| eventstats min(_time) as startTime, range(_time) as timeElapsed
| eval halfwayMark=startTime+(timeElapsed/2)
| eval series=if(_time<halfwayMark, "firstHalf", "secondHalf")
| chart sum(usage) by app, series
| eval diff=firstHalf-secondHalf

sohaibomar
Explorer

Thanks @jluo [Splunk], but I still can not get a difference between both halves.
...|eval diff=firstHalf-secondHalf does not do anything.

0 Karma

jluo_splunk
Splunk Employee
Splunk Employee

Could you go into detail about what you're seeing? Without more information, I can't tweak the search.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...