How to use a different field other than _time to group events based on a desired time interval (e.g. 1 week)

[bgagliardi1]

I'm working with ServiceNow incident logs and I'm trying to group events weekly, based on their final state in the week.

I've pulled them from the beginning of the year, and I did this starting about a month ago so _time is pretty skewed. I believe my desired field is "sys_updated_on".

I want to have a line graph for each incident's state, grouped by when it was last updated ("sys_updated_on").

This is how the search looks right now:

index=servicenow sourcetype=snow:incident incident_state=* | dedup sys_id | timechart span=7d count(sys_id)

I was looking at this one article (had to modify the URL since I don't have enough karma yet to post a URL), but I don't understand the syntax of how to use chart to do it.

splunkforums/answers/9730/using-a-different-time-base-on-timechart

Thanks,

Brandon

[DalJeanis · Accepted solution]

Seems like that will get you the number of tickets that changed to their final state in that week.

Something like this will give you how many tickets ended each week in a given state, whether the state had changed or not.

index=servicenow sourcetype=snow:incident incident_state=* 

| rename COMMENT as "Get the latest status for each week"
| fields sys_id sys_updated_on incident_state 
| eval WeekEpoch=604800*floor(((strptime(sys_updated_on,"%Y-%m-%d %H:%M:%S"))+345600)/604800)-345600
| stats latest(incident_state) as incident_state by sys_id WeekEpoch

| rename COMMENT as "Fan out the record, one event per week in the year"
| eval Time=mvrange(relative_time(strptime("2017-01-01","%Y-%m-%d"),"@w"),relative_time(now(),"+1w@w+1s"),604800)
| mvexpand Time

| rename COMMENT as "Kill records that hadn't occurred yet"
| where Time>=WeekEpoch
| rename COMMENT as "You can also kill records that have been in a completed state for some certain length of time"

| rename COMMENT as "Sort them into order and leave only the most recent record for each week"
| sort 0 sys_id Time - WeekEpoch
| streamstats count as recno by sys_id Time
| where recno = 1 

| rename COMMENT as "stats up and format the final records"
| stats count as statuscount by WeekEpoch incident_state
| rename WeekEpoch as _time 

[bgagliardi1]

Hi DalJeanis,

Thanks for taking a shot at this. I see some stuff in here that's new to me (e.g. WeekEpoch) that I'll take a look into.

One more question - and sorry to tack this on - how do I break all of this up by incident_state so that I have lines for a count of each incident_state?

It is otherwise working as intended. Part of me poking around helped in realizing that for some reason "sys_updated_on" field was being effected in the same way the _time was, and was only going as far back as my ingestion date. I've substituted the field with the seemingly hard-coded "sys_created_on" field for now, just as a proof of concept - knowing that I see variables that go back more than a year ago. I've also switched out the "incident_state" to my look up field, "value", so that the status is human friendly.

Here's what it looks like right now:

index=servicenow sourcetype=snow:incident value=
| rename COMMENT as "Get the latest status for each week"
| fields sys_id sys_created_on value
| eval WeekEpoch=604800*floor(((strptime(sys_created_on,"%Y-%m-%d %H:%M:%S"))+345600)/604800)-345600
| stats latest(value) as value by sys_id WeekEpoch
| rename COMMENT as "Fan out the record, one event per week in the year"
| eval Time=mvrange(relative_time(strptime("2017-01-01","%Y-%m-%d"),"@w"),relative_time(now(),"+1w@w+1s"),604800)
| mvexpand Time
| rename COMMENT as "Kill records that hadn't occurred yet"
| where Time>=WeekEpoch
| rename COMMENT as "You can also kill records that have been in a completed state for some certain length of time"

| rename COMMENT as "Sort them into order and leave only the most recent record for each week"
| sort 0 sys_id Time - WeekEpoch
| streamstats count as recno by sys_id Time
| where recno = 1
| rename COMMENT as "stats up and format the final records"
| stats count as statuscount by WeekEpoch value
| rename WeekEpoch as _time*

Thank you very much.

Brandon

[DalJeanis]

@bgagliardi1 - Yes, I had to work out that silly formula to calculate the date of the Sunday. That should be replaced with relative_time(strptime(sys_created_on,"%Y-%m-%d %H:%M:%S"),"@w"). I was having a brain thunk because bin is not streaming distributable, but just now I realized that relative_time is. Serves me right for being up that late.

If "value" is already on the records, then this should be doing what you ask. You should have a field called statuscount for each week for each value...

| stats count as statuscount by WeekEpoch value
| rename WeekEpoch as _time, value as Status
| timechart sum(statuscount) as count by Status

[bgagliardi1]

So I've evolved my first search, and have it pretty close - minus the part where I want to use a field other than _time as if it is _time:

1) index=servicenow sourcetype=snow:incident incident_state=* | chart count as "Status of Tickets" over sys_updated_on by value

Then I read that there was a say to eval a new _time:

2) index=servicenow sourcetype=snow:incident incident_state=* | eval NewTime=strptime(sys_updated_on,"%Y-%m-%d %H:%M:%S") | eval _time=NewTime | timechart count as "Status of Tickets" by value span=7d

However, the span is still pulling from the native _time, so my span is only going back to the date that I pulled down all the logs for the first time, instead of when the incidents were updated, as expressed in the sys_updated_on field.

[niketn]

@bgagliardi1, Put span=7d next to timechart command i.e.

 | timechart span=7d count as "Status of Tickets" by value 

Please try out and confirm.