topic Re: How to invert lines and columns in Splunk Search

How to invert lines and columns

gduc — Thu, 26 Dec 2019 15:05:19 GMT

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.

Re: How to invert lines and columns

skoelpin — Thu, 26 Dec 2019 17:46:46 GMT

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

Re: How to invert lines and columns

jpolvino — Thu, 26 Dec 2019 19:16:12 GMT

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

Re: How to invert lines and columns

woodcock — Thu, 26 Dec 2019 19:49:13 GMT

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 *

Re: How to invert lines and columns

gduc — Fri, 27 Dec 2019 13:02:24 GMT

Thanks a lot, it worked perfectly with the 1st answer.

Re: How to invert lines and columns

richgalloway — Fri, 27 Dec 2019 14:57:41 GMT

@gduc, If your problem is resolved, please accept an answer to help future readers.