Hi all.
I have a search like this:
index=data sourcetype=log* Type=INS finalStatus=done
| eventstats values(fecha) AS "MES",values(diasLaborablesMes) as "DIAS HABILES",values(OBJETIVO) as "OBJETIVO MES"
by AGENCIA
|eval ING_BRUTO=round(ING_BRUTO,0)
|eval REAL = 100000
| eval currentDay = strftime(now(),"%d") |eval OBJETIVO_DIARIO= floor(OBJETIVO/diasLaborablesMes)
|eval OBJETIVO_FECHA= floor(OBJETIVO_DIARIO*tonumber(currentDay))
|eval PORCENTAJE_FECHA=round((REAL/OBJETIVO)*100,2) |eval PROMEDIO_DIA=round((REAL/currentDay),2)
|eval PROYECCION=(REAL+(diasLaborablesMes-currentDay)*OBJETIVO_DIARIO)
|eval PORCENTAJE_PROYECCION=round(PROYECCION/OBJETIVO*100,2)
| eval BRUTO_PROYECTADO=round((ING_BRUTO/REAL)*PROYECCION,0)
| dedup AGENCIA
| table AGENCIA , MES,OBJETIVO, "DIAS HABILES", OBJETIVO_DIARIO, OBJETIVO_FECHA, REAL, PORCENTAJE_FECHA,
PROMEDIO_DIA, PROYECCION, PORCENTAJE_PROYECCIONING_BRUTO, BRUTO_PROYECTADO
|sort AGENCIA
| addcoltotals ING_BRUTO, BRUTO_PROYECTADO
| eval ING_BRUTO = tostring(ING_BRUTO, "commas")
| eval BRUTO_PROYECTADO = tostring(BRUTO_PROYECTADO, "commas")
And well, works fine.
This value of REAL
is only for testing purposes ( eval REAL = 100000
). I should be able to replace REAL by the number column of this search:
index=data sourcetype=log* Type=INS finalStatus=done | stats sum(Q_PRO) as REAL by AGENCIA
I tried:
index=data sourcetype=log* Type=INS finalStatus=done
| eventstats values(fecha) AS "MES",values(diasLaborablesMes) as "DIAS HABILES",values(OBJETIVO) as "OBJETIVO MES"
by AGENCIA
|eval ING_BRUTO=round(ING_BRUTO,0) | stats sum(Q_PRO) as REAL by AGENCIA
| eval currentDay = strftime(now(),"%d") | eval OBJETIVO_DIARIO= floor(OBJETIVO/diasLaborablesMes)
| eval OBJETIVO_FECHA= floor(OBJETIVO_DIARIO*tonumber(currentDay))
| eval PORCENTAJE_FECHA=round((REAL/OBJETIVO)*100,2)
| eval PROMEDIO_DIA=round((REAL/currentDay),2)
| eval PROYECCION=(REAL+(diasLaborablesMes-currentDay)*OBJETIVO_DIARIO)
| eval PORCENTAJE_PROYECCION=round(PROYECCION/OBJETIVO*100,2)
| eval BRUTO_PROYECTADO=round((ING_BRUTO/REAL)*PROYECCION,0)
| dedup AGENCIA |table AGENCIA,MES,OBJETIVO,"DIAS HABILES", OBJETIVO_DIARIO, OBJETIVO_FECHA, REAL,
PORCENTAJE_FECHA, PROMEDIO_DIA, PROYECCION, PORCENTAJE_PROYECCION,I NG_BRUTO, BRUTO_PROYECTADO
| sort AGENCIA | addcoltotals ING_BRUTO, BRUTO_PROYECTADO
| eval ING_BRUTO = tostring(ING_BRUTO, "commas")
| eval BRUTO_PROYECTADO = tostring(BRUTO_PROYECTADO, "commas")
But the resulting table is broken. Some idea? Thanks!
I can't really sort through all of this clearly - but:
When you use the command
| eval REAL = 100000
you are adding a field named REAL to every result that is present at that point in the pipeline. So if there are 4000 results, there will still be 4000 results, and each one will have a field REAL with the value of 100,000
When you use the command
| stats sum(Q_PRO) as REAL by AGENCIA
You are taking the events and summarizing them by AGENCIA. Let's say that there are still 4000 results, but only 1000 AGENCIAs within those results. After this command, there will be only 1000 results, and they will be formatted something like this:
AGENCIA REAL
alpha 1010
beta 1072
All other fields will be removed, as the stats command only retains the fields that are used. Even if there are 4000 AGENCIAs, the other fields will still be lost. This is why the rest of the search does not work.
Try this instead of stats:
| eventstats sum(Q_PRO) as REAL by AGENCIA
The eventstats command does not remove or summarize any results - it simply calculates the value of REAL and adds the field to every result.
I hope this helps!
Why not add REAL to the eventstats, like this?
index=data sourcetype=log* Type=INS finalStatus=done
| eventstats values(fecha) AS "MES",values(diasLaborablesMes) as "DIAS HABILES",values(OBJETIVO) as "OBJETIVO MES" sum(Q_PRO) as REAL
by AGENCIA
| eval ING_BRUTO=round(ING_BRUTO,0)
| eval currentDay = strftime(now(),"%d") | eval OBJETIVO_DIARIO= floor(OBJETIVO/diasLaborablesMes)
| eval OBJETIVO_FECHA= floor(OBJETIVO_DIARIO*tonumber(currentDay))
| eval PORCENTAJE_FECHA=round((REAL/OBJETIVO)*100,2)
| eval PROMEDIO_DIA=round((REAL/currentDay),2)
| eval PROYECCION=(REAL+(diasLaborablesMes-currentDay)*OBJETIVO_DIARIO)
| eval PORCENTAJE_PROYECCION=round(PROYECCION/OBJETIVO*100,2)
| eval BRUTO_PROYECTADO=round((ING_BRUTO/REAL)*PROYECCION,0)
| dedup AGENCIA |table AGENCIA,MES,OBJETIVO,"DIAS HABILES", OBJETIVO_DIARIO, OBJETIVO_FECHA, REAL,
PORCENTAJE_FECHA, PROMEDIO_DIA, PROYECCION, PORCENTAJE_PROYECCION,I NG_BRUTO, BRUTO_PROYECTADO
| sort AGENCIA | addcoltotals ING_BRUTO, BRUTO_PROYECTADO
| eval ING_BRUTO = tostring(ING_BRUTO, "commas")
| eval BRUTO_PROYECTADO = tostring(BRUTO_PROYECTADO, "commas")