_time always comes up blank

mgemin — Sun, 07 Jun 2020 00:29:26 GMT

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

Re: _time always comes up blank

richgalloway — Sat, 30 May 2020 12:37:30 GMT

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.

Re: _time always comes up blank

mgemin — Sun, 31 May 2020 22:06:45 GMT

Could you help me with an amended query? I tried playing around with tstats/stats to no avail.

Thanks

Re: _time always comes up blank

richgalloway — Mon, 01 Jun 2020 15:29:02 GMT

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

topic Re: _time always comes up blank in Getting Data In

_time always comes up blank

Re: _time always comes up blank

Re: _time always comes up blank

Re: _time always comes up blank