topic Change table representation in Splunk Search

Change table representation

anisgupt — Wed, 16 Jan 2019 10:41:40 GMT

I have a table as follows:

CN|Lev|ref1|ref2|ref3|ref4|ref5|ref6
cn1|1|1|2|3|4|||
cn2|2|||||5|6|

The representation required is:

CN|Lev|Ref|Count
cn1|1|ref1|1
cn1|1|ref2|2
cn1|1|ref3|3
cn1|1|ref4|4
cn2|2|ref5|5
cn2|2|ref6|6

Re: Change table representation

ashajambagi — Thu, 17 Jan 2019 09:46:34 GMT

Hi @anisgupt ,

Please try below query and let me know if it helps:

| makeresults 
| eval CN="cn1", Lev="1", ref1="1", ref2="2", ref3="3", ref4="4", ref5="", ref6="" 
| append 
    [| makeresults 
    | eval CN="cn2", Lev="2", ref1="", ref2="", ref3="", ref4="", ref5="5", ref6="6" ] 
| eval cr=mvzip(CN,Lev,"-") 
| fields - _time Lev CN 
| untable cr Ref Count 
| rex field=cr "(?P<CN>[^\,].+)-(?P<Lev>.+)" 
| table CN Lev Ref Count| where Count>0

Re: Change table representation

ashajambagi — Fri, 01 Feb 2019 06:11:04 GMT

@anisgupt Can you please accept the answer if it helped with your issue!