How Can I Compare Fields Against Another SourceType?

[taylornat]

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.

[somesoni2]

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

[taylornat]

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?

[somesoni2]

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".

[taylornat]

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

[somesoni2]

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.

[taylornat]

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.