topic Re: Tabular format in Splunk Search

Tabular format

jeganandrews — Fri, 12 Jul 2019 01:26:08 GMT

I want to display below logs in tabular format. Is there any possibility to display the below data in tabular format?

Start
2019-Jul-11 13:21:51#ServiceName#ResponseTime#Status
2019-Jul-11 13:21:51#Service1#100#Pass
2019-Jul-11 13:21:57#Service2#200#Pass
2019-Jul-11 13:22:09#Service3#300#Fail
End

Expected Tabular format

Re: Tabular format

jeganandrews — Fri, 12 Jul 2019 17:40:07 GMT

Input:

Time=2019-Jul-11 13:21:51#Service=A#ReponseTime=100#Status=Pass
Time=2019-Jul-11 13:21:57#Service=B#ReponseTime=200#Status=Pass
Time=2019-Jul-11 13:22:09#Service=C#ReponseTime=300#Status=Fail

Expected output

Time|Service|ResponseTime|Status
2019-Jul-11 13:21:51|A|100|Pass
2019-Jul-11 13:21:57|B|200|Pass
2019-Jul-11 13:22:09|C|300|Fail

Can any one help me to find search query?

Re: Tabular format

oscar84x — Fri, 12 Jul 2019 18:44:14 GMT

Try this:

index=foo sourcetype=bar
| rex field=_raw "^(?<time>[\d\w\s:\-]+)#(?<serviceName>\w+)#(?<responseTime>\w+)#(?<status>\w+)"
| table time, serviceName, responseTime, status

Re: Tabular format

jeganandrews — Sat, 13 Jul 2019 02:19:03 GMT

Thank you oscar84x

I am getting output in sorted with unique value which is not expected.
For Eg:

Input

Time=2019-Jul-11 13:21:51#Service=B#ReponseTime=200#Status=Pass
Time=2019-Jul-11 13:21:57#Service=C#ReponseTime=300#Status=Pass
Time=2019-Jul-11 13:22:09#Service=A#ReponseTime=100#Status=Pass

Expected output

Time|Service|ResponseTime|Status
2019-Jul-11 13:21:51|B|200|Pass
2019-Jul-11 13:21:57|C|300|Pass
2019-Jul-11 13:22:09|A|100|Pass

Actual Output

Time|Service|ResponseTime|Status
2019-Jul-11 13:21:51|A|100|Pass
2019-Jul-11 13:21:57|B|200|
2019-Jul-11 13:22:09|C|300|

Re: Tabular format

oscar84x — Mon, 15 Jul 2019 19:15:48 GMT

I'm not sure I understand your wording, but based on your example are you saying that the time isn't matching the event? And the status field isn't populating properly either?
Are these real data examples?

Re: Tabular format

woodcock — Tue, 16 Jul 2019 02:47:30 GMT

Like this:

| makeresults 
| eval raw="2019-Jul-11 13:21:51#ServiceName#ResponseTime#Status:::2019-Jul-11 13:21:51#Service1#100#Pass:::2019-Jul-11 13:21:57#Service2#200#Pass:::2019-Jul-11 13:22:09#Service3#300#Fail" 
| makemv delim=":::" raw 
| mvexpand raw 
| rename raw AS _raw 

| rename COMMENT AS "Everything above generates sample events; everything below is your solution"

| rex "^(?<_time>[^#]+)#(?<ServiceName>[^#]+)#(?<ResponseTime>[^#]+)#(?<Status>[^#]+)$"
| where isnum(ResponseTime)
| eval _time = strptime(_time, "%Y-%b-%d %H:%M:%S")
| table _time ServiceName ResponseTime Status

Re: Tabular format

jeganandrews — Wed, 17 Jul 2019 07:41:31 GMT

I have framed this sample data from real data. Instead of actual service name (in read data), i have given Alphabets (sample data here).

All the fields are sorted individually. I mean, Time is sorted, Services is sorted, Status is sorted, Response time also sorted.

But i want to display the data in tabular format as it is.

Input:
STARTS **
Time=2019-Jul-11 13:21:51#Service=B#ReponseTime=300#Status=Pass
Time=2019-Jul-11 13:21:57#Service=C#ReponseTime=200#Status=Pass
Time=2019-Jul-11 13:22:09#Service=A#ReponseTime=100#Status=Pass
ENDS **#2019-Jul-11 13:22:09#Pass

index="aaa" host="ccc"| transaction startswith="STARTS" endswith="ENDS"| search TimeStamp="2019-Jul-11 13:22:09"
| rex field=_raw "^(?[\d\w\s:-]+)#(?\w+)#(?\w+)#(?\w+)"
| table Time, Service, ResponseTime, Status

Expected output

Time|Service|ResponseTime|Status
2019-Jul-11 13:21:51|B|300|Pass
2019-Jul-11 13:21:57|C|200|Pass
2019-Jul-11 13:22:09|A|100|Pass

Actual Output

Time|Service|ResponseTime|Status
2019-Jul-11 13:21:51|A|100|Pass
2019-Jul-11 13:21:57|B|200|
2019-Jul-11 13:22:09|C|300|