Splunk Search

How to display values in xyseries format?

rajgowd1
Communicator

How to display values in xyseries format? i have log like below

tcp        0      0 12b8-splfwd02.nam.nsro:7171 poc-citi-luna1.nam.ns:50326 ESTABLISHED
tcp        0      0 12b8-splfwd02.nam.nsro:7171 poc-citi-luna1.nam.ns:50326 TIME_WAIT
tcp        0      0 12b8-splfwd02.nam.nsro:7171 poc-citi-luna2.nam.ns:50326 TIME_WAIT
tcp        0      0 12b8-splfwd02.nam.nsro:7171 poc-citi-luna3.nam.ns:50326 ESTABLISHED
tcp        0      0 12b8-splfwd02.nam.nsro:7171 poc-citi-luna4.nam.ns:50326 SYNC_SENT
tcp        0      0 12b8-splfwd03.nam.nsro:7171 poc-citi-luna1.nam.ns:46756 TIME_WAIT
tcp        0      0 12b8-splfwd03.nam.nsro:7171 poc-citi-luna1.nam.ns:46756 SYNC_SENT
tcp        0      0 12b8-splfwd03.nam.nsro:7171 poc-citi-luna2.nam.ns:46756 TIME_WAIT
tcp        0      0 12b8-splfwd03.nam.nsro:7171 poc-citi-luna3.nam.ns:46756 TIME_WAIT
tcp        0      0 12b8-splfwd03.nam.nsro:7171 poc-citi-luna4.nam.ns:46756 ESTABLISHED
tcp        0      0 12b8-splfwd04.nam.nsro:7171 poc-citi-luna1.nam.ns:46756 TIME_WAIT
tcp        0      0 12b8-splfwd04.nam.nsro:7171 poc-citi-luna3.nam.ns:46756 SYNC_SENT
tcp        0      0 12b8-splfwd04.nam.nsro:7171 poc-citi-luna2.nam.ns:46756 SYNC_SENT
tcp        0      0 12b8-splfwd04.nam.nsro:7171 poc-citi-luna4.nam.ns:46756 ESTABLISHED
tcp        0      0 12b8-splfwd04.nam.nsro:7171 poc-citi-luna2.nam.ns:46756 ESTABLISHED

when i index, it is displaying only one status which is in last column but it is ignoring other values
below is the search command i am using

index=netstat | xyseries host HSM CONN_STATUS
0 Karma
1 Solution

sundareshr
Legend

Try this

index=netstat | rex "(?<host>12[^\s]+)\s(?<HSM>[^\s]+)\s(?<CONN_STATUS>.*)" | table host  HSM  CONN_STATUS

View solution in original post

sundareshr
Legend

Try this

index=netstat | rex "(?<host>12[^\s]+)\s(?<HSM>[^\s]+)\s(?<CONN_STATUS>.*)" | table host  HSM  CONN_STATUS

rajgowd1
Communicator

Thank you Sundaresh for your answer.
i have attached the format,i am looking for table something like this

there should be multiple values from app server1 to HSMLuna1 like ESTABLSIHED,SYNC_SENT

alt text

0 Karma

cmerriman
Super Champion

have you tried a |chart values(CONN_STATUS) by host HSM

0 Karma

rajgowd1
Communicator

HI,
i just tried and somehow it showing NULL and TIME_OUT in Column headers and with values below

0 Karma

cmerriman
Super Champion

Did you regex the logs to get the correct fields and values? | rex "(?<host>12[^\s]+)\s(?<HSM>[^\s]+)\s(?<CONN_STATUS>.*)" splits it out, thanks to sundareshr.

Do any of the logs have null values or a TIME_OUT value in place of CONN_STATUS/host/HSM?

0 Karma

rajgowd1
Communicator

Thank you chart is working.

0 Karma

rajgowd1
Communicator

is there way to count how many established and how many are in TIME_WAIT ?

0 Karma

cmerriman
Super Champion
|chart values(CONN_STATUS) count by host HSM

might work?

0 Karma

rajgowd1
Communicator

HI Merriman,
it is displaying the count but not individually.

suppose if i have appsrv1 to hsm2 CONN_STATUS are like 3 ESTABLISHED,1 TIME_WAIT and 1 SYNC_SENT
and it totaly displaying count as 5

0 Karma

cmerriman
Super Champion
..| rex "(?12[^\s]+)\s(?[^\s]+)\s(?.*)"|eventstats count by CONN_STATUS host HSM|eval countConnStatus=count+" - "+CONN_STATUS|chart values(countConnStatus) by host HSM

the eventstats should get you how many times the CONN_STATUS was seen at each host/HSM, then concatenate them together with the eval and values in the chart.

That is, if I understood what you're trying to get.

rajgowd1
Communicator

Perfect.Thank you Merriman.
i am new to splunk and not expert in writing regular expression.it would be great if you explain this expression so that everyone will learn.

rex "(?12[^\s]+)\s(?[^\s]+)\s(?.*)"
0 Karma

gokadroid
Motivator

12 - literally means 12
[^\s] capture everything except space delimiters
+ capture one or more, as many times as possible
and so on...

type your regex in
regex101.com
and you will see on top right corner it will explain you everything about your regex.

0 Karma

rajgowd1
Communicator

HI Merriman,
i am trying to extract columns 4 and 5 from below out put
but when i extract 4th column 2a8-splfwd02.nsm.nsro , it is not selecting servers with IP address and same happining for 5th column also

tcp 0 0 12a8-splfwd02.nsm.nsro:7171 poc-hsm-luna1.nam.ns:50326 ESTABLISHED
tcp 0 0 12a8-splfwd02.nsm.nsro:7171 poc-hsm-luna2.nam.ns:46756 ESTABLISHED

tcp 0 0 vr-fc4c-1259.nsm.nsro:35802 poc-hsm-luna1.nam:ibm-dt-2 ESTABLISHED
tcp 0 0 vr-fc4c-1259.nsm.nsro:50895 poc-hsm-luna1.nam:ibm-dt-2 ESTABLISHED
tcp 0 0 vr-fc4c-1259.nam.nsro:38448 poc-hsm-luna2.nam:ibm-dt-2 ESTABLISHED
tcp 0 0 vr-fc4c-1259.nsm.nsro:53541 poc-hsm-luna2.nam:ibm-dt-2 ESTABLISHED

tcp 0 0 122.96.150.153:35802 129.172.202.13:1792 ESTABLISHED
tcp 0 0 12.96.150.153:50895 139.172.202.13:1792 ESTABLISHED
tcp 0 0 13.96.150.153:38448 139.172.202.14:1792 ESTABLISHED
tcp 0 0 12.96.150.153:53541 149.172.202.14:1792 ESTABLISHED

tcp 0 0 128.72.199.71:39650 165.172.202.14:1792 ESTABLISHED
tcp 0 0 138.72.199.71:50974 189.172.202.13:1792 ESTABLISHED

tcp 0 0 sd-98dd-ada7.nam.nsro:39650 poc-hsm-luna2.nam:ibm-dt-2 ESTABLISHED
tcp 0 0 sd-98dd-ada7.nam.nsro:50974 poc-hsm-luna1.nam:ibm-dt-2 ESTABLISHED

0 Karma

rajgowd1
Communicator

its really great to talk to you.Thank you Merriman.

0 Karma

cmerriman
Super Champion

https://regex101.com/ is a great place to learn/practice regex

so what (?12[^\s]+)\s(?[^\s]+)\s(?.) is doing is naming the first group host and starting it when it sees '12' stopping at a white space (\s) the second group is HSM and then stops at the next white space. the last group is CONN_STATUS and collects everything until the end of the string (.)

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 ...