topic Re: Prior day Report on Monday in Reporting

Prior day Report on Monday

hartfoml — Mon, 09 Sep 2013 16:21:09 GMT

I have a report that shows me the items installed on my systems for the prior day. the only problem is Monday as no one works on Sunday the report is always blank but should show for all day Friday and Saturday and Sunday. Mondays report should show for Friday and the weekend

Can i do this with the same search or do I have to create a new search?

index=ea sourcetype="*wineventlog:application" EventCode=11707 OR EventCode=11708 OR EventCode=11728 host!="*dev*" earliest=-1d@d latest=@d 
| eval date_wday = strftime(_time, "%A") 
| dedup _raw 
| rex field=Message "(?s)Product: (?<product_name>.*) --" 
| table _time date_wday host User product_name EventCode 
| eval status=case(EventCode == 11708, "Failed", EventCode == 11707, "Success", EventCode == 11728, "Success") 
| rename _time AS Time host AS Server product_name AS "Product Installed" status AS "Status" 
| convert timeformat="%m/%d/%Y - %H:%M:%S" ctime(Time) 
| fields Time Server User "Product Installed" Status EventCode`

Re: Prior day Report on Monday

sowings — Mon, 09 Sep 2013 17:20:11 GMT

I used a subsearch to populate "earliest" and "latest".

This works:

I started here:

[ | stats count | eval earliest=if(date_wday="monday", "-3d@d", "-1d@d") | eval latest="@d" | table earliest, latest ]

That includes the tabular subsearch output, so I get a parse error from the search.

Switch instead to return:

[ | stats count | eval earliest=if(date_wday="monday", "-3d@d", "-1d@d") | eval latest="@d" | return earliest, latest ]

From here I can use the search inspector to see what the subsearch evaluates to. It seems that it always comes back as -1d@d. I think you're right about the context for "date_wday". If I change my | stats to something else "fast", like "search index=_internal | head 1", I do get date_wday, but that would be subject to the time of the one event returned. I think instead we should consider an eval / strptime based approach in relation to "now".

This is what I used to debug my settings until I had the today condition correct:

Re: Prior day Report on Monday

hartfoml — Mon, 09 Sep 2013 18:02:18 GMT

This sounds like a good suggestion but I am having trouble with where to put it in the search:

search foo=* | search earliest=if(date_wday="Monday",-3d@d,-1d@d) latest=@d | table foo

This doesn't work because there is not time-frame to look for date_wday.

Maybe something like this;

search foo= [| search _time | eval earliest=if(date_wday="Monday",-3d@d,-1d@d)] latest=@d | table foo

Re: Prior day Report on Monday

sowings — Mon, 09 Sep 2013 18:39:41 GMT

Edited to provide a working example.

Re: Prior day Report on Monday

hartfoml — Mon, 09 Sep 2013 20:44:00 GMT

Thanks for the lesson and the help

your a true asset to the Splunk team

Hope the weather is nice in New Jersey