How to compare events from the last two weeks to find authentication success difference?

[atebysandwich]

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 

[rut]

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.