topic Re: How to find the number of times a specific field value has been present over time in Splunk Search

How to find the number of times a specific field value has been present over time

information_sec — Thu, 04 Aug 2016 21:29:49 GMT

I'm trying to find the average time (in weeks) it takes to patch specific network vulnerabilities. I take in data from network scans which include the hostname and the name of the plugin that are vulnerable. The scans run weekly, so if it took a department three weeks to patch a vulnerable plugin, that "plugin_name=VulnerabilityX" within "hostname=0.0.0.0" should be present three times. Then I could take those numbers of occurrences and use them to find the average number of weeks it takes to patch each plugin_name.

There are 100+ hostnames each with however many plugins that I need to do this for. From what I've tried and researched so far it seems like there is no easy way to do this without a huge mess of subsearches. There is a timestamp field included in the events with the field values represented in epoch time, for example "timestamp=1469561133 ", which seems like it could be useful, but I haven't found a way yet.

Any advice at all on this would be greatly appreciated!

Re: How to find the number of times a specific field value has been present over time

sundareshr — Thu, 04 Aug 2016 21:56:20 GMT

It would help if you share a few raw events. Minus that, you could try something like this

index=scanresults earliest=-3mon@mon | chart count over host by plugin_name limit=0

Re: How to find the number of times a specific field value has been present over time

information_sec — Thu, 04 Aug 2016 23:49:13 GMT

@sundareshr Here are some examples of the raw data that we get, this is just for one scan as you can see at host_start but you can imagine this would be repeated across scans for every week.

 {"object_id": 167, "host-ip": “192.168.5.1", "uuid": "91e61c00-97b6-3494-2a5e-354d79a748653191c7652a729bb3", "scanner_name": "Local Scanner", "control": true, "edit_allowed": true, "policy": "Credentialed Patch Audit", "severity": 0, "scan_start": 1469527225, "scanner_end": 1469561107, "plugin_id": 10736, "pci-can-upload": false, "plugin_name": "DCE Services Enumeration", "host_id": 23326, "severity_index": 5, "host-fqdn": “host@domain.com”, "haskb": true, "folder_id": 14, "user_permissions": 128, "host_start": "Tue Jul 26 11:51:28 2016", "vuln_index": 6, "sid": "167", "hostcount": 24429, "scan_type": "local", "count": 9, "targets": “192.168.1.1-192.168.100.255”, "host_end": "Tue Jul 26 11:54:02 2016", "scan_end": 1469561133, "hasaudittrail": true, "plugin_family": "Windows", "status": "completed", "hostname": “hostname@domain.com”, "scanner_start": 1469527225, "timestamp": 1469561133, "name": "Weekly Authenticated Scans"} 

{"object_id": 167, "host-ip": "192.168.5.1", "uuid": "91e61c00-97b6-3494-2a5e-354d79a748653191c7652a729bb3", "scanner_name": "Local Scanner", "control": true, "edit_allowed": true, "policy": "Credentialed Patch Audit", "severity": 0, "scan_start": 1469527225, "scanner_end": 1469561107, "plugin_id": 10940, "pci-can-upload": false, "plugin_name": "Windows Terminal Services Enabled", "host_id": 23326, "severity_index": 0, "host-fqdn": "host@domain.com", "haskb": true, "folder_id": 14, "user_permissions": 128, "host_start": "Tue Jul 26 11:51:28 2016", "vuln_index": 11, "sid": "167", "hostcount": 24429, "scan_type": "local", "count": 1, "targets": "192.168.1.1-192.168.100.255", "host_end": "Tue Jul 26 11:54:02 2016", "scan_end": 1469561133, "hasaudittrail": true, "plugin_family": "Windows", "status": "completed", "hostname": "hostname@domain.com", "scanner_start": 1469527225, "timestamp": 1469561133, "name": "Weekly Authenticated Scans - OSU Administrative Zones"}

Re: How to find the number of times a specific field value has been present over time

sundareshr — Fri, 05 Aug 2016 00:49:09 GMT

This should give you number of weeks (assuming once a week scans) a plugin_name occurs (you can adjust the time period you choose to run this search)

your base search | stats count by host-ip plugin_name

*OR*

your base search | eval wknum=strftime(strptime(host_end, "%a %b %d %H:%M:%S %Y"), "%W") | eval host_plugin="host-ip"."##".plugin_name | chart count over host_plugin by wknum | rex field=host_plugin "(?<host>[^#]+)##(?<plugin_name>.*)" | table host plugin_name * | addtotals | fields - host_plugin