comparing values of same field for duplicate

[ramyaashok]

Hi,

I am creating a dashboard like below, and want to check for duplicates in a particular column.

table is how dashboard will look initially, and later if the file value will be "adcdefghi", I want the status to be changed to data collected.  could anyone help me with this. 

app	file	status
one	abcdefghi	waiting for data
two	jklmnopq	waiting for data

[t_shreya]

Hi @ramyaashok 

I think you can use eventstats to calculate the number of occurrences of 'file' and then use eval to check if they are greater than 1 to set the status column as 'Data collected'. It would look something like this:

..| eventstats count as duplicates by file
| eval status = if(duplicates>1,"Data collected","waiting for data")
| table app, file, status

[ramyaashok]

Thanks Shreya, it worked.. 

had one more doubt as well. please have look if you could help.

job	time	file	status
one	10:50	abc	waiting
two	11:30	def	waiting
three	11:45	hij	Waiting

now, if there is a job four is going to have file name as "def", i dont want it to be added to next line. instead i want is like table 2 below. if file name is same, want the status to be changed to collected and also other values added to table horizontally. 

job	time	file	status	job2	time2
one	10:50	abc	waiting
two	11:30	def	collected	four	12:00
three	11:45	hij	waiting

[t_shreya]

Hi @ramyaashok ,

I'm not sure how to create a table exactly in the way that you want.

But it is possible to create a table in the following format:

file	status	name_of_job1	name_of_job2	name_of_job_3	name_of_job_4
abc	waiting	time_of_job1
def	collected		time_of_job2		time_of_job4
hij	waiting			time_of_job3

by using the following query:

your_results| eventstats count as duplicates by file
| eval status = if(duplicates>1,"Data collected","waiting for data")
| table file, status
| join file
    [| search your_results
    | chart values(_time) over file by job limit=10]

you can increase/decrease the limit parameter to put a threshold on the number of columns to be shown.