topic Re: how to select from table in Dashboards & Visualizations

how to select from table

fzfeng — Wed, 18 Apr 2018 01:11:29 GMT

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

Re: how to select from table

DalJeanis — Wed, 18 Apr 2018 03:33:37 GMT

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

Re: how to select from table

fzfeng — Wed, 18 Apr 2018 04:47:17 GMT

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

Re: how to select from table

mayurr98 — Wed, 18 Apr 2018 06:09:29 GMT

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!

Re: how to select from table

fzfeng — Wed, 18 Apr 2018 06:33:38 GMT

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

Re: how to select from table

mayurr98 — Wed, 18 Apr 2018 06:38:32 GMT

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

Re: how to select from table

fzfeng — Wed, 18 Apr 2018 06:49:48 GMT

count of mail not value

max count per hour per day

thank you

Re: how to select from table

mayurr98 — Tue, 29 Sep 2020 19:03:40 GMT

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.

Re: how to select from table

fzfeng — Wed, 18 Apr 2018 07:31:58 GMT

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

Re: how to select from table

fzfeng — Wed, 18 Apr 2018 07:38:26 GMT

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

Re: how to select from table

niketn — Wed, 18 Apr 2018 07:41:42 GMT

@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.

<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>

Re: how to select from table

fzfeng — Wed, 18 Apr 2018 08:50:37 GMT

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

Re: how to select from table

mayurr98 — Wed, 18 Apr 2018 15:46:36 GMT

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!

Re: how to select from table

niketn — Thu, 19 Apr 2018 03:37:57 GMT

@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.

Re: how to select from table

fzfeng — Thu, 19 Apr 2018 07:33:56 GMT

hello thank you so much it works

thank you very very much

Re: how to select from table

fzfeng — Thu, 19 Apr 2018 08:15:38 GMT

thank you It works

Re: how to select from table

niketn — Thu, 19 Apr 2018 08:38:51 GMT

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

Re: how to select from table

mayurr98 — Thu, 19 Apr 2018 09:46:04 GMT

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.

Re: how to select from table

fzfeng — Fri, 20 Apr 2018 11:38:05 GMT

thank you very much it works