I'm trying to create a search using Qualys vulnerability scan data to find hosts that failed to be logged into that were success the previous week.
I've been trying to use this similar example as a template but it doesn't quite get what I'm looking for.
For reference, the Qualys data does not have fields that say something regarding successful or failed authentication attempts - rather they use QIDs.
QID
105015 - Windows Failed
105053 - Unix Failed
38307 - Unix Successful
70053- Windows Successful
Based on the test data you're giving I had to fill some blanks, but if you want to detect a change only, the following could suffice:
| makeresults format=csv data="
_time,host,qid
2022-04-19,host_a,105015
2022-04-26,host_a,70053
2022-04-19,host_b,38307
2022-04-26,host_b,105053
2022-04-19,host_c,70053
2022-04-26,host_c,70053"
| stats dc(qid) as qid_count, last(qid) as last_qid by host
| where qid_count>1 AND (last_qid="38307" OR last_qid="105053")
So the "stats dc" counts unique qid values by host, where there's more than 1 value it has changed. If you want to detect its last attempt has failed" the last_qid field could be compared to known fail states.