topic How Can I Compare Fields Against Another SourceType? in Getting Data In

How Can I Compare Fields Against Another SourceType?

taylornat — Thu, 15 Jan 2015 20:58:19 GMT

I have two source types -

One with a list of hosts and their software (DataIn).
The other with a list of ideal version numbers for that software (IdealData).

I want to be able to compare the newest entry for hosts against the newest ideal software version.

I can't just use the newest host entry because there may be multiple entries for different software.

EG. Comparing DataIn vs. IdealData

Sourcetypes -
DataIn - Time,IP,MAC,Hostname,Software,Version
IdealData - Time, Software, Version

DataIn -
1/15/15, , ,Box1,VisiCalc,4
1/15/15, , ,Box1,Lotus,2
1/10/15, , ,Box2,VisiCalc,1

IdealData -
1/16/15,VisiCalc,3
1/5/15,VisiCalc,1

In this situation Box 2 would not be running the latest version, but Box 1 would.

Ideally I'd love to produce a list of hosts, showing the software that is out of date, but everything I'm trying isn't working. I'd love any help.

Re: How Can I Compare Fields Against Another SourceType?

somesoni2 — Thu, 15 Jan 2015 21:53:18 GMT

Assuming both the sourcetypes have _time field defined (timestamp recognition is setup). Try following query: -

index=A sourcetype=DataIn | stats latest(Version) as CurrentVersion by IP,MAC,Hostname,Software 
| join type=left Software [ search index=B sourcetype=IdealData | stats latest(Version) as IdealVersion by Software ]
| fillnull value="NA" IdealVersion 
| where IdealVersion!=CurrentVersion

Re: How Can I Compare Fields Against Another SourceType?

taylornat — Thu, 15 Jan 2015 22:31:04 GMT

Thank you so much for going to all this trouble.

This works great!

index=A sourcetype=DataIn | stats latest(Version) as CurrentVersion by IP,MAC,Hostname,Software

& This works great!

 index=B sourcetype=IdealData | stats latest(Version) as IdealVersion by Software

(although it repeats one Software title twice - not sure if that matters - same version)

However, the whole query doesn't work as I expected -

IP               MAC                  Hostname     Software    Cur.  IdealVersion
192.168.0.200   1A-A2-FF-EA-E4-C1   UserBox35   Lotus 123   7   NA   
192.168.0.50     D6-45-CE-D3-C2-6B  UserBox20   Lotus 123   4   NA

I would expect UserBox20 not to be in a list of Hosts that meet the ideal version (which is 5+)
(or to be included only if it was a list of Hosts that don't meet the ideal version).

Is it something I've done wrong or something the search doesn't account for?

Re: How Can I Compare Fields Against Another SourceType?

somesoni2 — Thu, 15 Jan 2015 22:36:35 GMT

Can you try the search without the "| where..." clause and see if you get values for IdealVersion field at all?
Mean other than value="NA".

Re: How Can I Compare Fields Against Another SourceType?

taylornat — Thu, 15 Jan 2015 22:40:45 GMT

It has the same results - with UserBox20 the same as before & NA for the IdealVersion.

Re: How Can I Compare Fields Against Another SourceType?

somesoni2 — Thu, 15 Jan 2015 22:44:40 GMT

We are joining based on field "Software", it may be possible that values are not same between two sourcetypes. To validate, run following query and let me know.

(index=A sourcetype=DataIn)  OR (index=B sourcetype=IdealData | stats values(sourcetype) as sourcetypes by Software

above search should gives either 2 valued field sourcetype for the common 'Software' values.

Re: How Can I Compare Fields Against Another SourceType?

taylornat — Thu, 15 Jan 2015 23:52:14 GMT

Here are the results:

Software   sourcetypes
Eudora   IdealDATA
           DataIN
Lotus 123  IdealDATA
           DataIN
WordStar   IdealDATA
           DataIN

It gives both sourcetypes for each type of Software.