topic Re: How to split events into multiple rows in a table? in Splunk Search

How to split events into multiple rows in a table?

dailv1808 — Tue, 29 May 2018 08:20:27 GMT

I have the following search result which has multiple values in a cell:

I would like to split table to raws. look like:

Time                                             |               ifName           |                    ifIn                     |                    ifOut                   |            ifSpeed
2018-05-29 15:0514                  |               mgmt0            |                    2725909466    |               445786495              |     1000000000
2018-05-29 15:0514                  |               Vlan1               |                    2739931731    |               807226632              |     1000000000
2018-05-29 15:0514                  |               Vlan30             |                    925889480      |               694417752              |     1000000000
2018-05-29 15:0514                  |               Vlan100           |                     925889308     |               694418432              |     1000000000

v.v

Thanks,

Re: How to split events into multiple rows in a table?

kamlesh_vaghela — Tue, 29 May 2018 09:29:26 GMT

Can you please try following search?

YOUR_SEARCH
| eval tempField= mvzip(mvzip(mvzip(ifName,ifIn),ifOut),ifSpeed) 
| stats count by _time tempField
| eval ifName = mvindex(split(tempField,","),0), ifIn= mvindex(split(tempField,","),1), ifOut=mvindex(split(tempField,","),2), ifSpeed=mvindex(split(tempField,","),2)  
| table _time ifName ifIn ifOut ifSpeed

Thanks

Re: How to split events into multiple rows in a table?

dailv1808 — Tue, 29 May 2018 09:45:40 GMT

why are you so smart. yeah, it's work. But it doesn't arranged in the correct order of the interface list. so What need i to do to sort by interface name as order in log.

https://imgur.com/a/QN89Jqg

Re: How to split events into multiple rows in a table?

FrankVl — Tue, 29 May 2018 09:45:48 GMT

As mentioned in my comment in the related question: https://answers.splunk.com/comments/662403/view.html

Manually defining a sourcetype as follows in props.conf works like a charm to split your raw data:

[snmptest]
DATETIME_CONFIG=CURRENT
SHOULD_LINEMERGE=false
NO_BINARY_CHECK=true
LINE_BREAKER=(\s+)IF-MIB::ifName

Which is much cleaner and much more reliable than messing around with multivalued fields.

Re: How to split events into multiple rows in a table?

kamlesh_vaghela — Tue, 29 May 2018 10:00:02 GMT

Hi @dailv1808,
Just use sort command to sort results.

http://docs.splunk.com/Documentation/Splunk/7.1.0/SearchReference/Sort

if you want to sort on time as well as Interface name then put sort command before table.
like,

 YOUR_SEARCH
 | eval tempField= mvzip(mvzip(mvzip(ifName,ifIn),ifOut),ifSpeed) 
 | stats count by _time tempField
 | eval ifName = mvindex(split(tempField,","),0), ifIn= mvindex(split(tempField,","),1), ifOut=mvindex(split(tempField,","),2), ifSpeed=mvindex(split(tempField,","),2)  
| sort _time ifName 
| table _time ifName ifIn ifOut ifSpeed

If you want to sort by only interface name then use this.

YOUR_SEARCH
     | eval tempField= mvzip(mvzip(mvzip(ifName,ifIn),ifOut),ifSpeed) 
     | stats count by _time tempField
     | eval ifName = mvindex(split(tempField,","),0), ifIn= mvindex(split(tempField,","),1), ifOut=mvindex(split(tempField,","),2), ifSpeed=mvindex(split(tempField,","),2)  
    | sort ifName 
    | table _time ifName ifIn ifOut ifSpeed

Thanks

Re: How to split events into multiple rows in a table?

xpac — Tue, 29 May 2018 10:01:01 GMT

This is the way to go! 🙂

Re: How to split events into multiple rows in a table?

dailv1808 — Tue, 29 May 2018 10:18:09 GMT

oh, y understood wrong my mind because my english is not good. so i mean, listed by order appears interfaces in the log, not sort by interface name. example:
I want to be like:
Ethernet1/1
Ethernet1/2
Ethernet1/3
Ethernet1/4
Ethernet1/5
Ethernet1/6
Ethernet1/7
Ethernet1/8
Ethernet1/9
Ethernet1/10
Ethernet1/11
Ethernet1/12

Not like this.
Ethernet1/1
Ethernet1/10
Ethernet1/11
Ethernet1/12
..........
Ethernet1/19
Ethernet1/2
Ethernet1/21
Ethernet1/22
....vv

Re: How to split events into multiple rows in a table?

kamlesh_vaghela — Tue, 29 May 2018 11:27:51 GMT

Hi @dailv1808,

Can you please try this search?

YOUR_SEARCH
| eval tempField= mvzip(mvzip(mvzip(ifName,ifIn),ifOut),ifSpeed) 
| stats count by _time tempField
| eval ifName = mvindex(split(tempField,","),0), ifIn= mvindex(split(tempField,","),1), ifOut=mvindex(split(tempField,","),2), ifSpeed=mvindex(split(tempField,","),2)  
|  eval sort_field1 =mvindex(split(ifName,"/"),0), sort_field2 =mvindex(split(ifName,"/"),1)
|  sort str(sort_field1) +num(sort_field2)
| table _time ifName ifIn ifOut ifSpeed

Thanks

Re: How to split events into multiple rows in a table?

dailv1808 — Tue, 29 May 2018 11:35:55 GMT

Thank @kamlesh_vaghela,
i tried, now result like this:
Ethernet1/1
Ethernet1/1
Ethernet1/1
Ethernet1/1
..........
Ok, But this problem is not really too serious.
Now i want to calculate bandwidth of each interface use ifName ifIn ifOut ifSpeed fields. Do you know how to do it? i get snmp log with interval = 1m.

Re: How to split events into multiple rows in a table?

dailv1808 — Tue, 29 May 2018 11:36:20 GMT

Thank so much!

Re: How to split events into multiple rows in a table?

kamlesh_vaghela — Tue, 29 May 2018 12:34:11 GMT

Ohh,

just replace | sort str(sort_field1) +num(sort_field2) with | sort _time str(sort_field1) +num(sort_field2)

And how you want to show bandwith details of each interface?

Re: How to split events into multiple rows in a table?

dailv1808 — Tue, 29 Sep 2020 19:43:23 GMT

Thank you so much, i replaced and it work fine.
And I want to add new collunm for bandwidth of each interface. like this:

I refer to this link,
https://www.cisco.com/c/en/us/support/docs/ip/simple-network-management-protocol-snmp/8141-calculate-bandwidth-snmp.html

How to calculate the formula in splunk?

Re: How to split events into multiple rows in a table?

CryoHydra — Wed, 30 May 2018 04:09:18 GMT

`your search`| table _time ifName ifIn ifOut ifSpeed | mvexpand ifName

Will this help ?

Re: How to split events into multiple rows in a table?

dailv1808 — Wed, 30 May 2018 06:32:53 GMT

it just split ifName field, not for ifName ifIn ifOut ifSpeed fields. I use the way of @kamlesh_vaghela and the problem is solved. Anyway, thank your help.