Searching the new SMAT Database in 6.1

[Danny Wheeler]

In 5.8.5 using the SMAT tool I was able to whittle away at the view until I had only what I wanted.  How is everyone searching the SMAT database tool?

Use case:

I need to find every transaction in the SMAT database that is an OMG and contains the MR# 000001.

In the previous SMAT tool I would search for the MRN in regex then remove if not OMG.

Is there a way to do this with a regex?  What is the recommended way to do a search like this?

Topic

[Danny Wheeler]

So many views and no replies is disheartening.  It tells my either I am the only one having this issue or no one has found a working solution yet.  I really like the SMAT Database tool but this would be a huge step back.

[Mark Thompson]

Hi Danny,

According to the 6.1 Release Notes …

Because SQLite does not have built-in support for RegEx, this has been added so that the IDE can query smat_msgs.messagecontent using RegEx queries.

I haven’t seen it in action yet, but hope to soon.

- Mark Thompson
HealthPartners

[Max Drown (Infor)]

Currently, you’ll need to use regular expressions. Searching will be much easier in Cloverleaf 6.1.1.

-- Max Drown (Infor)

[Peter Heggie]

Can you tell us more about 6.1.1?

Infor support says you cannot delete ‘rows’ from the SQLite tables (in order to create a rolling 24 hour window of messages). I expect also that there is no easy way to select messages and insert them into another SQLite database that is viewable by the SMAT Viewing Utility.

So we are stuck with cycle saving them, and if we want to look back 24 hours, we have to adjust te cycle save, or change the backup / compression scripts. We may have to hold off on compressing the backups for a longer period of time and then use the SMAT database selection criteria to select the backup smat databases (perhaps in the SmatHistory folder). But that might be ok. The tool is supposed to allow you to search through multiple smat databases (I think).

Peter Heggie

[Max Drown (Infor)]

I can’t tell you anything about CIS 6.1.1 yet. Details should be released soon. I will say that SMAT DB searching is *greatly * enhanced.

You will not be able to to modify the SMAT DB, especially since the ultimate goal is for the database to be encrypted to support HIPPA’s “data at rest” requirements. However, you can dump contents from the database and reuse the data.

Yup, you can search across files using SMAT DB search. Cycling at a specific time should not be necessary any more, but instead cycle on file size thresholds and then search across files. Requests have been made to add cycling on a schedule.

-- Max Drown (Infor)

[Rob Lindsey]

Since we have not gotten a copy of version 6.1 to test with yet, I need to ask a question.

Is Infor/Cloverleaf suggesting that there be only a single db file per interface and that single file keep the entire archive of the data flow in that single db?

We keep at least 45 days on the system to be able to do searches for historical reasons.  Just on one of our interface we have over 10 million messages over a 45 day period.  The size of the data for those messages is a bit over 19 GB.  

Is Infor suggesting that the entire interface be kept in a single DB and that it will be efficient with inserts happening at the same time as selects?

I believe that most sites that use cloverleaf do a cycle save each night or through out the day to be able to save off historical data.  This just a different way of doing things that a lot of people have been used to for many many years (in my case over 15 years).

[Yves Guerin]

Dear,

We do a cycling of the data for every inbound interfaces and some time outbound too.  We keep in the engine around 3 months of data (in separate directories) and we perfomed a backup every month of the data to dvd (around 3.5 G disk space for messages: .idx and .msg files).  I am wondering about the space that the db file will take on disk ?  Will we able to purge or save the db file as we did with the flat data file (.idx and .msg) and keep only 3 months on the engine and backup the rest (older than 3 months) to dvd ?

Regards,

Yves

[Max Drown (Infor)]

The SMAT DB files are configured in the NetConfig the same way that the SMAT files are configured, one per thread. With the flat files, unique file names were required. With SMAT DB, you have the option to point multiple threads at the same SMAT DB file.

You can continue to use external cycling scripts (UPoC advanced scheduling, cron, and scheduled jobs) to do whatever you are already doing like cycling on a time schedule. Currently, the built-in auto-cycling function only cycles on file size.

I would encourage you, where possible, to consider re-working your cycling methodology to take advantage of the built-in capabilities because this will be far easy to manage, make migrations significantly easier, and will leverage the SMAT DB search across files functions.

-- Max Drown (Infor)

[Yves Guerin]

Dear,

We still running cis.5.8

Regards,

Yves

[Bob Richardson]

Greetings,

Max, is there a published general availability for 6.1.1?  We are now running 6.1.0 on AIX but kept on the legacy option for SMAT files for now pending an evaluation of the SMAT DB option.

We are signed up for the Winter Showcase in March to be held locally in St. Paul, Minnesota (3/23 thru 3/26).   At that event you will be showcasing the 6.1.1 (3/25).

I urge other customers to check if INFOR is planning a showcase event near you!

Thanks.

[Max Drown (Infor)]

Not yet. Rob will post on Clovertech as soon as it’s available. I suspect the first part half of this year, maybe even the first quarter. It is in beta 1 now.

-- Max Drown (Infor)

[Danny Wheeler]

If regular expression is the only way currently how would that be achieved.  I can’t seem to find any way to do a “AND” comparison in regex.  If I wanted messages with “12345” MR# and “EMS” service how would that be achieved?  I know I can do a “OR” and match if either item occurs.  How do you match only if both occur?

[Max Drown (Infor)]

If it was me, I’d dump the first search to a file and then do a second search using grep. Or dump all of the messages to a file and use grep for all conditions.

With the tool, it won’t be as accurate I don’t think, not having seen your data. You’ll have to do something like VALUE1.*?VALUE2.

-- Max Drown (Infor)

[Danny Wheeler]

Below is an example.  I want to match messages where the MSH.9.0 is “ADT” and PID.3.0 is “12345”.  I don’t want the first message to match.  But I do want the second message to match.  I also don’t want to match message 3.

MSH|^~&|SOARFCHARGES|SMMC|CAC|PRECYSE|201502150522||ORM^A08||P|2.7

EVN|A08|201502150522

PID|1||12345||********||*******|||||||||||117776880|********

PV1|1|||||||||OPS||||||||ER|||||||||||||||||||||||||||||474952.10

MSH|^~&|SOARFCHARGES|SMMC|CAC|PRECYSE|201502150528||ADT^A08||P|2.7

EVN|A08|201502150528

PID|1||12345||********||*******|||||||||||117876573|********

PV1|1|||||||||OPS||||||||ER|||||||||||||||||||||||||||||634916.00

MSH|^~&|SOARFCHARGES|SMMC|CAC|PRECYSE|201502150528||DFT^A08||P|2.7

EVN|A08|201502150528

PID|1||12345||********||*******|||||||||||117876573|********

PV1|1|||||||||OPS||||||||ER|||||||||||||||||||||||||||||00.00

[Max Drown (Infor)]

Try something like this: ADT.*?12345

-- Max Drown (Infor)

[James Cobane]

Here at HFHS, we cycle the current (5.8.5) SMAT files nightly and keep a weeks worth available for trouble-shooting/research.

[Danny Wheeler]

That REGEX worked for what I needed.  Thanks Max!

[Max Drown (Infor)]

I checked with R&D on this. Here’s their response,

We can do

-- Max Drown (Infor)

[Danny Wheeler]

Great!  Thank you for following up on this!  I have been using the regex you gave me earlier to fit my needs in the meantime.

[Author]

Replies