How to create a new field and set it with a value of 5 minutes for each event?

[marina_rovira]

Hello all,

I'm making an alerts report and by now, I have the total number of Alerts for a month, let's set it as 10,000.
Now, I want to set an estimated time spent for all these alerts. To do that, I need to create a field with the value of 5 minutes for each Alert (is more or less the time I think the people spent handling this).

I want to set it for then, sum up the field for each event and get the estimated time as 10,000 alerts * 5 minutes (+or-) for alert = 50,000 minutes ~ 833 hours

For now, I've done this and it seems to work:

| eval startofevent=strptime(strftime(_time, "%Y/%m/%d 00:00:00"), "%Y/%m/%d %H:%M:%S") | eval endofevent=strptime(strftime(_time, "%Y/%m/%d 00:05:00"), "%Y/%m/%d %H:%M:%S") | eval new_duration=endofevent-startofevent  | stats count(Alert) as TotalAlerts sum(new_duration) as Total_time |  eval TotalDuration = tostring(Total_time, "duration") | table TotalAlerts,TotalDuration

It give the result as DD+HH:MM:SS.00000 and I would like it without .00000 or in some friendly format, so If someone has a suggestion to do it in a better way, it will be welcomed! 🙂

Thank you!

[sundareshr · Accepted solution]

You could try something like this

... | streamstats range(_time) as new_duration | stats count(Alert) as TotalAlerts sum(new_duration) as Total_time | eval Total_time =tostring(round(Total_time , 0), "duration") | table TotalAlerts,TotalDuration

http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/Streamstats

http://docs.splunk.com/Documentation/Splunk/6.1/SearchReference/Commonstatsfunctions

[marina_rovira]

It seems a good try, but where I should set the duration of 5 minutes per event?

Also, I check it in my code, and there is a new field called new_duration, which is good, but I don't know why, TotalDuration appears empty :S

I have problem with sum(X) and avg(X) with stats, I think they worked but my results appear empty the most of times, and I don't understand why.

Thanks a lot, you're being really helpful! 🙂

[sundareshr]

Try this

| stats count as TotalAlerts | eval Total_time = tostring(TotalAlerts*5, "duration") | table TotalAlerts, Total_time

[sundareshr]

I saw you had another question about finding the average. If this is related, I would change the search to this

 | stats count as TotalAlerts | eval Total_time = TotalAlerts*5 | stats avg(Total_time) as AvgTime | fieldformat AvgTime =tostring(AvgTime, "duration")

[marina_rovira]

At this one the average is not a theme. I tried what you said before and the results are these:

TotalAlerts Total_time
25211 1+11:00:55

I want to set a new field of 5 minuts for each alert, but the results doesn't seems realistic to me, I mean 5 minuts for 25211 alerts, seems quite difficult that the total spent time handling alerts is 1 day and 11 hours :S

[marina_rovira]

I was calculating and I think I am wrong and the result is correct like this. I just realised, if now I am correct, that it let's the time in seconds although I'm setting 5 minutes. And calculating (((Total_time/60)/60)/24) It give this "1+11:00:55"

I get it! Thanks for you comments! 🙂

[aholzer]

I might be misunderstanding your requirement... but why don't you just do an eval after your stats count?

... | stats count(Alert) as TotalAlerts | eval Total_time = TotalAlerts * 5

If this doesn't work for you please explain further your requirement.

[marina_rovira]

It's something like this, I've tried, but I think the final result is not correct. Maybe because we are not setting that this 5 is 5 minutes or I don't knwo why. I keep trying this 🙂 thanks!