topic Re: How could I make the new field less rows? in Splunk Search

How can I make the new field have less rows?

Jennifer — Tue, 15 Feb 2022 16:41:37 GMT

Here is the original log file:

Host availabilty Hashmap is {HKL20167984SIT_13_8225=true, HKL20167984SIT_7_82FB=true, HKL20167984SIT_2_82F6=true, HKL20167984SIT_16_8228=true, HKL20167984SIT_1_82F5=true, HKL20167984SIT_11_8223=true, HKL20167984SIT_14_8226=true, HKL20167984SIT_4_82F8=true, HKL20167984SIT_12_8224=false, HKL20167984SIT_3_82F7=true, HKL20167984SIT_15_8227=true, HKL20167984SIT_8_8220=true, HKL20167984SIT_9_8221=true, HKL20167984SIT_6_82FA=true, HKL20167984SIT_5_82F9=true, HKL20167984SIT_10_8222=true}

Here's my search command

index="hkcivr" source="/appvol/wlp/DIVR01HK-AS01/applogs/wrapup.log*" | rex max_match=0 "_(?<port status>\d{4}\=\w+)"

I hope to get the result like below:

Time
2022-02-15 07:02	8225=false, 8228=false, 8223=false, 8226=false, 8224=false, 8220=false, 8227=false, 8221=false, 8222=false, 8225=false, 8228=false, 8223=false, 8226=false, 8224=false, 8220=false, 8227=false, 8221=false 8222=false

Re: How could I make the new field less rows?

ITWhisperer — Tue, 15 Feb 2022 08:14:54 GMT

Is it that if any of them is false, you want them all to be false?

| eval status=coalesce(mvmap(port_status,if(mvindex(split(port_status,"="),1)="false","false",null())),"true") | eval port_status=mvmap(port_status,mvindex(split(port_status,"="),0)."=".status)

Or that you simply want to join them into a single string

| eval port_status=mvjoin(port_status,",")

Re: How could I make the new field less rows?

Jennifer — Tue, 15 Feb 2022 09:24:43 GMT

Thanks a lot for your help!

The data of original log doesn't match the column below!

I just need to join those values but I hope to make the two different results more distinct.

The solution is :

First, make the "false" text red and make the "true" text green

One is like that

Time	portstatus=true	portstatus=false
	8260=true,8296=true	8274=false,8277=false,8272=false,8279=false,8261=false,8263=false,8266=false,8275=false,8269=false,8276=false,8273=false,8264=false,8267=false,8297=false,8295=false,8262=false,8265=false,8268=false,8278=false,8298=false

The other is like this:

Time	portstatus=true	portstatus=false
	8260,8296	8274,8277,8272,8279,8261,8263,8266,8275,8269,8276,8273,8264,8267,8297,8295,8262,8265,8268,8278,8298

Re: How could I make the new field less rows?

ITWhisperer — Tue, 15 Feb 2022 09:33:11 GMT

| eval port_true=mvmap(port_status,if(mvindex(split(port_status,"="),1)="true",mvindex(split(port_status,"="),0),null())) | eval port_false=mvmap(port_status,if(mvindex(split(port_status,"="),1)="false",mvindex(split(port_status,"="),0),null()))

Re: How could I make the new field less rows?

Jennifer — Tue, 15 Feb 2022 09:52:44 GMT

Hi!

The 'mvmap' function is unsupported or undefined.

Re: How could I make the new field less rows?

ITWhisperer — Tue, 15 Feb 2022 12:49:30 GMT

You should probably upgrade your version of splunk.

Re: How could I make the new field less rows?

ITWhisperer — Tue, 15 Feb 2022 12:57:48 GMT

Here is a version without mvmap

| streamstats count as row | mvexpand port_status | eval port_true=if(mvindex(split(port_status,"="),1)="true",mvindex(split(port_status,"="),0),null()) | eval port_false=if(mvindex(split(port_status,"="),1)="false",mvindex(split(port_status,"="),0),null()) | stats values(port_true) as port_true values(port_false) as port_false values(_raw) as _raw by row | eval port_true=mvjoin(port_true,",") | eval port_false=mvjoin(port_false,",")

Re: How could I make the new field less rows?

somesoni2 — Tue, 15 Feb 2022 15:43:25 GMT

Try like this

index="hkcivr" source="/appvol/wlp/DIVR01HK-AS01/applogs/wrapup.log*" | rex max_match=0 "_(?<port_status_true>\d{4}\=true)" | rex max_match=0 "_(?<port_status_false>\d{4}\=false)"

Re: How could I make the new field less rows?

Jennifer — Wed, 16 Feb 2022 08:08:17 GMT

Hi, Thanks so much for your help!

But I don't know why there's some value missing from the table.

Here's the whole search command:

index="hkcivr" source="/appvol/wlp/DIVR01HK-AS01/applogs/wrapup.log*"
| rex max_match=0 "_(?<port_status>\d{4}\=\w+)"
| streamstats count as row
| mvexpand port_status
| eval port_true=if(mvindex(split(port_status,"="),1)="true",mvindex(split(port_status,"="),0),null())
| eval port_false=if(mvindex(split(port_status,"="),1)="false",mvindex(split(port_status,"="),0),null())
| stats values(port_true) as port_true values(port_false) as port_false values(_raw) as _raw by row
| eval port_true=mvjoin(port_true,",")
| eval port_false=mvjoin(port_false,",")
|table Time port_true port_false

Here's the table:

Re: How could I make the new field less rows?

ITWhisperer — Wed, 16 Feb 2022 08:39:23 GMT

Incorporating @somesoni2 solution, try it this way

index="hkcivr" source="/appvol/wlp/DIVR01HK-AS01/applogs/wrapup.log*" | rex max_match=0 "_(?<port_true>\d{4}\=true)" | rex max_match=0 "_(?<port_false>\d{4}\=false)" | eval port_true=mvjoin(port_true,",") | eval port_false=mvjoin(port_false,",") | table Time port_true port_false

If this still doesn't work, can you share the events with missing port numbers?

Re: How could I make the new field less rows?

Jennifer — Wed, 16 Feb 2022 09:34:01 GMT

Here's my updated search command:

index="hkcivr" source="/appvol/wlp/DIVR01HK-AS01/applogs/wrapup.log*"
| rex "Host availabilty Hashmap is \{(?<payload>[^}]*)\}"
| rex max_match=0 field=payload "_(?<portstatus>\d{4}\=\w+)"
| mvexpand portstatus
| rex max_match=0 "_(?<port_true>\d{4}\=true)"
| rex max_match=0 "_(?<port_false>\d{4}\=false)"
| eval port_true=mvjoin(port_true,",")
| eval port_false=mvjoin(port_false,",")
| table Time port_true port_false

Here's the result:

How could I only keep the port value in the column.

Time	Port_true	port_false
	8299, 8260,8296,8274,8277,8272,8279,8261,8263,8266,8275,8269,8276,8273,8264,8267

Re: How could I make the new field less rows?

ITWhisperer — Wed, 16 Feb 2022 09:59:37 GMT

index="hkcivr" source="/appvol/wlp/DIVR01HK-AS01/applogs/wrapup.log*" | rex "Host availabilty Hashmap is \{(?<payload>[^}]*)\}" | rex max_match=0 field=payload "_(?<port_true>\d{4}\=true)" | rex max_match=0 field=payload "_(?<port_false>\d{4}\=false)" | eval port_true=mvjoin(port_true,",") | eval port_false=mvjoin(port_false,",") | table _time port_true port_false

Re: How could I make the new field less rows?

Jennifer — Wed, 16 Feb 2022 10:07:11 GMT

Hi, IT Whisperer!

I don't think those commands work well!

Re: How could I make the new field less rows?

ITWhisperer — Wed, 16 Feb 2022 10:20:41 GMT

typo in availability!

index="hkcivr" source="/appvol/wlp/DIVR01HK-AS01/applogs/wrapup.log*" | rex "Host availability Hashmap is \{(?<payload>[^}]*)\}" | rex max_match=0 field=payload "_(?<port_true>\d{4}\=true)" | rex max_match=0 field=payload "_(?<port_false>\d{4}\=false)" | eval port_true=mvjoin(port_true,",") | eval port_false=mvjoin(port_false,",") | table _time port_true port_false

Re: How could I make the new field less rows?

yuanliu — Wed, 16 Feb 2022 10:51:46 GMT

To get rid of those =true and =false, you'd use

| rex max_match=0 field=payload "_(?<port_true>[\dA-F]{4})=true" | rex max_match=0 field=payload "_(?<port_false>[\dA-F]{4})=false" | eval port_true=mvjoin(port_true,", ") | eval port_false=mvjoin(port_false,", ") | table _time port_true port_false

The original sample data suggests that port is expressed in hexadecimal, so I'm including those:

_time	port_true	port_false
2022-02-16 02:46:17	8225, 82FB, 82F6, 8228, 82F5, 8223, 8226, 82F8, 82F7, 8227, 8220, 8221, 82FA, 82F9, 8222	8224

Re: How could I make the new field less rows?

ITWhisperer — Wed, 16 Feb 2022 11:10:51 GMT

Good spot @yuanliu on the placement of the closing bracket and hex in the ports. So the full search would be

index="hkcivr" source="/appvol/wlp/DIVR01HK-AS01/applogs/wrapup.log*" | rex "Host availability Hashmap is \{(?<payload>[^}]*)\}" | rex max_match=0 field=payload "_(?<port_true>[\dA-F]{4})\=true" | rex max_match=0 field=payload "_(?<port_false>[\dA-F]{4})\=false" | eval port_true=mvjoin(port_true,",") | eval port_false=mvjoin(port_false,",") | table _time port_true port_false