How to display the results of my transaction search in a table?

[Imjusttesting]

Hi Everyone,

I'm having an issue displaying transaction data in Table.
The end result should look like this:

No | Driver ID | Start Date | Start Time | End Date | End Time |
1  |ACBCD      |2/5/2016    |09:00       |2/5/2016  |18:00     |

There are 2 criteria:
1) Total driving time exceed 8 hrs within 24 hrs by Start Time
2) End time is determine by Gap of more than 30 minutes

My searchstring looks like this, but is not able to display as a table

sourcetype="xxxxxx" | transaction maxspan=8h maxpause=30m
| sort _time | streamstats count as TripNo by username
| table _time

Appreciate and feedback.

[renjith_nair · Accepted solution]

Try this for table format

sourcetype="xxxxxx" | transaction maxspan=8h maxpause=30m| sort _time 
|streamstats count as TripNo by username|eval end=_time+duration
|eval "Start Date"=strftime(_time,"%d-%b-%Y")|eval "End Date"=strftime(end,"%d-%b-%Y")
|eval "Start Time"=strftime(_time,"%H:%M:%S")|eval "End Time"=strftime(end,"%H:%M:%S")
|table TripNo username "Start Date" "Start Time" "End Date" "End Time"

[Imjusttesting]

Thanks @renjith.nair

This work perfectly fine.
Now the next issues, are the criteria;
1) Total driving time exceed 8 hrs within 24 hrs by Start Time
2) End time is determine by Gap of more than 30 minutes

When i look at the Table, some of the data are not group within the 8 hours span and the Stop don't have a 30 mins gap

What the best way to deal with this?

[renjith_nair]

Sorry didn't quite get that .
By 1 , do you mean
You want to find transaction which span across 24 hours and then divide them in 8 hrs span? or you want only thos transactions which are more than 8 hours only?

By 2 , Gap between what? between two transactions?

[Imjusttesting]

Sorry, missed your message

1) I would like to include transaction which are more than 8 hours
2) The 30 mins gap is between the transaction. Meaning if there a 30 mins gap between the transaction, the next event will be a new event

[renjith_nair]

If you would like to include only transaction which are more than 8hrs, then i think maxpause won't work here since maxpause limits the transaction to 8 hours

     Syntax: maxpause=<int>[s | m | h | d]
    Description: Specifies the maximum time in seconds, minutes, hours, or days that the events can span. The maxpause constraint requires there be no pause between a transaction's events of greater than maxpause. If value is negative, disable the maxpause constraint. 
    Default: -1 (no limit)

You should use maxpause only if the driver is supposed to drive only "x" hours which is not your case i believe.

If you have any other uniquely identified fields in these events to co-relate two events rather than time based, that would make things easier

Once you correct the initial search , then you can add |where duration > 28800 to get the transactions more than 8 hours

[Imjusttesting]

Thanks 🙂
Working good.

[Richfez]

Without fixing anything else that may (or may not) be wrong with the search, your table doesn't include all the other items. Try something like...

sourcetype="xxxxxx" | transaction maxspan=8h maxpause=30m
| sort _time | streamstats count as TripNo by username
| table TripNo, username, start_date, start_time, end_date, end_time

You will obviously have to use your own fieldnames - you didn't provide a sample event to work with so I could only pseudo-code them up.

[Richfez]

I'm also not sure this search will do quite what you want. I think your criteria 2 should be fine with your maxpause.

Criteria 1 - what actually do you mean? If you mean they CAN'T drive longer than 8 hours in a day, that's different from they SHOULDN'T drive more than 8 hours a day, which is completely different from "Find transactions where they DID drive longer than 8 hours in a 24 hour period." In several of those cases you may end up wanting maxspan=24h. You may have overlapping transactions within periods, though, which may force you to take a different tack. It would probably be worth a different question to answer that (to keep this one clean and easy to search), but there are answers for trickier things like that, see this as an example (the comments).

Those thoughts and links were just to get you started, hopefully just that bit of info will help you clarify what you are actually trying to display. If you need help with that, please ask, otherwise, have fun Splunking!

[Imjusttesting]

The 1st criteria is, we only need to identify driver that are driving for more than 8 hours.
2nd criteria, the Stop, is identify if there is more than 30 mins Gap.
(The data are constantly send every 1 mins and if the engine is cut off, nothing is send. Thus if there is no event between 30 mins after the last record, then that is a Stop)

Hope this clarify everything

[Imjusttesting]

Thanks.

I tried your search string, Tripno, Username are included but Start and End time are not included.
Is there any way to include the Start and End time?

I'm using the _time for all event.