Getting Data In

Sybase IQ log timestamp catching

FrancoiseMathy
New Member

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

Tags (2)
0 Karma

FrankVl
Ultra Champion

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.

0 Karma

FrancoiseMathy
New Member

the log is what they call the usage.log

0 Karma

FrankVl
Ultra Champion

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

0 Karma

adonio
Ultra Champion

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

0 Karma

sudosplunk
Motivator

Interesting! @adonio

0 Karma

FrancoiseMathy
New Member

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

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

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

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

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!

0 Karma

horsefez
SplunkTrust
SplunkTrust

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

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!