Hello,
Sorry for the language, I'm French. 😉
I'm executing this request with this lookup file:
index=xxxxxxxxxx u_ci_group_entity="xxxxxxxxx" cmd=check_interface_traffic
| fields svc, ds, u_ci_name, traffic_in_int, traffic_out_int, if_alias
| dedup svc, ds
| lookup collecte_orange_liste_interfaces_to_transportes u_ci_name if_alias OUTPUT valide group
| eval heures_charge = if (valide = "oui" , 0 , 1 )
| search heures_charge = 0
| stats sum(traffic_in_int) as "somme_in", sum(traffic_out_int) as "somme_out", latest(_time) as "_time" by group
| eval total_in_out_To=(somme_in+somme_out)/1024/1024/1024/1024, weeknumber=strftime(_time,"%V-%Y")
| table _time weeknumber group total_in_out_To
My question is:
How can I invert lines and columns in the table to get this:
The goal is to use outputlookup function to save results in CSV file as 1 line per week.
Thanks for helping.
Thanks a lot, it worked perfectly with the 1st answer.
@gduc, If your problem is resolved, please accept an answer to help future readers.
Like this:
index="xxxxxxxxxx" AND u_ci_group_entity="xxxxxxxxx" AND cmd="check_interface_traffic"
| fields svc, ds, u_ci_name, traffic_in_int, traffic_out_int, if_alias
| dedup svc, ds
| lookup collecte_orange_liste_interfaces_to_transportes u_ci_name if_alias OUTPUT valide group
| search valide = "oui"
| stats sum(traffic_in_int) AS"somme_in", sum(traffic_out_int) AS "somme_out", latest(_time) AS "_time" BY group
| eval total_in_out_To=(somme_in + somme_out)/1024/1024/1024/1024,
| fields - somme_in somme_out
| xyseries _time group total_in_out_To
| eval weeknumber=strftime(_time,"%V-%Y")
| table _time weeknumber *
Here is a solution that may work for you. Everything before eventstats
is kludge to get the data.
| makeresults
| eval data="2019-12-26 15:35:49,52-2019,ATM,0.178748;
2019-12-26 15:36:26,52-2019,BVPNIPNET,0.685878;
2019-12-26 15:32:45,52-2019,N2THD,0.046377;
2019-12-26 15:36:25,52-2019,PST,5.507981"
| makemv data delim=";" | mvexpand data | rex field=data "(\s|\n?)(?<data>.*)" | makemv data delim=","
| eval _time=strptime(mvindex(data,0),"%Y-%m-%d %H:%M:%S"),
weeknumber=mvindex(data,1),
group=mvindex(data,2),
total_in_out_To=mvindex(data,3)
| fields _time weeknumber group total_in_out_To
| eventstats first(_time) AS FirstTime | eval temp=FirstTime."~".weeknumber
| stats first(temp) AS temp first(weeknumber) AS weeknumber first(total_in_out_To) AS Val BY group
| xyseries temp group Val
| rex field=temp "^(?<FirstTime>[^\~]*)~(?<weeknumber>[^\e]*)"
| fields - temp | eval FirstTime=strftime(FirstTime,"%Y-%m-%d %H:%M:%S")
| table FirstTime weeknumber *
Here is the output:
FirstTime weeknumber ATM BVPNIPNET N2THD PST
2019-12-26 15:35:49 52-2019 0.178748 0.685878 0.046377 5.507981
The problem is with your stats
command and using by group
. Anytime you pass a field after the by clause, it will add multiple rows. If you remove the by clause, then it will show 1 row