Splunk Search

How to edit my search to replace an eval field with another field using stats sum?

Builder

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!

0 Karma

Legend

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!

0 Karma

Legend

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") 
0 Karma