Dashboards & Visualizations

how to select from table

fzfeng
New Member

hello

I made a search like this
index=IP1 I timechart span=1h count and I set the date one week

so I got this table
time count
2018/4/11 09:00 8
2018/4/11 10:00 58
2018/4/11 11:00 6
2018/4/11 12:00 2
2018/4/11 13:00 8
……
2018/4/12 00:00 8
2018/4/12 01:00 10
2018/4/12 02:00 8
……

2018/4/13 09:00 8
2018/4/13 10:00 5

how can I get the max value of everyday and the table will be like this

2018/4/11 10:00 58
2018/4/12 01:00 10
2018/4/13 09:00 8

thanks every much
please help me one more time

Tags (1)
0 Karma
1 Solution

mayurr98
Super Champion

Hey , I suppose you meant that you want your original report i.e each hour count and max count in one report.

You can try something like this

index=IP1 | timechart span=1h count| eval time=strftime(_time,"%Y-%m-%d")| eventstats max(count) as max_count by time

let me know if this helps!

View solution in original post

0 Karma

niketn
Legend

@fzfeng, try the following run anywhere Simple XML dashboard code based on Splunk's _internal index ( you can change base search as per your need.)
PS: Time selection id Week to Date and I have only two days of data as I have re-installed Splunk couple of days back.

alt text

<dashboard>
  <label>Daily Max as Overlaid Field</label>
  <row>
    <panel>
      <title>Hourly Stats Per Day with Daily Max as Overlaid Field</title>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO"
| timechart span=1h last(date_mday) as date_mday count as HourlyCount
| filldown HourlyCount
| bin _time span=1h
| eventstats max(HourlyCount) as HourlyMaxPerDay by date_mday
| fields - date_mday</query>
          <earliest>@w0</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.text">Time</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">1</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">connect</option>
        <option name="charting.chart.overlayFields">HourlyMaxPerDay</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">none</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

fzfeng
New Member

hello thanks very much

but I also have one problem

base on the command you show me please see the table
_time HourlyCount Maxcount
2018/4/15 00:00 I 85 I 1084
2018/4/15 01:00 I 84 I 1084
2018/4/15 03:00 I 86 I 1084
2018/4/15 04:00 I 89 I 1084
2018/4/15 05:00 I 90 I 1084
2018/4/15 06:00 I 95 I 1084
2018/4/15 07:00 I 1084 I 1084
2018/4/15 08:00 I 85 I 1084
2018/4/15 09:00 I 85 I 1084
2018/4/15 10:00 I 85 I 1084
2018/4/15 11:00 I 85 I 1084

how can I just get only
2018/4/15 07:00 I 1084 I 1084

the other days are same

thank you

0 Karma

niketn
Legend

@fzeng you might have do give mock output as per what your requirement is.

If you want to have MaxCount only when HourlyCount is Maximum, then you can add the following as your final pipe:

 <YourCurrentSearchAsPerAboveQuery>
| eval MaxCount =if(HourlyCount=MaxCount ,MaxCount ,0)

If you want to only retain the HourlyCount when it is equal to MaxCount you can try the following search:

 <YourCurrentSearchAsPerAboveQuery>
  | where HourlyCount=MaxCount

If your need is neither of the above two scenario please add a sample output and we can suggest required query.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

fzfeng
New Member

thank you It works

0 Karma

niketn
Legend

@fzfeng, glad it worked. Please accept the Answer and up-vote the comment/s that helped.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

mayurr98
Super Champion

Hey , I suppose you meant that you want your original report i.e each hour count and max count in one report.

You can try something like this

index=IP1 | timechart span=1h count| eval time=strftime(_time,"%Y-%m-%d")| eventstats max(count) as max_count by time

let me know if this helps!

0 Karma

fzfeng
New Member

thank you very much it works

0 Karma

fzfeng
New Member

thank you

maybe my explain is poor

I want to make a statistic table base on mailog of Outlook

the information I want to get from mailog is value of mail per hour and I just want the only one max value per hour of a day

the table will be like
time max_count
2018-4-12 9:00 6
2018-4-13 20:00 7

I donot know if you get it

sorry

0 Karma

mayurr98
Super Champion

okay "value of mail" is in which field? is there any field for that ?
also you just want max_count or you want both ?

0 Karma

fzfeng
New Member

count of mail not value

max count per hour per day

thank you

0 Karma

mayurr98
Super Champion

i do not understand what you are trying to achieve.

index=IP1 I timechart span=1h count 

with this you get
_time |count
2018/4/11 09:00 | 8
2018/4/11 10:00 | 58
2018/4/12 11:00 | 6
2018/4/12 12:00 | 12

which means per hour what is count of mail. In other words, which basically is max value per hour.
Now out of this result set if you want which hour in a day has the max count then you would need to use

| eval time=strftime(_time,"%Y-%m-%d")| eventstats max(count) as max_count by time| fields- time

which will give you result something like this

_time |count | max_count
2018/4/11 09:00 | 8 | 58
2018/4/11 10:00 | 58 | 58
2018/4/12 11:00 | 6 | 12
2018/4/12 12:00 | 12 | 12

which is your requirement.

0 Karma

fzfeng
New Member

with this you get
_time |count
2018/4/11 09:00 | 8
2018/4/11 10:00 | 58
2018/4/12 11:00 | 6
2018/4/12 12:00 | 12
from this table
I want to get
2018/4/11 10:00 58
2018/4/12 12:00 12

0 Karma

mayurr98
Super Champion

Try this then

 index=IP1 | timechart span=1h count| eval time=strftime(_time,"%Y-%m-%d")| eventstats max(count) as max_count by time | where max_count=count | table _time max_count

let me know if this helps!

0 Karma

fzfeng
New Member

hello thank you so much it works

thank you very very much

0 Karma

mayurr98
Super Champion

If you deem a posted answer as valid and helpful to your solving of the issue, please accept said answer so that this question no longer appears open.

0 Karma

fzfeng
New Member

thank you so much

I want the max count of hour

like this
time I maxcount
2018-4-11 10:00 I 58
2018-4-12 11:00 I 12

like this
2018-4-11 09:00 50
2018-4-11 10:00 60
2018-4-11 11:00 70
2018-4-12 15:00 55
2018-4-12 16:00 56
I just want get the max count item

so how can I do just get
2018-4-11 11:00 70
2018-4-12 16:00 56

thanks for your

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

With your base search, like this...

index=IP1 I timechart span=1h count

Ths produces records in this format

| table _time count

If you want the one hour with the highest count for each day, then you can do this.

| bin _time as day span=1d
| eventstats max(count) as maxcount by day
| where count=maxcount
| sort 0 _time
0 Karma

fzfeng
New Member

thanks for helping me

but it doesnot work

index I timechart span=1h count I bin _time as day span=1d I eventsstats max(count)as maxcount by day I where count=maxcount I sort 0 _time

I tried but I cannot get the only one item of everyday

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...