topic developpement in Splunk Search

developpement

anissabnk — Tue, 06 Feb 2024 16:11:48 GMT

Hello,

I have a question on a spl request.

I have those extracted fields about the entry data.

I used this spl request :

| union maxtime=500 timeout=500

[ search index="idx_arv_ach_appels_traites" AND (activite_cuid="N1 FULL" OR activite="FULL AC") AND (NOT activite_cuid!="N1 FULL" AND NOT activite!="FULL AC")| stats sum(appels_traites) as "Nbre appels" by date_month]

[ search index="idx_arv_ach_tracage" (equipe_travail_libelle="*MAROC N1 AC FULL")

| eval date=strftime(_time,"%Y-%m-%d")

| dedup date,code_alliance_conseiller_responsable,num_client

| chart count(theme_libelle) as "Nbre_de_tracagesD" by date_month ]

[search index="idx_arv_ach_cas_traces" source="*ac_at*" (cuid!="AUTOCPAD" AND cuid!="BTORCPAD" AND cuid!="COCOA01" AND cuid!="CRISTORC" AND cuid!="ECARE" AND cuid!="FACADE" AND cuid!="IODA" AND cuid!="MEFIN" AND cuid!="ND" AND cuid!="ORCIP" AND cuid!="ORDRAGEN" AND cuid!="PORTAIL USSD" AND cuid!="RECOU01" AND cuid!="SGZF0000" AND cuid!="SVI" AND cuid!="USAGER PURGE" AND cuid!="VAL01") (LibEDO="*MAROC N1 AC FULL"OR"*SENEGAL N1 AC FULL*") AND (code_resolution="474"OR"836"OR"2836"OR"2893"OR"3085"OR"3137"OR"3244"OR"4340"OR"4365"OR"4784"OR"5893"OR"5896"OR"5897"OR"5901"OR"5909"OR"5914"OR"6744"OR"7150"OR"8020"OR"8531"OR"8534"OR"8535"OR"8548"OR"8549"OR"8709"OR"8876"OR"8917"OR"8919"OR"8946"OR"8961"OR"8962"OR"8970"OR"8974"OR"8998"OR"8999"OR"9000"OR"9001"OR"9004"OR"9006"OR"9007"OR"9010"OR"9011"OR"9012"OR"9048"OR"9052"OR"9058"OR"9059"OR"9069"OR"9088"OR"9089"OR"9090"OR"9095"OR"9107"OR"9108"OR"9116"OR"9148"OR"9150"OR"9169"OR"9184"OR"9190"OR"9207"OR"9208"OR"9209"OR"9211"OR"9214"OR"9223"OR"9239"OR"9240"OR"9241"OR"9248"OR"9251"OR"9274"OR"92752"OR"9276"OR"9288"OR"9299"OR"9300"OR"9302"OR"9323"OR"9324"OR"9366"OR"9382"OR"9385"OR"9447"OR"9450"OR"9455"OR"9466"OR"9467"OR"9476"OR"9516"OR"9559"OR"9584"OR"9603"OR"9627"OR"9633"OR"9640"OR"9654"OR"9670"OR"9710"OR"9735"OR"9740"OR"9782"OR"9784"OR"9785"OR"9786"OR"9794"OR"9817"OR"9839"OR"9919"OR"9932"OR"10000"OR"10010"OR"10017"OR"10022"OR"10048"OR"10049"OR"10053"OR"10081"OR"10099"OR"10100"OR"10103"OR"10104"OR"10105"OR"10116"OR"10118"OR"10142"OR"10143"OR"10153"OR"10160"OR"10162"OR"10165"OR"10185"OR"10189"OR"10190"OR"10191"OR"10199"OR"10206"OR"10209"OR"10216"OR"10229"OR"10233"OR"10241"OR"10256"OR"10278"OR"10280"OR"10288"OR"10289"OR"10290"OR"10299"OR"10330"OR"10331"OR"10367"OR"10432"OR"10474"OR"10496"OR"10499"OR"10506"OR"10524"OR"10525"OR"10526"OR"10527"OR"10528"OR"10530"OR"10531"OR"10532"OR"10534"OR"10535"OR"10536"OR"10537"OR"10538"OR"10540"OR"10541"OR"10543"OR"10557"OR"10558"OR"10560"OR"10561"OR"10579"OR"10592"OR"10675"OR"10676"OR"10677"OR"10678"OR"10680"OR"10681"OR"10704"OR"10748"OR"10759"OR"10760"OR"10764"OR"10766"OR"10768"OR"10769"OR"10770"OR"10771"OR"10783"OR"10798"OR"10799"OR"10832"OR"10857"OR"10862"OR"10875"OR"10928"OR"10929"OR"10933"OR"10934"OR"10941"OR"10947"OR"10962"OR"10966"OR"10969"OR"10977"OR"10978"OR"11017"OR"11085"OR"11114"OR"11115"OR"11116"OR"11138"OR"11139"OR"11140"OR"11141"OR"11142"OR"11143"OR"11144"OR"11219"OR"11252"OR"11239"OR"11268"OR"11326"OR"11327"OR"11328"OR"11329"OR"11410"OR"11514"OR"11552"OR"11992"OR"12012"OR"12032"OR"12033"OR"12034"OR"12035"OR"12036"OR"12037"OR"12038"OR"12039"OR"12040"OR"12041"OR"12152") | chart sum(total) as "Nbre_de_tracagesB" by date_month ]

[search index="idx_arv_ach_enquetes_satisfaction" source="*maroc_base_1*" (conseiller_createur_equipe="*MAROC N1 AC FULL")

| chart count(appreciation) as "Nbre_de_retour_enquete" by date_month]

[search index="idx_arv_ach_enquetes_satisfaction" source="*maroc_base_1*" (conseiller_createur_equipe="*MAROC N1 AC FULL")

| eval nb5=case(appreciation="5", 5)

| eval nb123=case(appreciation>="1" and appreciation<="3", 3)

| eval nb1234=case(appreciation>="1" and appreciation<="4", 4)

| eval nbtotal=case(appreciation>="1" and appreciation<="5", 5)| stats count(nb5) as "Nbre_de_5", count(nb123) as "Nbre_de_123", count(nb1234) as "Nbre_de_1234", count(nbtotal) as "Nbre_total" by date_month

| eval pourcentage=round((Nbre_de_5/Nbre_total-(Nbre_de_123/Nbre_total))*100,2)." %"

| rename pourcentage as deltaSAT | table deltaSAT date_month] | stats values("Nbre appels") as "Nbre appels" values("Nbre_de_retour_enquete") as "Nbre_de_retour_enquete" values(Nbre_de_tracagesD) as Nbre_de_tracagesD values("Nbre_de_tracagesB") as "Nbre_de_tracagesB" values("Nbre_de_tracages_total") as "Nbre_de_tracages_total" values(deltaSAT) as deltaSAT by date_month

| eval pourcentage=round((Nbre_de_tracagesB/Nbre_de_tracages_total)*100, 2)." %"

| rename pourcentage as "Tx traçages bloquants"

| eval TxTracage=round((Nbre_de_tracagesD/'Nbre appels')*100,2)." %"

| rename TxTracage as "Tx traçages dédoublonnés"

| rename Nbre_de_tracagesD as "Nbre traçages dédoublonnés"

|eval date_month=case(date_month=="january", "01-Janvier", date_month=="february", "02-Février", date_month=="march", "03-Mars", date_month=="april", "04-Avril", date_month=="may", "05-Mai", date_month=="june", "06-Juin", date_month=="july", "07-Juillet", date_month=="august", "08-Août", date_month=="september", "09-Septembre", date_month=="october", "10-Octobre", date_month=="november", "11-Novembre", date_month=="december", "12-Décembre") | sort date_month | eval date_month=substr(date_month, 4)

| fields date_month, "Tx traçages bloquants", "Nbre appels", "Nbre traçages dédoublonnés", "Tx traçages dédoublonnés"

| transpose 15 header_field=date_month

I obtain this result but I have a problem :

I haven't worked on the date_year field and I don't get a table in a chronoligcal order.

Can you help me please ?

Re: developpement

gcusello — Wed, 07 Feb 2024 06:26:11 GMT

Hi @anissabnk,

let me understan: is the issue that the columns aren't in the correct order?

use date_year and date_month in the stats commands, instead only date_month, or add this command before using stats:

| eval month=date_year."-".date_month

and your stats columns will be ordered.

Ciao.

Giuseppe

Re: developpement

anissabnk — Wed, 07 Feb 2024 08:50:29 GMT

Ok for me, but How to have the result in the chronological order.

For now, I have this result, but not yet in a chronological order.

I have modified my spl request like this :

| union maxtime=500 timeout=500
[ search index="idx_arv_ach_appels_traites" AND (activite_cuid="N1 FULL" OR activite="FULL AC") AND (NOT activite_cuid!="N1 FULL" AND NOT activite!="FULL AC")|eval date_month_year=date_month." ".date_year| stats sum(appels_traites) as "Nbre appels" by date_month_year]
[ search index="idx_arv_ach_tracage" (equipe_travail_libelle="*MAROC N1 AC FULL")
| eval date=strftime(_time,"%Y-%m-%d") | eval date_month_year=date_month." ".date_year
| dedup date,code_alliance_conseiller_responsable,num_client
| chart count(theme_libelle) as "Nbre_de_tracagesD" by date_month_year]
[search index="idx_arv_ach_cas_traces" source="*ac_at*" (cuid!="AUTOCPAD" AND cuid!="BTORCPAD" AND cuid!="COCOA01" AND cuid!="CRISTORC" AND cuid!="ECARE" AND cuid!="FACADE" AND cuid!="IODA" AND cuid!="MEFIN" AND cuid!="ND" AND cuid!="ORCIP" AND cuid!="ORDRAGEN" AND cuid!="PORTAIL USSD" AND cuid!="RECOU01" AND cuid!="SGZF0000" AND cuid!="SVI" AND cuid!="USAGER PURGE" AND cuid!="VAL01") (LibEDO="*MAROC N1 AC FULL"OR"*SENEGAL N1 AC FULL*") AND (code_resolution="474"OR"836"OR"2836"OR"2893"OR"3085"OR"3137"OR"3244"OR"4340"OR"4365"OR"4784"OR"5893"OR"5896"OR"5897"OR"5901"OR"5909"OR"5914"OR"6744"OR"7150"OR"8020"OR"8531"OR"8534"OR"8535"OR"8548"OR"8549"OR"8709"OR"8876"OR"8917"OR"8919"OR"8946"OR"8961"OR"8962"OR"8970"OR"8974"OR"8998"OR"8999"OR"9000"OR"9001"OR"9004"OR"9006"OR"9007"OR"9010"OR"9011"OR"9012"OR"9048"OR"9052"OR"9058"OR"9059"OR"9069"OR"9088"OR"9089"OR"9090"OR"9095"OR"9107"OR"9108"OR"9116"OR"9148"OR"9150"OR"9169"OR"9184"OR"9190"OR"9207"OR"9208"OR"9209"OR"9211"OR"9214"OR"9223"OR"9239"OR"9240"OR"9241"OR"9248"OR"9251"OR"9274"OR"92752"OR"9276"OR"9288"OR"9299"OR"9300"OR"9302"OR"9323"OR"9324"OR"9366"OR"9382"OR"9385"OR"9447"OR"9450"OR"9455"OR"9466"OR"9467"OR"9476"OR"9516"OR"9559"OR"9584"OR"9603"OR"9627"OR"9633"OR"9640"OR"9654"OR"9670"OR"9710"OR"9735"OR"9740"OR"9782"OR"9784"OR"9785"OR"9786"OR"9794"OR"9817"OR"9839"OR"9919"OR"9932"OR"10000"OR"10010"OR"10017"OR"10022"OR"10048"OR"10049"OR"10053"OR"10081"OR"10099"OR"10100"OR"10103"OR"10104"OR"10105"OR"10116"OR"10118"OR"10142"OR"10143"OR"10153"OR"10160"OR"10162"OR"10165"OR"10185"OR"10189"OR"10190"OR"10191"OR"10199"OR"10206"OR"10209"OR"10216"OR"10229"OR"10233"OR"10241"OR"10256"OR"10278"OR"10280"OR"10288"OR"10289"OR"10290"OR"10299"OR"10330"OR"10331"OR"10367"OR"10432"OR"10474"OR"10496"OR"10499"OR"10506"OR"10524"OR"10525"OR"10526"OR"10527"OR"10528"OR"10530"OR"10531"OR"10532"OR"10534"OR"10535"OR"10536"OR"10537"OR"10538"OR"10540"OR"10541"OR"10543"OR"10557"OR"10558"OR"10560"OR"10561"OR"10579"OR"10592"OR"10675"OR"10676"OR"10677"OR"10678"OR"10680"OR"10681"OR"10704"OR"10748"OR"10759"OR"10760"OR"10764"OR"10766"OR"10768"OR"10769"OR"10770"OR"10771"OR"10783"OR"10798"OR"10799"OR"10832"OR"10857"OR"10862"OR"10875"OR"10928"OR"10929"OR"10933"OR"10934"OR"10941"OR"10947"OR"10962"OR"10966"OR"10969"OR"10977"OR"10978"OR"11017"OR"11085"OR"11114"OR"11115"OR"11116"OR"11138"OR"11139"OR"11140"OR"11141"OR"11142"OR"11143"OR"11144"OR"11219"OR"11252"OR"11239"OR"11268"OR"11326"OR"11327"OR"11328"OR"11329"OR"11410"OR"11514"OR"11552"OR"11992"OR"12012"OR"12032"OR"12033"OR"12034"OR"12035"OR"12036"OR"12037"OR"12038"OR"12039"OR"12040"OR"12041"OR"12152") |eval date_month_year=date_month." ".date_year | chart sum(total) as "Nbre_de_tracagesB" by date_month_year]
[search index="idx_arv_ach_cas_traces" source="*ac_at*" (cuid!="AUTOCPAD" AND cuid!="BTORCPAD" AND cuid!="COCOA01" AND cuid!="CRISTORC" AND cuid!="ECARE" AND cuid!="FACADE" AND cuid!="IODA" AND cuid!="MEFIN" AND cuid!="ND" AND cuid!="ORCIP" AND cuid!="ORDRAGEN" AND cuid!="PORTAIL USSD" AND cuid!="RECOU01" AND cuid!="SGZF0000" AND cuid!="SVI" AND cuid!="USAGER PURGE" AND cuid!="VAL01") (LibEDO="*MAROC N1 AC FULL"OR"*SENEGAL N1 AC FULL*") |eval date_month_year=date_month." ".date_year| chart sum(total) as "Nbre_de_tracages_total" by date_month_year]
[search index="idx_arv_ach_cas_traces" source="*ac_at*" (cuid!="AUTOCPAD" AND cuid!="BTORCPAD" AND cuid!="COCOA01" AND cuid!="CRISTORC" AND cuid!="ECARE" AND cuid!="FACADE" AND cuid!="IODA" AND cuid!="MEFIN" AND cuid!="ND" AND cuid!="ORCIP" AND cuid!="ORDRAGEN" AND cuid!="PORTAIL USSD" AND cuid!="RECOU01" AND cuid!="SGZF0000" AND cuid!="SVI" AND cuid!="USAGER PURGE" AND cuid!="VAL01") (LibEDO="*MAROC N1 AC FULL"OR"*SENEGAL N1 AC FULL*")| chart sum(total) as "Nbre_de_tracages_total" by date_month_year]
[search index="idx_arv_ach_enquetes_satisfaction" source="*maroc_base_1*" (conseiller_createur_equipe="*MAROC N1 AC FULL") |eval date_month_year=date_month." ".date_year
| chart count(appreciation) as "Nbre_de_retour_enquete" by date_month_year]
[search index="idx_arv_ach_enquetes_satisfaction" source="*maroc_base_1*" (conseiller_createur_equipe="*MAROC N1 AC FULL")
|eval date_month_year=date_month." ".date_year | eval nb5=case(appreciation="5", 5)
| eval nb123=case(appreciation>="1" and appreciation<="3", 3)
| eval nb1234=case(appreciation>="1" and appreciation<="4", 4)
| eval nbtotal=case(appreciation>="1" and appreciation<="5", 5)| stats count(nb5) as "Nbre_de_5", count(nb123) as "Nbre_de_123", count(nb1234) as "Nbre_de_1234", count(nbtotal) as "Nbre_total" by date_month _year
| eval pourcentage=round((Nbre_de_5/Nbre_total-(Nbre_de_123/Nbre_total))*100,2)." %"
| rename pourcentage as deltaSAT | table deltaSAT date_month] | stats values("Nbre appels") as "Nbre appels" values("Nbre_de_retour_enquete") as "Nbre_de_retour_enquete" values(Nbre_de_tracagesD) as Nbre_de_tracagesD values("Nbre_de_tracagesB") as "Nbre_de_tracagesB" values("Nbre_de_tracages_total") as "Nbre_de_tracages_total" values(deltaSAT) as deltaSAT by date_month_year
| eval pourcentage=round((Nbre_de_tracagesB/Nbre_de_tracages_total)*100, 2)." %"
| rename pourcentage as "Tx traçages bloquants"
| eval TxTracage=round((Nbre_de_tracagesD/'Nbre appels')*100,2)." %"
| rename TxTracage as "Tx traçages dédoublonnés"
| rename Nbre_de_tracagesD as "Nbre traçages dédoublonnés" |sort date_month_year

I tried to sort but it doesn't work.

Thank you

Re: developpement

gcusello — Wed, 07 Feb 2024 09:04:39 GMT

Hi @anissabnk,

don't use date_month_year, but date_year_month.,

then add month_number

| eval month=strftime(_time,"%m") | eval date_year_month=date_year." - ".month." - ".date_month

in this way, you can sort the results by date_year_month.

then at the end of the search, you can remove the month number in this way:

| eval date_year_month=substr(date_year_month,1,7).substr(date_year_month,12,15)

eventually check if the substrings are correct!

Ciao.