I have two source types -
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.
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
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?
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".
It has the same results - with UserBox20 the same as before & NA for the IdealVersion.
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.
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.