topic Re: How to display values in xyseries format? in Splunk Search

How to display values in xyseries format?

rajgowd1 — Thu, 06 Oct 2016 22:01:53 GMT

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

Re: How to display values in xyseries format?

sundareshr — Thu, 06 Oct 2016 22:27:38 GMT

Try this

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

Re: How to display values in xyseries format?

rajgowd1 — Thu, 06 Oct 2016 23:14:44 GMT

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

Re: How to display values in xyseries format?

cmerriman — Fri, 07 Oct 2016 00:32:54 GMT

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

Re: How to display values in xyseries format?

rajgowd1 — Fri, 07 Oct 2016 00:50:41 GMT

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

Re: How to display values in xyseries format?

cmerriman — Tue, 29 Sep 2020 11:18:46 GMT

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?

Re: How to display values in xyseries format?

rajgowd1 — Fri, 07 Oct 2016 01:19:55 GMT

Thank you chart is working.

Re: How to display values in xyseries format?

rajgowd1 — Fri, 07 Oct 2016 01:27:37 GMT

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

Re: How to display values in xyseries format?

cmerriman — Fri, 07 Oct 2016 15:09:30 GMT

|chart values(CONN_STATUS) count by host HSM

might work?

Re: How to display values in xyseries format?

rajgowd1 — Tue, 29 Sep 2020 11:19:52 GMT

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

Re: How to display values in xyseries format?

cmerriman — Fri, 07 Oct 2016 15:33:46 GMT

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

Re: How to display values in xyseries format?

rajgowd1 — Fri, 07 Oct 2016 16:01:16 GMT

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(?.*)"

Re: How to display values in xyseries format?

cmerriman — Fri, 07 Oct 2016 16:12:51 GMT

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

Re: How to display values in xyseries format?

rajgowd1 — Fri, 07 Oct 2016 16:19:05 GMT

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

Re: How to display values in xyseries format?

rajgowd1 — Fri, 07 Oct 2016 18:33:04 GMT

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

Re: How to display values in xyseries format?

gokadroid — Sat, 15 Oct 2016 06:06:29 GMT

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.