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
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
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
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
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
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
The aim was to count or chart over time.
subnet, 0-30days, 31-60days, 61-90days,