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
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,
| stats dc(VulnID) as UniqueVulns by Category _time
| xyseries _time Category UniqueVulns
xyseries
needs correct filed names.
@shivanshu1593 You're so quick response.
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
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.
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.
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
@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
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