Dashboards & Visualizations

If I want to display calculated indicators over time, not using time chart, how could I do it?

armandonaterag
New Member

I have one metric calculated from different types of events, the problem is, when I try to use a line chart to show how this particular indicator changes monthly, as this value is not directly related to the events (I used the eval command and a formula to obtain it) If I use time chart it wont show the results properly, I need to display this value after calculating it monthly in a line chart and actually be able to see how it varies. help please?

Tags (2)
0 Karma

maciep
Champion

It's kind of hard to answer the question without more context, but It sounds like you already have a month field in your results? If that's the case, some thoughts...

  • You can eval _time to that field, using striptime() to cast it to epoch if needed. At that point, timechart should work fine.
  • Find a way to sort your data from earliest to latest using your month field, then I think the line graph should work.
  • Depending on how your data is formatted, it might help to use xyseries/untable to put the results in a more graph-friendly format.

Of course, if you give us a bit more information about your data and your current search, we might be able to help you use timechart natively

0 Karma

armandonaterag
New Member

Hello again and thanks for previous thoughts,

so this is the search:
index=xxxx sourcetype="xxxxx" name="xxxxx*" earliest= -2mon@mon+3w
|dedup host-ip, plugin_name, plugin_family, severity, "ports{}.port", "ports{}.transport" | eval vulhost=if (severity="critical" or severity="high" or severity="medium" or severity="low",'host-ip',null())| stats dc(host-ip) as TH, dc(vulhost) as VH, first(date_month) as ETime, count(eval(severity="critical")) as VH1, count(eval(severity="high")) as VH2, count(eval(severity="medium")) as VH3, count(eval(severity="low")) as VH4, count | fillnull critical, high,medium,low,VH1,VH2,VH3,VH4
|eval RiskValueC=round((VH1+0.75*VH2+0.5*VH3+0.1*VH4)/(VH1+VH2+VH3+VH4)(VH/TH),5)
|stats first(RiskValueC) as "Risk Value 2 months ago" by ETime
|join [search index=xxxx sourcetype="xxxxx" name="xxxxx
" earliest=-1mon@mon+3w
|dedup host-ip, plugin_name, plugin_family, severity, "ports{}.port", "ports{}.transport" | eval vulhost=if (severity="critical" or severity="high" or severity="medium" or severity="low",'host-ip',null())| stats dc(host-ip) as TH, dc(vulhost) as VH, first(date_month) as ETime, count(eval(severity="critical")) as VH1, count(eval(severity="high")) as VH2, count(eval(severity="medium")) as VH3, count(eval(severity="low")) as VH4, count | fillnull critical, high,medium,low,VH1,VH2,VH3,VH4
|eval RiskValueL=round((VH1+0.75*VH2+0.5*VH3+0.1*VH4)/(VH1+VH2+VH3+VH4)(VH/TH),5)
|stats first(RiskValueL) as "Last Month Risk Value" by ETime]
|join [search index=xxxx sourcetype="xxxxx" name="xxxxx
" earliest=-7d
|dedup host-ip, plugin_name, plugin_family, severity, "ports{}.port", "ports{}.transport" | eval vulhost=if (severity="critical" or severity="high" or severity="medium" or severity="low",'host-ip',null())| stats dc(host-ip) as TH, dc(vulhost) as VH, first(date_month) as ETime, count(eval(severity="critical")) as VH1, count(eval(severity="high")) as VH2, count(eval(severity="medium")) as VH3, count(eval(severity="low")) as VH4, count | fillnull critical, high,medium,low,VH1,VH2,VH3,VH4

Basically I tried doing a 2 months search and then joined the same search 2 more times to have a total of 6 months, but even though I have the Etime field time chart would not order it properly in the line chart.

0 Karma

maciep
Champion

First, to your question....

I think the problem is that your ETime field is just a string, so it's going to be sorted as a string. Instead of using date_month for that, maybe just eval ETime.

your base search
| eval ETime = relative_time(_time, "@mon")

Then in your stats

|stats ... first(ETime) as ETime

And at the end of your search

| sort ETime  
| convert timeformat="%b-%Y" ctime(ETime)

Something to that effect. There are many different (and probably better) ways to get there, but ensuring you can sort by your ETime as a date instead of string will allow you to get the data in the right order before trying to visualize it

0 Karma

maciep
Champion

Second, I think you should be able to do this in one search instead of three. And I'm not sure if your current search is accurate either? I'm assuming your verifying the numbers, but since your first search looks at all data since 2 months ago, it seems like it it's including counts from today. It doesn't look like you're doing anything to actually limit the risk value calculation to 2 months ago.

Meaning, your stats is doing its calculations across every event being returned. Sure, the Etime will resolve to the first month because that's what you're telling it, but you're not actually limiting your counts to that month.

If the goal is to get the risk values over time by month, then I think this would be better accomplished with a combination of bucket _time span=1mon and stats or timechart span=1mon. But I don't understand the data set enough to create an accurate search. For example, i'm not sure why you're deduping events before doing your counts. And I'm not sure why your searches start 3 weeks into the month.

But I hope this helps a little.

0 Karma

niketn
Legend

@armandonaterag would it be possible for you to provide your current query. Also screenshot of what issue you are getting. Mock up/hide any sensitive data if required.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

armandonaterag
New Member

Hello again and thanks for previous thoughts,

so this is the search:
index=xxxx sourcetype="xxxxx" name="xxxxx*" earliest= -2mon@mon+3w
|dedup host-ip, plugin_name, plugin_family, severity, "ports{}.port", "ports{}.transport" | eval vulhost=if (severity="critical" or severity="high" or severity="medium" or severity="low",'host-ip',null())| stats dc(host-ip) as TH, dc(vulhost) as VH, first(date_month) as ETime, count(eval(severity="critical")) as VH1, count(eval(severity="high")) as VH2, count(eval(severity="medium")) as VH3, count(eval(severity="low")) as VH4, count | fillnull critical, high,medium,low,VH1,VH2,VH3,VH4
|eval RiskValueC=round((VH1+0.75*VH2+0.5*VH3+0.1*VH4)/(VH1+VH2+VH3+VH4)(VH/TH),5)
|stats first(RiskValueC) as "Risk Value 2 months ago" by ETime
|join [search index=xxxx sourcetype="xxxxx" name="xxxxx
" earliest=-1mon@mon+3w
|dedup host-ip, plugin_name, plugin_family, severity, "ports{}.port", "ports{}.transport" | eval vulhost=if (severity="critical" or severity="high" or severity="medium" or severity="low",'host-ip',null())| stats dc(host-ip) as TH, dc(vulhost) as VH, first(date_month) as ETime, count(eval(severity="critical")) as VH1, count(eval(severity="high")) as VH2, count(eval(severity="medium")) as VH3, count(eval(severity="low")) as VH4, count | fillnull critical, high,medium,low,VH1,VH2,VH3,VH4
|eval RiskValueL=round((VH1+0.75*VH2+0.5*VH3+0.1*VH4)/(VH1+VH2+VH3+VH4)(VH/TH),5)
|stats first(RiskValueL) as "Last Month Risk Value" by ETime]
|join [search index=xxxx sourcetype="xxxxx" name="xxxxx
" earliest=-7d
|dedup host-ip, plugin_name, plugin_family, severity, "ports{}.port", "ports{}.transport" | eval vulhost=if (severity="critical" or severity="high" or severity="medium" or severity="low",'host-ip',null())| stats dc(host-ip) as TH, dc(vulhost) as VH, first(date_month) as ETime, count(eval(severity="critical")) as VH1, count(eval(severity="high")) as VH2, count(eval(severity="medium")) as VH3, count(eval(severity="low")) as VH4, count | fillnull critical, high,medium,low,VH1,VH2,VH3,VH4

Basically I tried doing a 2 months search and then joined the same search 2 more times to have a total of 6 months, but even though I have the Etime field time chart would not order it properly in the line chart.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...