topic Re: Sybase IQ log timestamp catching in Getting Data In

Sybase IQ log timestamp catching

FrancoiseMathy — Tue, 29 Sep 2020 21:03:03 GMT

Hello,

I have a very special log to index into Splunk. This is a Sybase IQ log with a special timestamp format.
Each line is a new log event.
At the begining of the log and at some lines after, you find a timestamp with the format %m%d %H%M%S.%3N
But between them, the time is shown as relative milliseconds

For example,
0523 095954.807,[,1000000001,sp_iq_mpx_init,16,iq
+2,],1000000001,sp_iq_mpx_init,16=
+79,P,1,[S]DUMMY
=,>,1,EXEC
......
0523 095954.807,[, xxxxxx
+83,>,1,CONNECT,1
......

At the first line the timestamp is with format %m%d %H%M%S.%3N
At the second line, the timestamp is thus the timestamp of first line +2 milliseconds (sign "+2")
At the third line, the timestamp is thus the timestamp of the second line +79 milleseconds (sign "+79")
Aty the fourth line, the timestamp is thus the timestamp of the thid line (sign "=")
....
This mechanism is valid till the new line with timestamp with format %m%d %H%M%S.%3N
And then it begins again

I do not see how I can catch the timestamp at each line at index time (preferably) or at search time.
Can you please advice?
Thanks

Re: Sybase IQ log timestamp catching

horsefez — Wed, 29 Aug 2018 13:19:31 GMT

If you find that developer, kick him in the nuts.

Re: Sybase IQ log timestamp catching

mstjohn_splunk — Wed, 29 Aug 2018 22:32:12 GMT

Hey @FrancoiseMathy , did you figure out an answer to your question? If so, would you mind posting it here so that others can learn from your experience? Thanks!

Re: Sybase IQ log timestamp catching

FrancoiseMathy — Thu, 30 Aug 2018 06:19:23 GMT

Hello, no I did not find the way to do so.
Still hoping some help.

Re: Sybase IQ log timestamp catching

mstjohn_splunk — Thu, 30 Aug 2018 15:51:08 GMT

@FrancoiseMathy ah, bummer. Maybe someone could help you in the Slack chat group? Check this out:

If you want to try to get some immediate help for your question, you should join the 5000+ Splunk users in our public Slack Community chat. People ask each other for immediate help on there daily. You can share your question/link to your post there to see if anyone can take a stab at it.

You first have to request access through https://splk.it/slack Fill out the form, and once you receive the approval email from our Community Manager (usually the approval process may take a couple days), you can access Slack.com and ask for help in the #general channel.

Re: Sybase IQ log timestamp catching

adonio — Tue, 29 Sep 2020 21:13:23 GMT

there are couple of ways to go about it
all pretty painful so, like @horsefez said, kick him / her before or after your pain
to do in indextime youll need to write a very unique datetimexml, read here:
http://docs.splunk.com/Documentation/SplunkCloud/latest/Data/Configuredatetimexml
to do so in search time, recognize / extract the milliseconds field:
... | rex "\+(<milliseconds>\d{1,3})"
now you can eval it and assign to _time
try something like that:
... | eval new_millisecond = tonumber(millisecond/1000) ...
and now for the time
| eval _time = if(some condition on your data,_time,_time+new_millisecond)

hope it helps

Re: Sybase IQ log timestamp catching

sudosplunk — Thu, 30 Aug 2018 19:35:52 GMT

Interesting! @adonio

Re: Sybase IQ log timestamp catching

FrankVl — Tue, 11 Sep 2018 14:49:45 GMT

What Sybase IQ log is this exactly? Is this the transaction log, or something completely different?

Because the transaction log can be passed through the dbtran utility to translate it into a much more readable (although to my experience still quite horrible) format.

Re: Sybase IQ log timestamp catching

FrancoiseMathy — Tue, 18 Sep 2018 11:28:07 GMT

the log is what they call the usage.log

Re: Sybase IQ log timestamp catching

FrankVl — Thu, 20 Sep 2018 08:30:05 GMT

Right, that sounds like something different from what I have been dealing with.