topic How to display ONLY first row for each value in table in Splunk Search

How to display ONLY first row for each value in table

mprreddy51 — Wed, 03 Aug 2016 18:09:39 GMT

Hi,

I have a requirement to use display first row for every ACCNO.any Ideas?

query:
I used some transaction command |table ACCNO,VALUE

ACCNO VALUE
1 100
1 110
2 125
2 134
2 143
3 156
3 123
4 124
5 567
5 129
5 345

EXPECTED OUTPUT:

ACCNO VALUE
1 100
2 125
3 156
4 124
5 567

Re: How to display ONLY first row for each value in table

somesoni2 — Wed, 03 Aug 2016 18:12:10 GMT

Try any of these

 some transaction command |table ACCNO,VALUE | dedup ACCNO

 some transaction command |table ACCNO,VALUE | stats first(VALUE) as VALUE by ACCNO

Re: How to display ONLY first row for each value in table

mprreddy51 — Wed, 03 Aug 2016 20:46:47 GMT

@somesoni2

Hi Somesh,

can we use first(duration) or last(duration) in timechart command?I want to pick only first value in duration

like sampledata:

_time duration
2016-08-02 12:00:00 11.848000
12.031000
query:
transaction command| timechart span=1m list(duration) as duration

thanks.

Re: How to display ONLY first row for each value in table

somesoni2 — Wed, 03 Aug 2016 21:06:11 GMT

You're using list function with timechart, so you'd be getting a multivalued field duration for minutes where there is multiple duration. This list will be sorted by the time. So if you want to pick up only a single value, first or last, from this multivalued list, try like this for getting first/oldest duration for that min

transaction command| timechart span=1m list(duration) as duration | eval duration=mvindex(duration,0)

Replace| eval duration=mvindex(duration,0) with | eval duration=mvindex(duration,-1) for last/latest duration for that min