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
Builder

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,

Thank you,
Shiv
###If you found the answer helpful, kindly consider upvoting/accepting it as the answer as it helps other Splunkers find the solutions to similar issues###

to4kawa
Ultra Champion
| 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
Builder

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

Thank you,
Shiv
###If you found the answer helpful, kindly consider upvoting/accepting it as the answer as it helps other Splunkers find the solutions to similar issues###
0 Karma

shivanshu1593
Builder

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.

Thank you,
Shiv
###If you found the answer helpful, kindly consider upvoting/accepting it as the answer as it helps other Splunkers find the solutions to similar issues###
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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...