Is there any way to do stats count over multiple time frames?

[peiffer]

Is there any way to do stats count over multiple time frames?

I am trying to replace something written in perl and output to .xls format. I wish to count IP addresses in each subnet; I have about 3500 subnets that I wish to summarize across multiple time frames ( current, -30days, -60days, -90days). I have done the first part by doing a CIDR lookup to subnet and then counting.

I am looking for alternate ideas to accomplish the same thing. Help?

Tim

( index=network_dns OR index=network_bro )  earliest=-30d
| rex field=named_message "client (?<client_ip>\d+\.\d+\.\d+\.\d+)"
| fields _time id_orig_h id_resp_h  client_ip
| eval ip=coalesce(id_orig_h, id_resp_h, client_ip)
| regex ip="\d+\.\d+\.\d+\.\d+" 
| dedup ip
| lookup cidr_ranges subnet AS ip OUTPUT subnet
| eval ip_class=if(`is_my_network(ip)`, "MINE", "External")
| stats count(ip) as count by subnet, ip_class
| where ip_class="MINE"
| where subnet!="UNKNOWN"
| sort subnet
| table count subnet

[sundareshr · Accepted solution]

To compare over time, try this

 ( index=network_dns OR index=network_bro )  earliest=-90d
| eval when=case(_time<relative_time(now(), "-60d@d"), "90d", _time>relative_time(now(), "-60d@d") AND _time<relative_time(now(), "-30d@d"), "60d", _time>relative_time(now(), "-30d@d"), "30d", 1=1, "Other")
| rex field=named_message "client (?<client_ip>\d+\.\d+\.\d+\.\d+)"
| eval ip=coalesce(id_orig_h, id_resp_h, client_ip)
| regex ip="\d+\.\d+\.\d+\.\d+" 
| dedup ip
| lookup cidr_ranges subnet AS ip OUTPUT subnet
| eval ip_class=if(`is_my_network(ip)`, "MINE", "External")
| where ip_class="MINE"
| where subnet!="UNKNOWN"
| eval subnet=subnet." (".ip_class.")"
| chart count over subnet by when
| sort subnet

[peiffer]

I think this was what i was looking for. Why didn't I think of it "| chart count over subnet by time " is just what I was looking for. I will try it out, make a few modifications and likely accept this answer.

Thanks,
Tim

[peiffer]

While sundareshr provided exactly what I asked for, unfortunately, I need something with higher performance. I believe that I will need to do something with summary indexes in order to complete in a reasonable time frame. None the less, I will accept this answer and rephrase / re-pose the question.

Tim

[gcusello]

Hi peiffer,
your request is a suggest to semplify your search or a different one?

If you want to semplify it you could do something like this

( index=network_dns OR index=network_bro )  earliest=-30d
 | rex field=named_message "client (?<client_ip>\d+\.\d+\.\d+\.\d+)"
 | eval ip=coalesce(id_orig_h, id_resp_h, client_ip)
 | regex ip="\d+\.\d+\.\d+\.\d+" 
 | dedup ip
 | lookup cidr_ranges subnet AS ip OUTPUT subnet
 | search `is_my_network(ip)` subnet!="UNKNOWN"
 | stats count(ip) as count by subnet
 | table count subnet

in my mind there is that is_my_network(ip) is a search macro and that all the ips fields are correct.

To more semplify, you could extract client_ip field and create a calculated field, so you could delete also 2 and 3 item.

Alternatively you could do something like this:

( index=network_dns OR index=network_bro )  earliest=-30d
[ | inputlookup cidr_ranges | rename subnet AS query | fields query ]
| search `is_my_network(ip)` subnet!="UNKNOWN"
| stats count(ip) as count by subnet
| table count subnet

In this way you search as a string in your search all the IP Addresses that you have in your lookup.
Verify if IP format is the same between lookup and search results.

Bye.
Giuseppe

[peiffer]

The aim was to count or chart over time.

subnet, 0-30days, 31-60days, 61-90days,