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.
| timechart span=1d dc(VulnID) by Category
output is blank for this timechart.
| 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
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.
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.
_time Critical High Low Moderate
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