topic How do stack multi search in a result in Splunk Search

How do stack multi search in a result

guilpink — Wed, 18 Jan 2017 15:30:23 GMT

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

Re: How do stack multi search in a result

lguinn2 — Wed, 18 Jan 2017 19:14:38 GMT

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.

Re: How do stack multi search in a result

DalJeanis — Thu, 19 Jan 2017 01:00:57 GMT

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

Re: How do stack multi search in a result

DalJeanis — Tue, 29 Sep 2020 12:28:33 GMT

....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")

Re: How do stack multi search in a result

guilpink — Thu, 19 Jan 2017 08:47:33 GMT

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!

Re: How do stack multi search in a result

lguinn2 — Thu, 19 Jan 2017 16:23:42 GMT

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.

Re: How do stack multi search in a result

guilpink — Fri, 20 Jan 2017 07:34:42 GMT

Ok, understood! Thank you for your help 😉

Re: How do stack multi search in a result

aaraneta_splunk — Fri, 20 Jan 2017 07:52:22 GMT

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!