topic Re: timechart output - drop most recent week in Splunk Search

How to write timechart output that drops most recent week but keeps in the initial query?

tlmayes — Fri, 08 Jul 2022 14:40:43 GMT

I have a query that must search 9 weeks of data, and then applies a filter against a single field (dv_opened_at) looking for specific events that occurred within an 8 week period. Initial 9 week search is necessary to catch events that were modified after the end of the last week, yet had a dv_modified_at time within the last 8 weeks.

query

index=cmdb (dv_number=* OR number=*) dv_state=* dv_assigned_to[| inputlookup cmdb_users.csv| table dv_assigned_to ] earliest=-8w@w latest=now() | table _time number dv_number dv_opened_at dv_assigned_to dv_short_description dv_watch_list dv_sys_updated_on dv_state close_notes | dedup number | eval dv_opened_at=strptime(dv_opened_at,"%Y-%m-%d %H:%M:%S") | where dv_opened_at>=relative_time(now(), "-8w@w") AND dv_opened_at<=relative_time(now(), "@w") | eval _time=dv_opened_at | bin _time span=1w | eval weeknumber=strftime(_time,"%U") | rename dv_assigned_to AS Analyst | timechart limit=0 useother=false span=1w count BY Analyst

The problem is, the timechart outputs 9 weeks of data, and as expected the last week is all 0's. How do I eliminate the current week from the output, but keep the current week in the initial query?

Output

_time Analyst1 Analyst2 Analyst3 Analyst4 2022-05-08 19 6 0 0 2022-05-15 5 4 0 0 2022-05-22 8 2 0 1 2022-05-29 7 4 0 0 2022-06-05 1 3 1 39 2022-06-12 7 1 4 51 2022-06-19 3 2 0 59 2022-06-26 25 5 2 26 2022-07-03 0 0 0 0 #how to drop this row each weekly report

Re: timechart output - drop most recent week

PickleRick — Fri, 08 Jul 2022 04:40:10 GMT

Just filter out the empty row. Something like

| where _time <now()-7*86400

Re: timechart output - drop most recent week

ITWhisperer — Fri, 08 Jul 2022 05:12:21 GMT

timechart uses earliest and latest to determine what time range to use - update these on your search line to snap to the beginning of the week

... earliest=-8w@w latest=@w

Re: timechart output - drop most recent week

tlmayes — Mon, 11 Jul 2022 12:30:54 GMT

cannot do this since doing so would eliminate the present week in the query. Weird, but I need the present week in the query, but not in the output.

Re: timechart output - drop most recent week

ITWhisperer — Mon, 11 Jul 2022 12:47:33 GMT

| where _time < relative_time(now(),"@w")

Re: timechart output - drop most recent week

tlmayes — Mon, 11 Jul 2022 13:51:03 GMT

Exactly. This is what I was looking for. Tried other variations but could not make it work. Yours did. Thanks