How to split events into multiple rows in a table?

[dailv1808]

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,

[CryoHydra]

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

Will this help ?

[dailv1808]

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.

[FrankVl]

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.

[xpac]

This is the way to go! 🙂

[dailv1808]

Thank so much!

[kamlesh_vaghela · Accepted solution]

Hi

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

[dailv1808]

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

[kamlesh_vaghela]

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

[dailv1808]

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

[kamlesh_vaghela]

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

[dailv1808]

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.

[kamlesh_vaghela]

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?

[dailv1808]

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

time | ifName |ifIn | ifOut | ifSpeed | bandwidth_in | badwidth_out
2018-05-30 08:41:45 | Ethernet1/42 |123434342 |3123 | 100000000| 1212|789

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

How to calculate the formula in splunk?