how to change table field value if it's equal to previous record field.

[wangkevin1029]

I have Splunk table output as below.

for every different id 1st occurrence, I want to keep id value here, but for all following records, I want to change the value to null.

time	id	value
40:56.1	00J7ER7SGO8PHCAU4O2CM2LAES0006CA	eree334
40:56.2	00J7ER7SGO8PHCAU4O2CM2LAES0006CA	face
41:27.6	00J7ER7SGO8PHCAU4O2CM2LAES0006CA	face
41:27.7	00J7ER7SGO8PHCAU4O2CM2LAES0006CA	dsafasdf
41:27.8	00J7ER7SGO8PHCAU4O2CM2LAES0006CA	earweraw
49:02.1	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	eqtdzgta
49:02.2	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	12341234
49:03.1	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	efgwerwe
49:03.2	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	dafdsaf
49:03.3	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	erwqerqw
50:08.0	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	daadsfad
50:08.7	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	qerqwer
50:08.7	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	ewrqwerqr
50:08.8	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	dfasdfsad
50:08.9	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	ewqrqewr

after change,  it should be like this,  anyone knows how to do this? thanks in advance.

time	id	value
40:56.1	00J7ER7SGO8PHCAU4O2CM2LAES0006CA	eree334
40:56.2		face
41:27.6		face
41:27.7		face
41:27.8		earweraw
49:02.1	00J7ER7SGO8PHCAU4O2CM2LAES0006CF	eqtdzgta
49:02.2		12341234
49:03.1		face
49:03.2		face
49:03.3		face
50:08.0		face
50:08.7		face
50:08.7		face
50:08.8		face
50:08.9		face

Kevin

[johnhuang]

| makeresults
| eval data_string="40:56.1,00J7ER7SGO8PHCAU4O2CM2LAES0006CA,eree334|40:56.2,00J7ER7SGO8PHCAU4O2CM2LAES0006CA,face|41:27.6,00J7ER7SGO8PHCAU4O2CM2LAES0006CA,face|41:27.7,00J7ER7SGO8PHCAU4O2CM2LAES0006CA,dsafasdf|41:27.8,00J7ER7SGO8PHCAU4O2CM2LAES0006CA,earweraw|49:02.1,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,eqtdzgta|49:02.2,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,12341234|49:03.1,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,efgwerwe|49:03.2,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,dafdsaf|49:03.3,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,erwqerqw|50:08.0,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,daadsfad|50:08.7,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,qerqwer|50:08.7,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,ewrqwerqr|50:08.8,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,dfasdfsad|50:08.9,00J7ER7SGO8PHCAU4O2CM2LAES0006CF,ewqrqewr"
| eval data_string=split(data_string, "|") | mvexpand data_string
| rex field=data_string "^(?<time>[^,]*)\,(?<id>[^,]*)\,(?<value>[^,]*)"
| eventstats min(time) AS first_time by id
| eval id_sort=id
| eval id=if(time=first_time, id,null())
| table time id_sort id value

[wangkevin1029]

thank you very much for your quick response.

I tried your code,  but I forgot something,  I still need id to sort the record., like what 2nd screenshot shows.

| sort id,  _time, asc,  

if  I filled non-1st occurrence with null, it will cause the record order messed up.

how can I still use the original ID to sort，then build an id copy column with blank for all non-1st occurrence,   in this way, order still works properly.

Kevin

[PickleRick]

You gotta have a way to distinguish between the first occurence and next ones. Streamstats is good for this

<your_search>
| streamstats count as temp_index by id
| eval id=if(temp_index=1,id,null())
| fields - temp_index

[wangkevin1029]

it works perfectly after I assign id to the other variable, which is used as  sort order field.

but the only issue is since I  use sort asc,  which cause only the last occurrence  not 1st occurrence displays.

how to  change your following code to only have 1st occurrence shown, when I use  sort id asc for my table?

| streamstats count as temp_index by id
| eval id=if(temp_index=1,id,null())
| fields - temp_index

[wangkevin1029]

when I use sort  id asc, other than I want 1st occurrence showing, instead of last occurance,,  I also need this temp_index showing reverse order... 

_time	sid	id	temp_index
03:06.3		000ANT58PG8RJD597Q8SI2LAES0000C2	3
03:06.5		000ANT58PG8RJD597Q8SI2LAES0000C2	2
03:06.8	000ANT58PG8RJD597Q8SI2LAES0000C2	000ANT58PG8RJD597Q8SI2LAES0000C2	1
11:38.5		000ANT58PG8RJD597Q8SI2LAES0000H8	10
11:38.9		000ANT58PG8RJD597Q8SI2LAES0000H8	9
11:39.0		000ANT58PG8RJD597Q8SI2LAES0000H8	8
11:40.8		000ANT58PG8RJD597Q8SI2LAES0000H8	7
11:40.9		000ANT58PG8RJD597Q8SI2LAES0000H8	6
11:41.2		000ANT58PG8RJD597Q8SI2LAES0000H8	5
11:41.3		000ANT58PG8RJD597Q8SI2LAES0000H8	4
11:41.5		000ANT58PG8RJD597Q8SI2LAES0000H8	3
11:41.6		000ANT58PG8RJD597Q8SI2LAES0000H8	2
11:41.9	000ANT58PG8RJD597Q8SI2LAES0000H8	000ANT58PG8RJD597Q8SI2LAES0000H8	1
13:14.9		000ANT58PG8RJD597Q8SI2LAES0000IB	16
13:15.1		000ANT58PG8RJD597Q8SI2LAES0000IB	15
13:16.9		000ANT58PG8RJD597Q8SI2LAES0000IB	14
13:17.2		000ANT58PG8RJD597Q8SI2LAES0000IB	13
13:17.4		000ANT58PG8RJD597Q8SI2LAES0000IB	12
13:17.7		000ANT58PG8RJD597Q8SI2LAES0000IB	11
13:18.2		000ANT58PG8RJD597Q8SI2LAES0000IB	10
13:18.6		000ANT58PG8RJD597Q8SI2LAES0000IB	9
13:19.0		000ANT58PG8RJD597Q8SI2LAES0000IB	8
13:19.3		000ANT58PG8RJD597Q8SI2LAES0000IB	7
13:19.6		000ANT58PG8RJD597Q8SI2LAES0000IB	6
13:19.7		000ANT58PG8RJD597Q8SI2LAES0000IB	5
13:20.0		000ANT58PG8RJD597Q8SI2LAES0000IB	4
13:20.4		000ANT58PG8RJD597Q8SI2LAES0000IB	3
13:20.7		000ANT58PG8RJD597Q8SI2LAES0000IB	2
13:20.9	000ANT58PG8RJD597Q8SI2LAES0000IB	000ANT58PG8RJD597Q8SI2LAES0000IB	1
14:01.5		000ANT58PG8RJD597Q8SI2LAES0000J1	2
14:03.1	000ANT58PG8RJD597Q8SI2LAES0000J1	000ANT58PG8RJD597Q8SI2LAES0000J1	1

[wangkevin1029]

when using sort asc, there is some confusion. but finally, I got it fully working,  than you very very much.

Kevin