Splunk Search

How to invert lines and columns

gduc
Observer

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

alt text

My question is:
How can I invert lines and columns in the table to get this:
alt text

The goal is to use outputlookup function to save results in CSV file as 1 line per week.

Thanks for helping.

0 Karma

gduc
Observer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

woodcock
Esteemed Legend

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 *
0 Karma

jpolvino
Builder

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
0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...