Splunk Enterprise Security

Getting output of stats command to work in timechart

jlovik
Explorer

I am getting the following data from a stats command. How would i translate this into a timechart? when i do try and add a time chart after manipulating a second date field into _time i get no more data. The stats command give me the data i need i just need help formatting it day over day with vulnID totals broken down by Category.

Have tried:

| timechart span=1d dc(VulnID) by Category

output is blank for this timechart.

Stats command:

| stats dc(VulnID) by Category _time


Category    _time   dc(VulnID)
Critical    2020-04-14  53
High    2020-04-07  424
High    2020-04-08  4
High    2020-04-09  34
High    2020-04-13  138
High    2020-04-14  4427
High    2020-04-15  719
High    2020-04-16  5416
High    2020-04-22  6
Low 2020-04-06  1
Low 2020-04-07  926
Low 2020-04-08  2
Low 2020-04-09  11
Low 2020-04-13  3364
Low 2020-04-14  89049
Low 2020-04-15  443
Labels (1)
0 Karma

shivanshu1593
Contributor

I got you. Since you have all the correct data till stats command. I suggest you to keep everything in your search till stats and get rid of the timechart command and use this to get your results.

| xyseries _time Category UniqueVulns

Let me know if it works

Ciao,

to4kawa
SplunkTrust
SplunkTrust
| stats dc(VulnID) as UniqueVulns by Category _time
| xyseries _time Category UniqueVulns

xyseries needs correct filed names.
@shivanshu1593 You're so quick response.

0 Karma

shivanshu1593
Contributor

Haha not as fast as you, my friend. Your command on SPL is really amazing and I follow your answers to learn a lot as well 🙂
@to4kawa

0 Karma

shivanshu1593
Contributor

Just like you pasted the output of stats command here, would you post the sample output, that you'd like to see from timechart?

As far as I can see, your timechart should produce results, with days on the X-Axis and the Vulnerabilities on Y-axis, with the dc spread in between them. Your expected output can help us to guide you to the correct SPL.

0 Karma

jlovik
Explorer

Expected output would be just what you mentioned. but instead of getting that i just get 1 date and 0s for critical high low moderate. I suspect it is translating _time back to the event inject time and not the VulnerabilityPublishedDate like i set it to.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The stats command gave you three fields: _time, Category, and 'dc(VulnID)'. To avoid counting a count, the timechart command should be timechart span=1d values('dc(VulnID)') as "dc(VulnID)" by Category. You can make it a little less ugly this way:

... | stats dc(VulnID) as UniqueVulns by Category _time
| timechart span=1d values(UniqueVulns) by Category
---
If this reply helps you, an upvote would be appreciated.
0 Karma

jlovik
Explorer

@richgalloway for whatever reason this still produces nothing. That is what has me all mixed up. Below i put the whole query in case i am doing something foolish higher up in my stack.

output:
_time Critical High Low Moderate
2020-05-05

Full Query:
index=vuns sourcetype="vulndb" earliest=-12h latest=now
| eval Epoch_Time=strptime(VulnerabilityPublishedDate, "%Y-%m-%d")
| eval disctime=strftime(_time, "%Y-%m-%d")
| eval Addtime=strftime(Epoch_Time + (30 * 86400), "%Y-%m-%d")
| where Addtime > disctime
| eval newtime=strftime(Epoch_Time, "%Y-%m-%d")
| eval _time=newtime
| stats dc(VulnID) as UniqueVulns by Category _time
| timechart span=1d values(UniqueVulns) by Category

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I see a few problems.

First, the base search specifies earliest=-12h so only half a day of events is being sent to timechart, which is trying to display events by day. This is minor.

Second is a major problem. | where Addtime > disctime is trying compare timestamps in text format, which is not possible. Timestamps must be compared in epoch form.

Third, _time must be in epoch form so it's not correct to perform eval _time = strftime.... This is unintuitive because Splunk automatically displays _time in text format.

Try this query:

index=vuns sourcetype="vulndb" earliest=-12h latest=now
| eval Epoch_Time=strptime(VulnerabilityPublishedDate, "%Y-%m-%d")
| eval Addtime=relative_time(Epoch_Time, "+30d")
| where Addtime > _time
| eval _time=Epoch_Time
| stats dc(VulnID) as UniqueVulns by Category _time
| timechart span=1d values(UniqueVulns) by Category
---
If this reply helps you, an upvote would be appreciated.
0 Karma