How can I use my billing info to create a prediction for the future?

[RexStout]

I've asked about this before and now I've re-loaded the raw data without any modifications. It looks like this (without an actual timestamp):

Month,Billing,MsgType,BillSize,Direction
2013-04,BI70276,ORDHDR,5,SENT
2013-04,BI70276,INVFIL,8,RECV
2013-04,BI70276,ORDHDR,5,SENT
2013-04,BI70276,INVFIL,34,RECV
2013-04,BI70276,ORDHDR,20,SENT
2013-04,BI70276,INVFIL,13,RECV
2013-04,BI70276,ORDHDR,7,SENT
2013-04,BI70276,INVFIL,1,RECV
2013-04,BI70276,ORDHDR,1,SENT
2013-04,BI70276,ORDHDR,5,SENT
2013-04,BI70276,INVFIL,4,RECV
2013-04,BI70276,ORDHDR,6,SENT
2013-04,BI70276,INVFIL,9,RECV
2013-04,BI70276,ORDHDR,12,SENT
2013-04,BI70276,INVFIL,178,RECV.................................etc.

I have this data for every CCYY-MM for the last 53 months, c200k events. So, no actual timestamp for each event.
If I use this:

index=IX Billing=BI70400 MsgType=ORDHDR Direction=SENT | stats sum(BillSize) as MonthSize by Month

...I get the column chart that I expect/want.
How can I use this to create a prediction for the future? We've tried a few variations, based on this, but without success.

Thank you.

[RexStout]

As I'm getting my data from an Excel spreadsheet, I took your hint and edited the [raw] data so that my CCYY-MM became CCYY-MM-DD HH:MM:SS:XX.

I can now use, virtually, the same search to get what I want.

Thank you,

Richard aka RexStout

[RexStout]

Hmm, I've re-edited the raw data now.....twice.

I suppose I'd have to run the search again for that index...but I'm fairly sure that...

I'd guess it was: dd-mm-ccyy:hh:mm:ss
I used all 12s for the hh:mm:ss so that it slotted into the middle of the day.
The only other option would've been: dd-mm-ccyy.hh:mm:dd

What's the best format for a Splunk timestamp?

Richard aka RexStout

[tmarlette · Accepted solution]

Honestly, the easiest way to do this is to add:
2013-04-{arbitrary day and time} and then just cascade it using excel for the month and THEN load the data. In order to do a 'predict' of any kind, Splunk must have a _time reference, as it has to plot data points into time in the future.

The easiest way to make this happen is to adjust your data to reflect a timestamp.

In the past on daily data I've used midnight, but on intraday data I use hourly increments, and hourly data i'll use minute increments and then load the data into Splunk.

If you're doing monthly predictions, you'll have to do a |timehcart span=1mthen use the predict function, but as long as you're doing monthly increments, you should be able to force a timestamp like I was mentioning. If you need it by the minute or the hour, it will not be as accurate.

[RexStout]

Well, what you're suggesting is exactly what I'd tried but never got it to work. All I got was a sum of the events and a prediction of that too. I was convinced that it was all because I tried to shoe-horn in a fake timestamp.

Back to the drawing board!

Thank you,

Richard aka RexStout.

[tmarlette]

What is the format of the timestamp you used? can you copy and paste it here?