topic Re: Splunk Use multiple index in a same search in Getting Data In

Splunk Use multiple index in a same search

jaciro11 — Wed, 10 Jun 2020 19:04:13 GMT

Hello Slunk Team,

I have a question about appendcols.

When I try to use two index to compare some information I got the information in different orders not in the same to compare the values.

SEARCH

index="inlooxtt" StatusName!=Paused StatusName!=Completed StatusName!=Cancelled PerformedByName!=Donado* | eval Horas=(DurationMinutes/60) |stats dedup_splitvals=true sum(Horas) as Tiempo by ProjectName | eval Tiempo=round(Tiempo,2) |rename Tiempo as Tiempo | sort ProjectName | appendcols [search index="inlooxtasks" ProjectStatusName!=Paused ProjectStatusName!=Completed ProjectStatusName!=Cancelled ContactDisplayName!=Donado* ContactDisplayName!="null" | eval Horas2=(WorkAmount) | stats dedup_splitvals=true sum(Horas2) as Tiempo2 by ProjectName | rename ProjectName as Proyecto2 | eval Tiempo2=round(Tiempo2,2) | sort Proyecto2]

How I can do to solve my iproblem I will show what happen.

I want to have all my data in order to do a exatly data comparison

Thanks all!!

Re: Splunk Use multiple index in a same search

dmarling — Wed, 10 Jun 2020 19:34:10 GMT

I looked at your query and am not seeing a reason why you are using appencols. If your goal is to compare the work amount to the DurationMinutes you can do that without running the same query again in appendcols and ensure it's on the same project with the below query:

index="inlooxtt" StatusName!=Paused StatusName!=Completed StatusName!=Cancelled PerformedByName!=Donado* | eval Horas=(DurationMinutes/60), WorkAmount=if(ContactDisplayName!="null", WorkAmount, null()) | stats dedup_splitvals=true sum(Horas) as Tiempo sum(WorkAmount) as Tiempo2 by ProjectName | eval Tiempo=round(Tiempo,2) | rename Tiempo as Tiempo | sort ProjectName

I noticed in your append cols you only wanted to include WorkAmounts where the ContactDisplayName!="null". Adding an eval that accomplishes that allows you to do this in a single query.

Re: Splunk Use multiple index in a same search

greg_kollias — Wed, 10 Jun 2020 20:42:51 GMT

You could try something like this:

index="inlooxtt" StatusName!=Paused StatusName!=Completed StatusName!=Cancelled PerformedByName!=Donado* OR (index="inlooxtasks" ProjectStatusName!=Paused ProjectStatusName!=Completed ProjectStatusName!=Cancelled ContactDisplayName!=Donado* ContactDisplayName!="null") | eval Tiempo=(DurationMinutes/60), Tiempo2=WorkAmount | stats dedup_splitvals=true sum(Tiempo) as Tiempo, sum(Tiempo2) as Tiempo2 by ProjectName | eval Tiempo=round(Tiempo,2) | sort ProjectName

An OR clause will be much more efficient than appendcols, and you can aggregate all the data before transforming it in stats.

Hope this helps!

-Greg

Re: Splunk Use multiple index in a same search

jaciro11 — Wed, 10 Jun 2020 21:51:49 GMT

Hello Greg,

When I try to use ur search I take this:

Re: Splunk Use multiple index in a same search

jaciro11 — Wed, 10 Jun 2020 22:01:46 GMT

Hello,

Its not the same Query we have 2 different Index, Inlooxtt and Inlooxasks.

Thanks you

Re: Splunk Use multiple index in a same search

jaciro11 — Wed, 10 Jun 2020 23:49:39 GMT

I try with this because we need the position of the indexes.

(index="inlooxtt" StatusName!=Pausado StatusName!=Completed StatusName!=Cancelled PerformedByName!=Donado* ) OR (index="inlooxtasks" ProjectStatusName!="Paused" ProjectStatusName!="Completed" ProjectStatusName!="Cancelled" ContactDisplayName!=Donado* ContactDisplayName!="null")
| rename ProjectName as Proyectos
| eval Tiempo_Ejecutado=(DurationMinutes/60), Tiempo_Planeado=WorkAmount
| stats dedup_splitvals=true sum(Tiempo_Ejecutado) as Tiempo_Ejecutado, sum(Tiempo_Planeado) as Tiempo_Planeado by Proyectos
| eval Tiempo_Ejecutado=round(Tiempo_Ejecutado,2)
| sort Proyectos

But the solution is the OR thanks for all!