filter search results based on subsearch with regex

[margo_zefirka]

Hi dear community!
I'm trying to build the dashboard using records in two states STATE1 and STATE2. I'm logging state changes so in logs I have these lines:

RECORD_<record_id>_CHANGED_STATE_TO STATE1

RECORD_<record_id>_CHANGED_STATE_TO STATE2

To get all the records in STATE2 I use this and it works well: 

index=... source=... "CHANGED_STATUS_TO STATE2" | eval action="STATE2" 
| timechart count by action span=200m

To get the records in STATE1 I need to grep all the records "CHANGE_STATUS_TO STATE1" but filter it removing all the records that are already in STATE2

I was trying to extract record_id and use subsearch like this but seems I'm doing something wrong:

index=... source=... NOT record_id+"CHANGED_STATUS_TO STATE2" [search index=... source=... | rex "(?<record_id>\d+)_CHANGED_STATUS_TO STATE1" | fields record_id] | eval action="STATE1" | timechart count by action span=200m

Could you please help me?

[ITWhisperer]

So in other words, you want the last action (since this appears to be the state the record was changed to) and the last time for each record id?

index=... source=... "CHANGED_STATUS_TO"
| rex "(?<record_id>\d+)_CHANGED_STATUS_TO"
| stats last(_time) as _time last(action) as action by record_id
| timechart count by action span=200m

[margo_zefirka]

Thank you  @ITWhisperer for the reply

Yes, I want the last action. I have ran your query and sorry but I'm not sure I understand the result. I can see a chart with only one color (action) but I need the count of STATE1 and the count of STATE2 per time unit

[ITWhisperer · Accepted solution]

I had assumed action was a field you had already extracted - try it this way

index=... source=... "CHANGED_STATUS_TO"
| rex "(?<record_id>\d+)_CHANGED_STATUS_TO\s(?<action>\w+)"
| stats last(_time) as _time last(action) as action by record_id
| timechart count by action span=200m

[margo_zefirka]

Thank you very much! 

[margo_zefirka]

I have NULL instead of action