Hi All,
I want to be able to add a timestamp to each event, so that I can then perform some stats over a period of time. Ideally I'd like to come up with metrics about how the dataset has changed over a period of time (up to 6 months).
I guess my first step is to get the _time for every event. However when I add this, it is not showing in my output - can anyone help with this? On top of this I would love any generic help with the other piece.
Query below...:
| tstats values(devDeviceName) as devDeviceName values(devDeviceIp) as devDeviceIp values(devProductFamily) as devProductFamily values(devProductId) as devProductId values(matchConfidence) as matchConfidence values(matchConfidenceReason) as matchConfidenceReason WHERE ( index=xxxx-np sourcetype=psirt_details_vulnerable_v7 earliest=-2d matchConfidence="*" matchConfidenceReason!="Missing: Feature" [| `last_np_source("index=xxxx-np", "psirt_details_vulnerable_v7")`] devDeviceName!=".*" ) by deviceId, psirtColdId
| fields psirtColdId deviceId devDeviceName devDeviceIp devProductFamily devProductId nextSteps matchConfidence matchConfidenceReason cv* sir psirtAdvisoryId _time
| eval ss = psirtColdId."@".matchConfidence."@".matchConfidenceReason
| append [search index="xxxx-np" sourcetype="device_details" | table deviceId configStatus deviceSysname swVersion ]
| stats values(*) as * by deviceId
| mvexpand ss
| eval sss=split(ss,"@")
| eval psirtColdId = mvindex(sss,0)
| eval matchConfidence = mvindex(sss,1)
| eval matchConfidenceReason = mvindex(sss,2)
| regex devDeviceName=".*"
| rex mode=sed field=devProductId "s/,.*//"
| lookup xxxx-psirt_bulletins.csv psirtColdId
| rex mode=sed field=deviceSysname "s/\..*$//"
| makemv delim=";" matchConfidenceReason
| mvexpand matchConfidenceReason
| eval newMCR=if(configStatus!="Completed" and match(matchConfidenceReason, "Missing: Feature"), "Missing: Configuration", matchConfidenceReason)
| fields - matchConfidenceReason
| mvcombine newMCR
| eval matchConfidenceReason=mvjoin(newMCR, ";")
| fields deviceSysname deviceId devDeviceName devDeviceIp configStatus swVersion devProductFamily devProductId nextSteps matchConfidence matchConfidenceReason cv* sir psirtAdvisoryId bulletinFirstPublished bulletinLastUpdated bulletinMappingCaveat bulletinTitle bulletinSummary bulletinUrl _time
| lookup xxxx-hardware.csv deviceId OUTPUT ps
| mvexpand ps
| rex field=ps "^(?.*?)::(?.*?)$"
| where NOT ((psirtAdvisoryId="cisco-sa-20200205-nxos-cdp-rce" or psirtAdvisoryId="cisco-sa-20200205-fxnxos-iosxr-cdp-dos") and match(deviceSysname,"-(?:ISD|CMD|OBI|DMD)$") and match(devProductId,"^N9K"))
| where NOT (match(devProductId,"9300L") and match(cveId,"(?:CVE-2017-6663|CVE-2017-6664|CVE-2017-6665|CVE-2019-1649)$"))
| lookup secure_boot.csv deviceId OUTPUTNEW deviceId as secureId
| where NOT (psirtAdvisoryId="cisco-sa-20190513-secureboot" and isnull(secureId))
| append [inputlookup psirt2.csv append=true | lookup devices2.csv device_type | eval zip=mvzip(affected_device,sw_version,",") | mvexpand zip | eval zip2 = split(zip,",") | eval device_name=mvindex(zip2,0) | eval sw_version=mvindex(zip2,1) | eval device_type=if(device_type="ISE","Identity Services Engine",if(device_type="SD-WAN","SD-WAN Solution",if(device_type="DNAC","DNA Center",if(device_type="DNA-S-C","DNA Spaces Connector",if(device_type="SD-WAN-R","IOS-XE SD-WAN Software",device_type))))) | eval cvss_temp_score="No temporal CVSS score available" | fields device_name sw_version advisory_id cvss_base_score cvss_temp_score last_updated first_published device_type advisory_title sir cve_id fixed_sw bcs_comments bcs_risk url | rename advisory_id as psirtAdvisoryId | rename cvss_base_score as cvssBase | rename cvss_temp_score as cvssTemporal | rename last_updated as bulletinLastUpdated | rename first_published as bulletinFirstPublished | rename device_type as devProductFamily | rename advisory_title as bulletinTitle | rename url as bulletinUrl | rename cve_id as cveId | rename device_name as deviceSysname | rename sw_version as swVersion | eval matchConfidence = "Vulnerable" | eval matchConfidenceReason = "Manual Analysis - Not mapped natively in BCS"]
| table _time deviceId deviceSysname devDeviceName devDeviceIp configStatus devProductFamily devProductId productId swVersion serialNumber matchConfidence matchConfidenceReason cv* sir psirtAdvisoryId bulletinFirstPublished bulletinLastUpdated bulletinTitle bulletinUrl fixed_sw bcs_comments bcs_risk
| rename sir as Severity
| rename productId as childProductId
Thanks,
Michael
The problem lies with tstats
and stats
. Those commands filter out all but the fields they use. That means if you don't include _time in the command it won't be available to later commands.
Could you help me with an amended query? I tried playing around with tstats/stats to no avail.
Thanks
Have you tried this?
| tstats values(devDeviceName) as devDeviceName values(devDeviceIp) as devDeviceIp values(devProductFamily) as devProductFamily values(devProductId) as devProductId values(matchConfidence) as matchConfidence values(matchConfidenceReason) as matchConfidenceReason WHERE ( index=xxxx-np sourcetype=psirt_details_vulnerable_v7 earliest=-2d matchConfidence="*" matchConfidenceReason!="Missing: Feature" [| `last_np_source("index=xxxx-np", "psirt_details_vulnerable_v7")`] devDeviceName!=".*" ) by deviceId, psirtColdId, _time