Reporting

Prior day Report on Monday

hartfoml
Motivator

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`
0 Karma
1 Solution

sowings
Splunk Employee
Splunk Employee

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

This works:

index=main [ search index=_internal | head 1 | eval today=strftime(time(), "%a") | eval earliest=if(today="Mon", "-3d@d", "-1d@d") | eval latest="@d" | return earliest latest ]

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:

index=_internal | head 1 | eval today=strftime(time(), "%a") | eval earliest=if(today="Mon", "-3d@d", "-1d@d") | eval latest="@d" | table earliest, latest

View solution in original post

sowings
Splunk Employee
Splunk Employee

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

This works:

index=main [ search index=_internal | head 1 | eval today=strftime(time(), "%a") | eval earliest=if(today="Mon", "-3d@d", "-1d@d") | eval latest="@d" | return earliest latest ]

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:

index=_internal | head 1 | eval today=strftime(time(), "%a") | eval earliest=if(today="Mon", "-3d@d", "-1d@d") | eval latest="@d" | table earliest, latest

hartfoml
Motivator

Thanks for the lesson and the help

your a true asset to the Splunk team

Hope the weather is nice in New Jersey

0 Karma

sowings
Splunk Employee
Splunk Employee

Edited to provide a working example.

0 Karma

hartfoml
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...