Comparison for use with a trend line

[bworrellZP]

I am trying to compare daily failed logins for a web app. The initial ask was they wanted to know how many failed Yesterday, as compared with the day before. Racking my head around the search, as no matter what I do, I seem to also get todays failed logins, which then does not show yesterday and the day before, but rather today and yesterday.

(index=web1 OR index=web2) AND LoginStatus=Failed
| stats first(*) as * by LoginAttemptID, index, _time
| table _time, date_wday, ClientID, UserIPAddress, GuarantorAccountEntry, tag, LoginAttemptID LoginStatus | eval earliestTime = relative_time(now(),"-2d@d")
| eval latestTime = relative_time(now(),"-0@d")
| where _time>=earliestTime AND _time<=latestTime
| timechart count as Total span="1d" fixedrange=false

I am sure its something silly, but any help is appreciated.

[mayurr98]

hey try this

(index=web1 OR index=web2) AND LoginStatus=Failed earliest=-2d | stats first(*) as * by LoginAttemptID, index, _time | table _time, date_wday, ClientID, UserIPAddress, GuarantorAccountEntry, tag, LoginAttemptID LoginStatus | timechart count span=1d | timewrap d | rename latest_day as today 1day_before as yesterday 2days_before as day_before_yesterday

let me know if this helps you!

[bworrellZP]

This option still listed today in the list. Thank you for the suggestion though

[mayurr98]

oh then you do not need to use timewrap i thought you wanted to compare 3 days data so i put together all three 🙂 just use timechart as suggested by @cmerriman will also do!

[cmerriman · Accepted solution]

can you just put the earliest and latest into the base search?

(index=web1 OR index=web2) AND LoginStatus=Failed earliest=-2d@d latest=@d| stats first(*) as * by LoginAttemptID, index, _time | table _time, date_wday, ClientID, UserIPAddress, GuarantorAccountEntry, tag, LoginAttemptID LoginStatus  | timechart count as Total span="1d" fixedrange=false

you could also try to use timewrap. | timechart count as Total span="1d" fixedrange=false|timewrap 1d

https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Timewrap

[bworrellZP]

This worked, without the need for the timewrap. Thank you.