topic Re: How to create a time chart with milestones? in Splunk Search

How to create a time chart with milestones?

dbcase — Fri, 27 Jan 2017 17:14:53 GMT

Hi,

I'm trying to get some sort of timechart with milestones. Something like the attached pic (example) .

I know Splunk can't do exactly what is in the pic but I was thinking of a column chart with two series. Series #1 would come from index betadb, and series 2 would come from index allmsos.

betadb events look like this:

1004534,1004295,TCA203,N,N,01-26-2017 05:01:33,null

PremiseID, reference number, hardware, don't care, don't care, date time, don't care

and the allmsos data looks like this:

01-27-2017 10:27:59,7_3_10_000500_3851899

date time, version number

Iguinn was very helpful and came up with a query that looks like this:

(index=betadb OR index=allmsos) (source=*bbOrCellOffline* OR source=*Beta.csv*) 
 | eval theSource=if(index=="betadb","bbOrCellOffline","Beta.csv")
 | timechart count by theSource|rename Beta.csv as "Version Count"

And it works the way I had described the problem. What I didn't think of is, the allmsos data will be updated every day so there will be "duplicate" entries in there, only the date will change. I don't want to graph every day. I only want to graph when the version number changes..... And I'm stumped on this one.

Re: How to create a time chart with milestones?

somesoni2 — Fri, 27 Jan 2017 17:29:48 GMT

Give this a try. Check the field names and base searches. The ideas is to have allmsos data appended to other data and a dedup is done on version number so that only the records when version changes will exist.

(index=betadb  source=*Beta.csv*) 
 timechart count as  "Version Count"
| append [search index=allmsos source=*bbOrCellOffline* | dedup "Version Number" | timechart count as bbOrCellOffline] 
 timechart values(*) as *

Re: How to create a time chart with milestones?

dbcase — Fri, 27 Jan 2017 17:34:11 GMT

Hi Somesoni2,

Not very familiar with the append command (yet)

getting this error

Error in 'append' command: The last argument must be a subsearch.

Re: How to create a time chart with milestones?

dbcase — Fri, 27 Jan 2017 17:36:49 GMT

Also I'm thinking that the sources/indexes are mismatched

index=betadb has a source of bbOrCellOffline

index=allmsos has a source of Beta.csv

Re: How to create a time chart with milestones?

gokadroid — Fri, 27 Jan 2017 17:36:58 GMT

Just a thought:

How about the one's u want to keep as timeline milestone, keep them as bar charts and then overlay the one you want to keep as line on these bars. That will give the similar affect of having milestones standing lines (bars) and a line running through them (the value u overlayed).

Re: How to create a time chart with milestones?

dbcase — Fri, 27 Jan 2017 17:39:35 GMT

That will work even better! TY! Now to get the query sorted

Re: How to create a time chart with milestones?

somesoni2 — Fri, 27 Jan 2017 17:47:51 GMT

Actually missed the puoe before last timechart. Please add that and update the sources accordingly.

Re: How to create a time chart with milestones?

dbcase — Fri, 27 Jan 2017 17:53:07 GMT

Fixed the search but still not quite where it needs to be

(index=allmsos  source=*Beta.csv*) 
  |timechart count as  "Version Count"  | append [search index=betadb source=*bbOrCellOffline* | dedup "Version"| timechart count as bbOrCellOffline] |
  timechart values(*) as *

This query just gives a stat table of the Beta.csv source. No betadb data is represented.

Re: How to create a time chart with milestones?

dbcase — Fri, 27 Jan 2017 17:56:47 GMT

Also The Version field is part of the Beta.csv source. If I remove the dedup "Version" it does give me close to the chart I'm looking for but I'm back to each day having a Version column as in the original query that Iguinn provided.

Re: How to create a time chart with milestones?

dbcase — Fri, 27 Jan 2017 19:38:43 GMT

Fixed it!

Query looks like this:

(index=allmsos  source=*Beta.csv*) | dedup "Version"
  |timechart count as  "Version Count"  | append [search index=betadb source=*bbOrCellOffline* | timechart count as bbOrCellOffline] |
  timechart values(*) as * |convert num("Version Count") as vc |eval vc=vc*50|fields - "Version Count"