Getting Data In

_time always comes up blank

mgemin
New Member

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

Labels (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

mgemin
New Member

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

Thanks

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...