Splunk Search

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

peiffer
Explorer

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
0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

sundareshr
Legend

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
Explorer

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

0 Karma

peiffer
Explorer

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

0 Karma

gcusello
Legend

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

0 Karma

peiffer
Explorer

The aim was to count or chart over time.

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...