How do stack multi search in a result

[guilpink]

Hello team,

I'm a splunk beginner and i'm looking for a solution.

My research is:

sourcetype="itsm_extract" Environement="Production" metier="Ass" groupe_proprietaire="MOE ASS FCT*" categorisation_produit_3="*ECH*" | eval _time=strptime(date_creation,"%d/%m/%Y") | eval RPTMois=strftime(_time, "%m") | eval Mois=strftime(_time, "%B") | stats count as "Total Incidents" by Mois,RPTMois | sort RPTMois | fields - RPTMois | append [search sourcetype="itsm_extract" Environement="Production" metier="Ass" groupe_proprietaire="PIL CC05 OPEN CIB IS RB N1" analyse_technique="Autre erreur batch" | fields + resume + date_creation | rex field=resume "(?:[A-Z0-9\-]*\_){4}[A-Z0-9]{2}(?<code_app>[A-Z0-9]{3})" | rex field=code_app "(?<car2>[A-Z0-9]{2})(?<car3>[A-Z0-9])" | eval code_app_final=if(isint(car3),car2,code_app) | join code_app_final [search sourcetype="ref_application" | fields + Code_Application,Affectation,Domaine | rename Code_Application as code_app_final] | eval _time=strptime(date_creation,"%d/%m/%Y") | eval RPTMois=strftime(_time, "%m") | eval Mois=strftime(_time, "%B") | search Domaine="*ECH*" | stats count(Domaine) as "Total Dysfonctionnements" by RPTMois,Mois | fields - RPTMois]

image?
Link: http://www.hostingpics.net/viewer.php?id=797097splunkanw.png

My question is: how should i do to merge my events by month in order to have only one occurence of each month? This way to be able to do a stacked histogram.

I have tried append, appendcols, multisearch....

Thank you for your help and sorry for this english.

Kévin

[aaraneta_splunk]

Hi @guilpink - Looks like you have a few possible solutions to your question. If one of them provided a working solution, please don't forget to click "Accept" below the best answer to resolve this post. If you still need help, please leave a comment. And of course, don't forget to up-vote any answer/comment that was helpful. Thanks!

[DalJeanis]

I started off with Iguinn's code, and then made some additional enhancements as follows:

1) Since you don't use the value of date_creation except at the month level, I altered the value to pull only the year and month, then used that value instead of RptMois for all the summary stats.
2) I didn't translate the month's long name (Mois) until the very end, because each RptMois has only one Mois, that was part of what was giving you your duplication problems.
3) When I calculate the Mois, I also calculate the An, since there may be multiple years in your data at some point.
4) I moved the test for Domaine like ECH inside the subsearch, which will kill the non-ECH records during the join itself, a tiny bit earlier in the process.

sourcetype="itsm_extract" Environement="Production" metier="Ass"
     groupe_proprietaire="MOE ASS FCT*" categorisation_produit_3="*ECH*"
 | eval date_creation=strptime(date_creation,"%d/%m/%Y")
 | eval date_creation=relative_time(date_creation, "@m")
 | stats count as "Total Incidents" by date_creation
 | append 
    [ search sourcetype="itsm_extract" Environement="Production" metier="Ass"
     groupe_proprietaire="PIL CC05 OPEN CIB IS RB N1" analyse_technique="Autre erreur batch"
     | fields + resume date_creation
     | rex field=resume "(?:[A-Z0-9\-]*\_){4}[A-Z0-9]{2}(?<code_app>[A-Z0-9]{3})"
     | rex field=code_app "(?<car2>[A-Z0-9]{2})(?<car3>[A-Z0-9])"
     | eval code_app_final=if(isint(car3),car2,code_app)
     | join code_app_final 
        [ search sourcetype="ref_application"
         | fields + Code_Application Domaine
         | dedup Code_Application Domaine
         | search Domaine="*ECH*"
         | rename Code_Application as code_app_final ]
     | eval date_creation=strptime(date_creation,"%d/%m/%Y")
     | eval date_creation=relative_time(date_creation, "@m")
     | stats count as "Total Dysfonctionnements" by date_creation]
 | stats sum(*) as * by date_creation
 | eval An = strftime(date_creation, "%Y")
 | eval Mois=strftime(date_creation, "%B")

[DalJeanis]

....it might be better to rename the field date_creation to month_creation for future maintainers....

 sourcetype="itsm_extract" Environement="Production" metier="Ass"
      groupe_proprietaire="MOE ASS FCT*" categorisation_produit_3="*ECH*"
  | eval month_creation=relative_time(strptime(date_creation,"%d/%m/%Y"), "@m")
  | stats count as "Total Incidents" by month_creation
  | append 
     [ search sourcetype="itsm_extract" Environement="Production" metier="Ass"
      groupe_proprietaire="PIL CC05 OPEN CIB IS RB N1" analyse_technique="Autre erreur batch"
      | fields + resume date_creation
      | rex field=resume "(?:[A-Z0-9\-]*\_){4}[A-Z0-9]{2}(?<code_app>[A-Z0-9]{3})"
      | rex field=code_app "(?<car2>[A-Z0-9]{2})(?<car3>[A-Z0-9])"
      | eval code_app_final=if(isint(car3),car2,code_app)
      | join code_app_final 
         [ search sourcetype="ref_application"
          | fields + Code_Application Domaine
          | dedup Code_Application Domaine
          | search Domaine="*ECH*"
          | rename Code_Application as code_app_final ]
      | eval month_creation=relative_time(strptime(date_creation,"%d/%m/%Y"), "@m")
      | stats count as "Total Dysfonctionnements" by month_creation]
  | stats sum(*) as * by month_creation
  | eval An = strftime(month_creation, "%Y")
  | eval Mois=strftime(month_creation, "%B")

[lguinn2 · Accepted solution]

Try this:

sourcetype="itsm_extract" Environement="Production" metier="Ass"
    groupe_proprietaire="MOE ASS FCT*" categorisation_produit_3="*ECH*"
| eval date_creation=strptime(date_creation,"%d/%m/%Y")
| eval RPTMois=strftime(date_creation, "%m")
| eval Mois=strftime(date_creation, "%B")
| stats count as "Total Incidents" by RPTMois Mois
| append [ search sourcetype="itsm_extract" Environement="Production" metier="Ass"
    groupe_proprietaire="PIL CC05 OPEN CIB IS RB N1" analyse_technique="Autre erreur batch"
    | fields + resume date_creation
    | rex field=resume "(?:[A-Z0-9\-]*\_){4}[A-Z0-9]{2}(?<code_app>[A-Z0-9]{3})"
    | rex field=code_app "(?<car2>[A-Z0-9]{2})(?<car3>[A-Z0-9])"
    | eval code_app_final=if(isint(car3),car2,code_app)
    | join code_app_final [ search sourcetype="ref_application"
        | fields + Code_Application Domaine
        | dedup Code_Application Domaine
        | rename Code_Application as code_app_final ]
    | eval date_creation=strptime(date_creation,"%d/%m/%Y")
    | eval RPTMois=strftime(date_creation, "%m")
    | eval Mois=strftime(date_creation, "%B")
    | search Domaine="*ECH*"
    | stats count as "Total Dysfonctionnements" by RPTMois Mois ]
| stats sum(*) as * by RPTMois Mois
| fields - RPTMois

To be honest, the line that is probably most useful to you is the next-to-last line in the search, which collapses the data. I did rework a few things in the search. To be honest, I wanted to see if I could factor out at least one of the subsearches, but it was not possible based on my understanding of the data. But there are a few tweaks. I would avoid using the _time field as it has the meaning of "the time of the event" in Splunk; you don't need an additional time field anyway.

[guilpink]

Hello team,

Really good, the last one works perfectly, I'm just wondering why the stats sum(*) as * by RPTMois Moisis needed.
Is sum(X) not only to "Returns the sum of the values of the field X"?
What I understand is sum() add both values for "January" of the col "Total Dysfonctionnements".

Thank you again!

[guilpink]

Ok, understood! Thank you for your help 😉

[lguinn2]

what

sum(*) as * 

means is "for each (numeric) field, sum its values and put the results back into the same field"

So if you have fields a and b, then field a will contain the sum of all values of a; field b will contain the sum of all values of b. In this case, you are also using by RPTMois Mois and there is only one value for each field for each month. So you could use first (*) as * by RPTMois Mois in this search and it would work equally well.