Splunk Search
Highlighted

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

Path Finder

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 "sysupdated_on".

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

This is how the search looks right now:

index=servicenow sourcetype=snow:incident incidentstate=* | dedup sysid | 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

0 Karma
Highlighted

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

Path Finder

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 incidentstate=* | chart count as "Status of Tickets" over sysupdated_on by value

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

2) index=servicenow sourcetype=snow:incident incidentstate=* | eval NewTime=strptime(sysupdated_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 sysupdated_on field.

0 Karma
Highlighted

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

Legend

@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.




| eval message="Happy Splunking!!!"


Highlighted

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

SplunkTrust
SplunkTrust

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 

View solution in original post

Highlighted

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

Path Finder

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 incidentstate so that I have lines for a count of each incidentstate?

It is otherwise working as intended. Part of me poking around helped in realizing that for some reason "sysupdatedon" 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 "syscreatedon" 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 "incidentstate" 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 sysid syscreatedon value
| eval WeekEpoch=604800*floor(((strptime(sys
createdon,"%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(relativetime(strptime("2017-01-01","%Y-%m-%d"),"@w"),relativetime(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 sysid 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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

@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
0 Karma