Timechart from lookupfile using Date field

[Barty]

Good morning all,

I have a little challenge for someone whom has far superior brains than myself! I have created a lookup file for one of our teams due to the fact that we cannot permit index access to where the original indexed emails reside. The team would like to be able to timechart against the count of emails by sender. There are 3 x mailboxes that the mails could come in from, as such seeing a breakdown as to the date & time recieved from each mailbox, over the day, would give some value as to trend analysis. Normally that would easily be achieved via a |timechart span=1m count by From, however - I can't run this as the _time is escaped due to the nature of the lookup.

My question is then, how do I go about extracting the 'Date' values from the lookup and use them to chart against BY from? I have the following search to try and conver the text date string into a usable time value:-

| inputlookup dxpt_mails.csv 
| search Date="09*"
| eval sent_date=strptime(Date,"%d-%b-%Y %H:%M:%S") 
| convert timeformat="%Y-%m-%dT%H:%M:%S" ctime(sent_date)

The sample lookup file is as such:-

Date    From    Subject sent_date   
09-Apr-2020 09:05:13 +0100  FormSubmission@test.com Pensions & retirement request a call back Form Submission   2020-04-09T09:05:13 
09-Apr-2020 09:51:19 +0100  FormSubmission@test.com Life change address Form Submission 2020-04-09T09:51:19 
09-Apr-2020 10:02:48 +0100  FormSubmission@test.com Life change address Form Submission 2020-04-09T10:02:48 
09-Apr-2020 10:23:58 +0100  FormSubmission@test.com Life change address Form Submission 2020-04-09T10:23:58 
09-Apr-2020 10:22:16 +0100  FormSubmission@test.com Life request sum assured Form Submission    2020-04-09T10:22:16 
09-Apr-2020 10:35:56 +0100  FormSubmission@test.com Life change address Form Submission 2020-04-09T10:35:56 

Thank you to anyone able to assist, and a happy easter to you all!

[to4kawa · Accepted solution]

| makeresults
| eval _raw="Date,From,Subject,sent_date
09-Apr-2020 09:05:13 +0100,FormSubmission@test.com,Pensions & retirement request a call back Form Submission,2020-04-09T09:05:13
09-Apr-2020 09:51:19 +0100,FormSubmission@test.com,Life change address Form Submission,2020-04-09T09:51:19
09-Apr-2020 10:02:48 +0100,FormSubmission@test.com,Life change address Form Submission,2020-04-09T10:02:48
09-Apr-2020 10:23:58 +0100,FormSubmission@test.com,Life change address Form Submission,2020-04-09T10:23:58
09-Apr-2020 10:22:16 +0100,FormSubmission@test.com,Life request sum assured Form Submission,2020-04-09T10:22:16
09-Apr-2020 10:35:56 +0100,FormSubmission@test.com,Life change address Form Submission,2020-04-09T10:35:56"
| multikv forceheader=1
| table Date,From,Subject,sent_date
| eval _time=sent_date
| timechart cont=f count by From

For this result, recommend is following:

| inputlookup dxpt_mails.csv 
| search Date="09*"
| eval _time=strptime(Date,"%d-%b-%Y %T %z") 
| timechart cont=f count by From

[gcusello]

Hi @Barty,
let me understand: you created a view of your data in this lookup to access these data by some userses using a search with outputlookup, is it correct?

To do the same thing you could use a time based lookup, using the lookup as an index.

Or (better) instead outputlookup, you could use the collect command to save the search results in a summary index and give the access to this summary index to the users you want.
In this way your users don't access the email main index but only the summary index and you have all the feature of the index instead of a lookup.

Ciao.
Giuseppe

[Barty]

Good afternoon Giuseppe,

You're quite right - I have created this lookup for the Team as they cannot have access to the underlying mail index. However, they do not utilise outputlookup, they only invoke the data by running an |inputlookup command.

I did think about replacing the schedule search that populates the lookup with the collect command, however collect also presents the issue of potentially replicating events in the target index unless your timeframes are very very precise. That's why I hoped I would be able to take the string value of the Date field and convert into a usable time field to then chart against.