Absent Member.
Absent Member.
837 views

Question on regex for csv log file that has time and date separation to device receipt time

Jump to solution

Hi,

I'm working on a csv log file that has the time and date separated by a comma.  I am trying to map this to deviceReceiptTime and having some difficulty.  Can anyone help.

csv file

"05-27-2010","12:00"

token.count=2

token[0].name=date
token[0].type=TimeStamp

token[1].name=time
token[1].type=Time
token[1].format=HH\:mm

event.deviceReceiptTime=__concatenate(date," ",time)

Is there another way in tokenizing the date and time and omit the "," ?   Thanks in advance for any assistance.

Labels (2)
0 Likes
1 Solution

Accepted Solutions
Absent Member.
Absent Member.
I've just tried this in  the regex FlexConnector to test the output of the timestamp function,  but it does work fine:


# FlexAgent Regex Configuration File
do.unparsed.events=true


regex=([^,]+),(\\d\\d\\\:\\d\\d)


token.count=2


token[0].name=testdate
token[0].type=Date
token[0].format=dd-MM-yyyy


token[1].name=testtime
token[1].type=Time
token[1].format=HH\:mm



#submessage.messageid.token=
#submessage.token=


event.deviceReceiptTime=__createTimeStamp(testdate,testtime)



#l10n.filename.prefix=

As you can see in the screenshot, there's a long number as the output of the function - mid-right under Value. That's the epoch time, so for that to be returned, it's evidentally processing the inputs as time/date fields, and outputting a timestamp successfully.

Suggestions

  • There's a typo in your last date, as in "027-" - which would not match the format condition of dd-
  • The ""'s - are you sure you've set these as text markers in the CSV FlexConnector settings? I didn't see this in the header of your quoted file. If not, then again, the format doesn't match. You could simply add those to the format ( as "dd-MM-yyyy" ) - unsure if \" required
  • If you still can't get it working, then if you really want to, you can open the regex wizard with arcsight regex in the connector directory and copy/paste my above into the lower window to see it working. Type a sample log line in the top text panel, and a regex expression underneath (eg. (\d\d-\d\d-\d\d\d\d),(\d\d\:\d\d) ) to capture the date & time tokens. Then watch the output for event.deviceReceiptTime on the right, to see if an epoch time is generated, or is still blank if not working. (This is handy for testing out more complex functions without having to re-run the connector after each modification).

View solution in original post

0 Likes
5 Replies
Absent Member.
Absent Member.

Hi PTse,


__concatenate won't work - for a start, they're the wrong type.

There's a number of useful timestamp processing functions in the Flex guide, at the end. Probably the best for you is:

__createTimeStamp

The first parameter is a Date and the
second parameter is a Time. They are
combined into a single TimeStamp and
returned. Everything is assumed to be in
local time.

__createTimeStamp(date,time)

There are 3-4 ways of doing it using the functions listed, such as tokenising as strings, concatenating those, and then using Regex to get the timestamp out. But I'd suggest using the simple function above, unless you really enjoy functions and regex 🙂

Damian

0 Likes
Absent Member.
Absent Member.

Damian,

Thanks for the reply.  I just try the createTimeStamp function and it did not return any value.  Can you let me know if my syntax is correct.

Log

"027-05-2010","16:07"

config file

token.count=2

token[0].name=testdate
token[0].type=Date
token[0].format=dd-MM-yyyy

token[1].name=testtime
token[1].type=Time
token[1].format=HH\:mm

event.deviceReceiptTime=__createTimeStamp(testdate,testtime)

Thanks.

0 Likes
Absent Member.
Absent Member.
I've just tried this in  the regex FlexConnector to test the output of the timestamp function,  but it does work fine:


# FlexAgent Regex Configuration File
do.unparsed.events=true


regex=([^,]+),(\\d\\d\\\:\\d\\d)


token.count=2


token[0].name=testdate
token[0].type=Date
token[0].format=dd-MM-yyyy


token[1].name=testtime
token[1].type=Time
token[1].format=HH\:mm



#submessage.messageid.token=
#submessage.token=


event.deviceReceiptTime=__createTimeStamp(testdate,testtime)



#l10n.filename.prefix=

As you can see in the screenshot, there's a long number as the output of the function - mid-right under Value. That's the epoch time, so for that to be returned, it's evidentally processing the inputs as time/date fields, and outputting a timestamp successfully.

Suggestions

  • There's a typo in your last date, as in "027-" - which would not match the format condition of dd-
  • The ""'s - are you sure you've set these as text markers in the CSV FlexConnector settings? I didn't see this in the header of your quoted file. If not, then again, the format doesn't match. You could simply add those to the format ( as "dd-MM-yyyy" ) - unsure if \" required
  • If you still can't get it working, then if you really want to, you can open the regex wizard with arcsight regex in the connector directory and copy/paste my above into the lower window to see it working. Type a sample log line in the top text panel, and a regex expression underneath (eg. (\d\d-\d\d-\d\d\d\d),(\d\d\:\d\d) ) to capture the date & time tokens. Then watch the output for event.deviceReceiptTime on the right, to see if an epoch time is generated, or is still blank if not working. (This is handy for testing out more complex functions without having to re-run the connector after each modification).

View solution in original post

0 Likes
Absent Member.
Absent Member.

Damian,

It worked. Thanks for your help.  Not sure what I was doing wrong before but after coping your regex in the the regex tester, the results came back.  I was actually working on the other option that you suggested to set it as strings and concat 2 strings and then use regexToken which is another nightmare.  I'll stick with this option.  Thanks again.

0 Likes
Absent Member.
Absent Member.

No problem

The Regex Wizard is great for testing how the connector itself would handle the regex and functions that you throw at it - always worth reverting to!

--

Damian Skeeles

+44 7917 443073

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.