Search to compare two blocks of 12 weeks using field other than _time

[shazbot79]

Hi, new member so apologies if I miss any forum etiquette! 

I'm trying to query Service Now incident data to show number of tickets opened over the last 52 weeks and compare it to the previous 52 week period. I had a bit of help from a third party company to build some queries but now they have gone I can see a few issues with the numbers. The query is as follows:

index=prod_service_now sourcetype=snow:incident earliest=-52w@w1 latest=@w1 number=INC* |dedup sys_id| search dv_assignment_group=ITSOCS* NOT dv_assignment_group="ITSOCS Logistics" | eval _time = strptime(opened_at,"%Y-%m-%d%H:%M:%S")
| eval now = now()
| eval now = relative_time(now,"@w1")
| eval earliest = now()
| eval earliest = relative_time(earliest,"@w1")
| eval earliest= relative_time(earliest, "-52w@w1")
| where _time >= earliest AND _time <= now
| eval _time = relative_time(_time,"@w1")
| timechart span="1w@w1" dc(number) as current_incident_count | rename VALUE as NULL * as "* - CURRENT YEAR" | rename "_time - CURRENT YEAR" as _time | fields - "_span - CURRENT YEAR", "_spandays - CURRENT YEAR"
| appendcols
[ |search index=prod_service_now sourcetype=snow:incident earliest=-104w@w1 latest=-52w@w1 number=INC* | dedup sys_id |search dv_assignment_group=ITSOCS* NOT dv_assignment_group="ITSOCS Logistics"| eval _time = strptime(opened_at,"%Y-%m-%d%H:%M:%S")
| eval now = now()
| eval now = relative_time(now,"@w1")
| eval now = relative_time(now,"-52w@w1")
| eval earliest = now()
| eval earliest = relative_time(earliest,"@w1")
| eval earliest= relative_time(earliest, "-104w@w1")
| where _time >= earliest AND _time <= now
| eval _time = relative_time(_time,"@w1")
| timechart span="1w@w1" dc(number) as historical_incident_count | rename VALUE as NULL * as "* - LAST YEAR" | rename "_time - LAST YEAR" as _time | fields - "_span - LAST YEAR", "_spandays - LAST YEAR"]

One obvious issue is the query limits the base search to blocks of 52 weeks based on _time which in this case is the last updated field. So, I could have tickets that will be missed if they were opened in that period but updated outside of that period. If I remove the earliest and latest parameters then the search is painfully slow and also the lines on the graph are no longer overlaid but instead they run sequentially.

Can anyone suggest a better way to do this? What I need is a line graph with 52 weeks and then the 2 series need to be on top of one another.

Hopefully I have given enough info, please shout if anything isn't clear! Thanks for reading 🙂

[shazbot79]

I should add the dates on the graph should be the Monday of each week so the incidents need to be grouped by week. Thanks

[shazbot79]

Thanks for having a look, your search only brings back a fraction of the tickets it should.

In terms of setting time to opened_at - because this happens after the base search I still believe it will miss tickets...for example in my query I search for tickets updated between 52 weeks ago and 104 weeks ago. I then summarise by opened_at in the same period....if one of the incidents was opened 53w ago but last updated 51w ago it would be missed....

Good chance I am completely misunderstanding the base search/sub search logic - I'm very new to SPL.

[ericjorgensenjr]

Kind of a shot in the dark, but this might work:

It looks like you're setting _time to the 'opened_at' field, so I don't think there should be any issues with updated events.

index=prod_service_now sourcetype=snow:incident earliest=-1y@d latest=now number=INC* 
| dedup sys_id 
| search dv_assignment_group=ITSOCS* NOT dv_assignment_group="ITSOCS Logistics" 
| eval _time = strptime(opened_at,"%Y-%m-%d%H:%M:%S") 
| timechart span=1w dc(number) as current_count 
| appendcols 
    [| search index=prod_service_now sourcetype=snow:incident earliest=-2y@d latest=-1y@d number=INC* 
    | dedup sys_id 
    | search dv_assignment_group=ITSOCS* NOT dv_assignment_group="ITSOCS Logistics" 
    | eval _time = strptime(opened_at,"%Y-%m-%d%H:%M:%S") 
    | eval _time = relative_time(_time,"+1y") 
    | timechart span=1w dc(number) as historical_count ]