Need help with time-based search with data from two sources

[john_dagostino]

I'm looking to put together some reports on vulnerability data where I can show a trending value of both fixed and active vulns at any given time. Our vulnerability data is separated where we have assets (asset_id) and the last time they were scanned (last_scan_finished) as one sourcetype, and the assets (asset_id), vulnerability (signature_id) and the last time that vuln was detected (most_recently_discovered) as another sourcetype. When a vulnerability is resolved we don't receive any indication in the data, but it will not be detected in future scans.

I'm looking to timechart each combination of asset_id and signature_id, where if the most_recently_discovered field is greater than or equal to the last_scan_finished date it is considered active, otherwise it's resolved. I've made several attempts however haven't been able to come up with a workable solution. Any help would be greatly appreciated.

[richgalloway]

It would help if you'd share the search(es) you've tried. Perhaps we'll find your mistake. At least we won't waste time suggesting what you've already done.

[john_dagostino]

This is the closest I've come, after converting to a data model, however it will only give me the status at the time the search is run due to the latest(Nexpose.last_scan_finished). I also added an offset in there so that the most_recently_discovered plus 1 day needs to be less than the last scan.

| tstats count from datamodel=Nexpose_Vulnerability where nodename=Nexpose.Vulnerabilities by Nexpose.asset_id Nexpose.signature_id Nexpose.most_recently_discovered
| join type=outer Nexpose.asset_id [| tstats latest(Nexpose.last_scan_finished) AS last_scan_finished from datamodel=Nexpose_Vulnerability where nodename=Nexpose.Assets by Nexpose.asset_id]
| rename Nexpose.* AS *
| eval most_recent_epoch=strptime(most_recently_discovered,"%Y-%m-%d %H:%M:%S") , last_scan_epoch=strptime(last_scan_finished,"%Y-%m-%d %H:%M:%S"), most_recent_plus_24=most_recent_epoch+86400
| convert ctime(most_recent_plus_24) AS vuln_last_seen_plus24, ctime(last_scan_epoch) AS last_scan
| eval status=if(most_recent_plus_24