Stats count use last event

[yoan]

Hello,

I'm trying to make a report to count the number of interfaces available and used.

I found the query that matches my need.

index=centreon check_command="Cisco-SNMP-Interfaces-Global-Status" service_description="Status_All-Interfaces" src_interface!="Ethernet*.*" src_interface!="Vlan*" src_interface!="mgmt*" src_interface!="port*" src_interface!="Null*" src_interface!="loopback*" 
| rex field=host "ZSE-(?<loc>\w+)-(?<room>\w+).*"
| replace "1H" WITH "UC1"| replace "1E" WITH "UC1"| replace "2H" WITH "UC2"|replace "2F" WITH "UC2"| replace "6E" WITH "C6"| replace "6F" WITH "C6"| replace "6T" WITH "C6"| replace "4B" WITH "C4"| replace "4T" WITH "C4"| replace "4E" WITH "C4"
| eval site=loc+"-"+room
| stats count(src_interface) as tot_int by site
| appendcols
[search index=centreon check_command="Cisco-SNMP-Interfaces-Global-Status" service_description="Status_All-Interfaces" src_interface!="Ethernet*.*" src_interface!="Vlan*" src_interface!="mgmt*" src_interface!="port*" src_interface!="Null*" src_interface!="loopback*" state_interface="up" 
| rex field=host "ZSE-(?<loc>\w+)-(?<room>\w+).*"
| replace "1H" WITH "UC1"| replace "1E" WITH "UC1"|replace "2H" WITH "UC2"|replace "2F" WITH "UC2"| replace "6E" WITH "C6"| replace "6F" WITH "C6"| replace "6T" WITH "C6"| replace "4B" WITH "C4"| replace "4T" WITH "C4"| replace "4E" WITH "C4"
| eval site=loc+"-"+room
| stats count(state_interface) as tot_up by site]
| eval tot_free=tot_int-tot_up

My concern is the frequency of data reception by SPLUNK is not stable (plus or minus 10 minutes)

How to make so that my dream is based only on the last events received?

Thank's

[PickleRick]

If I understand you correctly, you're periodicaly getting a "dump" of a state of some gizmo. Each dump consists of several events. And you want to do a report only from the latest dump.

If the whole dump is "condensed" in terms of time (starts at some point in time and all subsequent events come at the same time or in a short period from the start), you can use a subsearch to find latest run of your dump and run the search with thus generated time limit.

Something like that:

<<your search>> [
  | tstats max(_time) as latest where index=your_index 
  | eval earliest=latest-15 ] 
| <<rest of your search>>

You might want to add timerange constraints to tstats (I don't remember if it's needed)

[Richfez]

You could possibly just stick a

| head 1000

or some other number right after the base search (before the first rex) and run it over a somewhat longer period (like 20 or 30 minutes in this case), if you had a reasonably predicable "count" you could go back to consistently.

How would you, as an actual person looking at the data, describe what you mean by "only showing the last data".  I mean, how would you do that if you had this data in excel?  What would define the "last data," and how would that be split apart from the data that came before?

I think with this answer we might be able to come up with something better.

On a side note, this search looks both really fragile, and also could be faster and better.

Was the replace for 1H and 1E with UC1 skipped on purpose with the appendcols search?

I think if that was just an omission or isn't important, that you could do this

index=centreon check_command="Cisco-SNMP-Interfaces-Global-Status" service_description="Status_All-Interfaces" src_interface!="Ethernet*.*" src_interface!="Vlan*" src_interface!="mgmt*" src_interface!="port*" src_interface!="Null*" src_interface!="loopback*" 
| head 1000
| rex field=host "ZSE-(?<loc>\w+)-(?<room>\w+).*"
| replace "1H" WITH "UC1"| replace "1E" WITH "UC1"| replace "2H" WITH "UC2"|replace "2F" WITH "UC2"| replace "6E" WITH "C6"| replace "6F" WITH "C6"| replace "6T" WITH "C6"| replace "4B" WITH "C4"| replace "4T" WITH "C4"| replace "4E" WITH "C4"
| eval site=loc+"-"+room
| stats count(src_interface) as tot_int, sum(eval(if(state_interface="up",1,0))) as tot_up by site
| eval tot_free = tot_int - tot_up

I'm sure that probably needs tweaking, but the advantages in speed and non-duplicated SPL is very large - it's probably worth some time doing a thing like this.